In this tutorial, we will guide you on how to connect Excel to Access Database using Excel VBA. Let’s get them below!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Access Database
The Access database is a relational database management system that effectively saves a large amount of data in an organized manner.
Connecting Access Database as Data Source to Excel
Add Reference To AcitveX Data Object
- Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
- Then, you need to ** ** select the Visual Basic option under the Code section.
- Now, in the Microsoft Visual Basic, we have to use ADO to connect to access the database.
- For that, you need to add the reference to ADO object.
- You have to add a module to your VBA project and click on the tools.
- Then, you need to click on the references.
- Now, you have to look for Microsoft ActiveX Data Object Library.
- Here, you need to check the latest version you have and click OK button, and it is done.
- Finally, you can create a link to the Access Database.
Write a VBA Code To Establish a connection to the Access Database
- To connect Excel to an Access database , you need to have an Access database.
- Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
- Then, you need to ** ** select the Visual Basic option under the Code section.
- Now, you have to copy and paste the code given below.
Sub ADO_Connection()
'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\Admin\Desktop\Test Database.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "SELECT * from customerT;"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close
End Sub
- After that, you need to save the code by selecting it and then close the window.
- Again, you have to go to the Excel Spreadsheet, and click on the Developer Tab.
- You need to choose the Macros option in the Code section.
- Now, you have to make sure that your macro name is selected and click the *Run * button.
- Finally, you will receive the *output * as data in the Excel will be connected to database in the Microsoft Excel.
Closure
We hope that this short tutorial gives you guidelines to connect Excel to Access Database using Excel VBA. ** Please leave a comment in case of any **queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!
Keep Reading:
- Options in Quick Access Toolbar in Microsoft Excel 365
- What is a Name Box and Its Features in Microsoft Excel 365?
- How to Convert Excel Files to PDF in Microsoft Excel 365
- How to Write To A Word File Using Macros (VBA) in Excel Office 365?
- What is CreateObject Method in VBA & How to use CreateObject Method in Excel?
Top comments (0)