DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How to Connect Excel to Access Database using Excel VBA?

#toconnectexceltoacce #vbamacros

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.

Select Visual Basic

  • 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.

Click on Reference

  • 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.

Click the latest version

  • 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.

Select Visual Basic
Select Visual Basic

  • 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
Enter fullscreen mode Exit fullscreen mode
  • After that, you need to save the code by selecting it and then close the window.

Save the Code

  • 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.

Choose Macro option
Choose Macro option

  • Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

  • 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:

Top comments (0)