How to Connect Excel to Access Database using Excel VBA?
Tags: toconnectexceltoacce, vbamacros
Published: 2021-09-06 01:21:09

---
title: How to Connect Excel to Access Database using Excel VBA?
published: true
date: 2021-09-05 17:47:33 UTC
tags: ToConnectExcelToAcce,VBAMacros
canonical_url: https://geekexcel.com/how-to-connect-excel-to-access-database-using-excel-vba/
---

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](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.

![](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

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

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Tools.png)

- Then, you need to click on the **references**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Reference.png)<figcaption>Click on Reference</figcaption>

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

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-the-latest-version.png)<figcaption>Click the latest version</figcaption>

- 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](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-8-1024x180.png)<figcaption>Select Visual Basic</figcaption>

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

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-12-1024x644.png)<figcaption>Save the Code</figcaption>

- 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](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-8-1024x184.png)<figcaption>Choose Macro option</figcaption>

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

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-6.png)<figcaption>Run the Code</figcaption>

- 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](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Keep Reading:**

- **[Options in Quick Access Toolbar in Microsoft Excel 365](https://geekexcel.com/options-in-quick-access-toolbar-in-microsoft-excel-365/)**
- **[What is a Name Box and Its Features in Microsoft Excel 365?](https://geekexcel.com/what-is-a-name-box-and-its-features-in-microsoft-excel-365/)**
- **[How to Convert Excel Files to PDF in Microsoft Excel 365](https://geekexcel.com/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?](https://geekexcel.com/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?](https://geekexcel.com/what-is-createobject-method-in-vba-how-to-use-createobject-method-in-excel/)**