Posted on

Accessing multiple data sources using ADO and VBA in Excel

One way to load data into an Excel worksheet is to simply use “Get Data” option from Data tab. But it’s a manual process. In this article, let us look at another way to load data using VBA with ADO as the interface.

ADO stands for “ActiveX Data Objects”, the reason for using ADO in excel is that it acts as a common interface to get data from multiple sources. The main use of ADO is to unite all other data sources on to a single platform. If there was no ADO, then we would end up writing individual code for each data source.

ActiveX Data Object model to connect excel

‘ADO’ is not a default object added into the Excel VBA library. So, we must add it by navigating as follows: Tools -> references -> Microsoft ActiveX Data Objects 6.1 Library. Look at the below image for reference.

ADO object 6.1 in excel VBA Library

Let us write the VBA code using ADO object to load data from MS Access database. In this article we would be using “CarDetails” (MS Access Table) as the source DB table. Refer below snap to know more in detail.

Table in MS Access Database to connect to excel

The below code will fetch the data from MS Access database and loads into the excel file. Let us look at the loaded data in the below image. You can customize the query and the source depending on the required data. Indeed, you can get data from any source using ADO interface without any glitch.

MS Access table copied to Excel using ADO and VBA

Step 1: First step is to declare the new ADO object as:
                   “New ADODB.Connection”
Step 2: Open the connection by providing the connection string. In this article let us consider MS Access connection string
            “Provider=Microsoft.ACE.OLEDB.12.0;”&”Data Source=”Location of the MS Access file;”
Step 3: Create a string object to write a SQL query
              Dim query As String
              query = “select * from CarDetails”
Step 4: Create a ‘New’ Recordset to open the connection and execute the query. Refer below code to get an idea
              Dim rs As New ADODB.Recordset
              rs.Open query,Conn
Step 5: Select the destination location to load the data. In this article, we have considered Excel as the destination file. So, select the specific range in the excel to populate the data
             Sheet1.Range(“A1:F1”).Value = Array(“ID”, “Car name”, “Manufacturer”,  “year of manufacture”, “BS Version”, “Price”)
             Sheet1.Range(“A2”).CopyFromRecordset rs
Step 6: Final step is to close the connection
              Conn.Close

 

Check out our Tableau Expertise Related Pages