Get data from ms access table using VBA Excel

Connect VBA to MS Access table

Objective

To connect VBA Excel to ms access table and fetch the records in excel worksheet.

Approach

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

Steps

  1. Declare 2 ADODB objects (1) MyConnection (2) MyRecordset. ADODB objects are created to access a database.
  2. Then set the value of both the objects mentioned above, initially values of both the object variables will be  NULL.
  3. 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.
  4. Define the query which is to be passed to ms access table
  5. Open the connection to ms access , and  pass the query to ms access
  6. MyConnectionString, contains the driver name, database file path.
  7. Copy the records from ms access table [i.e. MyTable] to cell A1 of sheet1 in the macro file where this code was added.

 

Code

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.

 

Connect VBA to MS Access table

 

We had a table in MS access[i.e. MyTable ], see the screenshot below.

 

Connect VBA to MS Access table

 

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.

 

Connect VBA to MS Access table

 

 

Notes

  1. ADODB [ ActiveX Data Objects Database] – is a library which contains the objects which are used to connect an application to a database.
  2. Here OLEDB acts as a middle layer between ADODB and Database[MS Access] .
  3. We can modify the query as per requirement.

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.copyfromrecordset
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/recordset-object-ado?view=sql-server-2017

Post you may like

Copy the data from one excel file or workbook to another excel file using VBA Excel

1 comment on “Get data from ms access table using VBA Excel

Comments are closed.