The Access database is a relational database management system that effectively saves a large amount of data in an organized manner. Where Excel is a powerful tool for crunching down data into meaningful information. However, Excel can not store too much data. But when we use Excel and Access together, the power of these tools increases exponentially. So, let us learn how to connect the Access database as a data source to Excel through VBA.

• The Excel file runs on smaller summarised data and is kept lean. You have the power to call on refreshed data periodically to update your Excel datasets. Getting started the most important thing to do is to set up a connection to MS Access. In Excel Press Alt F11.
• This section describes the Excel Web Access web part and explains how to use it. In this section. How to: Programmatically Add an Excel Web Access web part to a Page. Learn how to programmatically add an Excel Web Access web part to a SharePoint page, and how to programmatically display a workbook in an Excel Web Access web part.
• The Access database is a relational database management system that effectively saves a large amount of data in an organized manner. Where Excel is a powerful tool for crunching down data into meaningful information. However, Excel can not store too much data.
• The first official version of Microsoft Access was released in 1992, a good 7 years after Excel was launched. Access is a Microsoft database management system (DBMS). It’s a relational database engine that’s used for both small and large database deployments.

Access does a good job of interpreting Excel data that originates in Excel. However, data that originates somewhere else and subsequently finds its way into an Excel file you must then import.

Connecting Access Database as Data Source Excel

1: Add Reference To AcitveX Data Object

We will be using ADO to connect to access the database. So first we need to add the reference to ADO object.

Add a module to your VBA project and click on the tools. Here click on the references.

Now look for Microsoft ActiveX Data Object Library. Check the latest version you have. I have 6.1. Click OK button and it is done. Now we are ready to create a link to the Access Database.

2. Write a VBA Code To Stablish a connection to the Access Database

To connect Excel to an Access database, you need to have an Access database. My database's name is 'Test Database.accdb'. It is saved at 'C:UsersManish SinghDesktop' location. These two variables are important. You will need to change them according to your needs. Rest code can be kept as it is.

Copy the code below to make your Excel VBA module and make changes as per your requirement. I have explained each line of the code below:

Copy the above code or download the file below and make changes to the file to suit your requirements.

When you run this VBA code, Excel will establish a connection to the database. Afterward, it will run the designed query. It will clear any old content on the sheet and will fill the column A with values of Field 1 (second field) of the database.

How does this VBA Access Database Connection work?

In the above line, we are not just declaring the Connection and recordset variables but initializing it directly using the New keyword.

These two lines are contestants. The DBPATH will change with your database only. PRVD is connecting OLE DB provider.

This line opens the connection to the database. Open is the function of the connection object that takes several arguments. The first and necessary argument is ConnectingString. This string contains the OLE DB provider (here PRVD) and the data source (here DBPATH). It can also take admin and password as optional arguments for protected databases.

The syntax of Connection.Open is:

Since I do not have any ID and Password on my database, I only use ConnectionString. The ConnectionString's format is 'Provider= provider_you want to use; Data Source= fully qualified name of database'. We made and saved this string in connString variable.

This is the query I want to run on the database. You can have any queries you want.

This statement runs the defined query in the defined connection. Here we are using the Open method of recordset object. All the output is saved in the recordset object rec. You can retrieve manipulate or delete values from the recordset object.

This line clears the content of the sheet. In other words, deletes everything from the cells of the sheet.

The above set of lines check if the recordset is empty or not. If the recordset is not empty (it means query returned some records) the loop begins and starts printing each value of field 1 (second field, first name in this case) in last unused cell in column.

(This is used just explain. You may not have these lines. If you just want to open a connection to the database then the VBA code above these lines is enough.)

We have used rec.EOF to run the loop until the end of the recordset. The rec.MoveNext is used to step up to the next recordset. rec.Fields(1) is used to get values from field 1 (which is second as its field indexing starts from 0. In my database, the second field is the First Name of the customer).

Finally, when all the work we wanted from the rec and conn is done, we close them.

You may have these lines in separate subroutine if you like to separately open and close specific connections.

So yeah guys, this how you establish a connection to the ACCESS database using ADO. There are other methods too, but this is the easiest way to connect to a data source of access through VBA. I have explained it in as detail as I can. Let me know if this was helpful in the comments section below.
Related Articles:

Use a closed workbook as a database (DAO) using VBA in Microsoft Excel | To use a closed workbook as a database with DAO connection use this VBA snippet in Excel.

Use a closed workbook as a database (ADO) using VBA in Microsoft Excel | To use a closed workbook as a database with ADO connection use this VBA snippet in Excel.

Getting Started With Excel VBA UserForms | To insert data to database, we use forms. The Excel UserForms are useful for getting information from the user. Here is how you should start with VBA userforms.

Change the value/content of several UserForm-controls using VBA in Excel | To change the content of the userform controls use this simple VBA snippet.

Prevent a userform from closing when the user clicks the x-button by using VBA in Excel | To prevent the userform from closing when the user clicks on the x button of the form we use UserForm_QueryClose event.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

In this guide, I’ll show you how to import an Excel file into Access.

Here are the steps you may follow:

Steps to Import an Excel File into Access

Step 1: Prepare the Excel File

To start, prepare the Excel file to be imported.

For example, I stored the following data in an Excel file:

This is how the data would look like in Excel (where the Excel file name is “People“):

Step 2: Import the Excel File into Access

Excel Access To The Resource Is Forbidden

To import the Excel file into Access:

First, open MS Access, and then go to the External Data tab:

Click on the New Data Source icon, and then select From File:

Excel Accessibility Checker

Choose the Excel option:

Click on the Browse… button:

Locate the Excel file to be imported, and then press Open:

Press OK:

Since our first row contains column headings, make sure that the First Row Contains Column Headings is checked. Then press Next:

You’ll now see the following display, where you can change the data type for each of your columns/fields. For example, the data type for the ‘First_Name‘ field is Short Text:

Let’s say that you want to change the data type of the ‘Age‘ field into integer. To do so, select the ‘Age‘ column, and then select Integer. Once you’re done, press Next:

Note that since the other fields contain short texts, we will not change the data type for those fields. By default, those fields are already assigned with the data type of Short Text.

At times, you may get errors when importing your data into Access. One way to avoid those errors, is by selecting the data type of Short Text for each field when importing your data. Once the data is imported, you can apply different formatting.

You may now choose to ‘Let Access add primary key.’ This primary key is a unique key that represents each of your records. It can be used to link with other tables. Finally, press Next:

Type a name for the new table to be created in Access. This table will contain the imported data from Excel.

For example, type ‘People‘ for the new table name, and then press Finish:

Press Close. Optionally, you may wish to check the ‘Save import steps’ box if you intend to repeat the same steps as above when importing your data from Excel.

Your Excel file should now be imported into Access. You can view the imported data at anytime by double-clicking on the ‘People‘ table under the ‘All Access Objects‘ section:

You’ll then see the imported data: