To connect VBA Excel to ms access table and fetch the records in excel worksheet.
Here we shall create ADODB objects for connection and recordset. Then copy the retrieved records from MS access table to worksheet.Code shown below is written in excel macro file [.xlsm].
- Declare 2 ADODB objects (1) MyConnection (2) MyRecordset. ADODB objects are created to access a database.
- Then set the value of both the objects mentioned above, initially values of both the object variables will be NULL.
- New keyword is used because in Dim statement only variable type is defined, here with “New” we are creating a New connection object and records set object and then assigning values to these two variables.
- Define the query which is to be passed to ms access table
- Open the connection to ms access , and pass the query to ms access
- MyConnectionString, contains the driver name, database file path.
- Copy the records from ms access table [i.e. MyTable] to cell A1 of sheet1 in the macro file where this code was added.
Sub ConnectAccess() Dim MyConnection As ADODB.Connection Dim MyRecordset As ADODB.Recordset Dim MyQuery As String Set MyConnection = New ADODB.Connection Set MyRecordset = New ADODB.Recordset MyConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Users \Desktop\SampleAccessFile.accdb" MyQuery = "Select top 3 * from MyTable;" 'Open the connection MyConnection.Open MyConnectionString 'Execute the query Set MyRecordset = MyConnection.Execute(MyQuery) 'Copy the output of query in sheet1 Worksheets("Sheet1").Range("A1").CopyFromRecordset MyRecordset 'Closing the connection and setting the values of declared variables as Nothing or simply to NULL. MyRecordset.Close Set MyRecordset = Nothing MyConnection.Close Set MyConnection = Nothing End Sub
We need to add below highlighted library i.e.Microsoft ActiveX Data Objects 2.0 Library.
We had a table in MS access[i.e. MyTable ], see the screenshot below.
Once the code is executed, the data from table [i.e.MyTable ] was inserted into excel sheet.In the screenshot below, you can see all 3 records have been fetched into excel macro file.
- ADODB [ ActiveX Data Objects Database] – is a library which contains the objects which are used to connect an application to a database.
- Here OLEDB acts as a middle layer between ADODB and Database[MS Access] .
- We can modify the query as per requirement.
Post you may like