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.
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:\Users\Manish Singh\Desktop" 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:
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\ExcelTip\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
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.
Dim conn As New Connection, rec As New Recordset
In the above line, we are not just declaring the Connection and recordset variables but initializing it directly using the New keyword.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
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:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
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.
query = "SELECT * from customerT;"
This is the query I want to run on the database. You can have any queries you want.
rec.Open query, conn
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.
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
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.
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.
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.