Definitive Guide To ADO in Excel and VBA

There are several methods to work with external data and files in Excel. ADO (ActiveX Data Objects) is one of the best and most frequently used tools to work with external data and files. In Excel, we can use the combination of ADO with VBA (Visual Basic for Applications) to work with external data sets in memory. ADO always comes in handy when we need to perform complex, multi-layered procedures and checks on external datasets.

What is ActiveX Data Objects (ADO)?

ADO in Excel and VBA is a tool in that helps developers to write VBA code to access data without knowing how the database is implemented; developers should be aware of the database connectivity only. Being a developer, you don’t need to know the SQL to access a database when using ADO, although you can run SQL commands directly using ADO. So, in short, ADO helps developers accomplish two major tasks:

Connect to a data source
Specify the datasets with which to work

Using the ADODB connection, we connect our VBA application with the databases e.g., SQL, MS Access, Microsoft List, Excel workbook, etc.,

Understanding the fundamental syntax of ADO (Connection String and Recordset)

While dealing with external data and files, we must connect the data source before doing anything. To establish the connection, we must provide VBA a few pieces of information. The required information will be provided to VBA in the form of the connection string.

What is a connection string?

A connection string is nothing but a text string that contains a series of variables (also called arguments), which VBA uses to identify the data source and open the connection for further use.

Let’s understand the connection string and its arguments that point to an MS Access database and MS Excel Workbook. You can find several other connection strings at ConnectionStrings.com – Forgot that connection string? Get it here!

Connection Sting – MS Access database

“Provider=Microsoft.ACE.OLEDB.12.0; ” & _
“Data Source= C:\MyDatabase.accdb;” & _
“User ID= Administrator;” & _
“Password= AdminPassword”

Connection Sting – MS Excel workbook

“Provider=Microsoft.ACE.OLEDB.12.0; ” & _
“Data Source= C:\MyExcelWorkbook.xlsx;” & _
“Extended Properties=Excel 12.0”

ADO connection string can have multiple arguments basis the data source type. Let’s understand the arguments which commonly used i.e., Provider, Data Source, Extended Properties, User ID, and Password (the same have been used in the previous example for MS Access and Excel).

Live Project – Data Entry Application in Excel and VBA using ADO
Data Entry Application in Excel and Access

Provider: With the help of Provider argument in the connection string, VBA identifies the type of data source we are going to work with. Suppose we are going to work with MS Access or MS Excel datasets, then the Provider syntax will be:

Provider=Microsoft.ACE.OLEDB.12.0

Data Source: This argument helps VBA to find the source of database or workbook that contains the data needed. For this parameter, we need to pass the full path of the database or workbook. For example:

Data Source=C:\Mydirectory\MyDatabaseName.accdb

Extended Properties: Extended Properties is required when we connect to an Excel Workbook. With the help of this argument, VBA identifies that data source is something else than a database. You can use this argument below:

Extended Properties=Excel 12.0

User ID: The User ID argument is optional and only used if the data source is protected with a user id and password. For example:

User Id = Admin

Password: This argument is optional and only need if the password is required to connect to the data source. For example:

Password = MyPassword

Note: You can skip User ID and Password arguments if the data source is not protected.

What is a Recordset?

A Recordset object is a group of records that can either come from a data source or as the output of a query to the table. The Recordset provides several methods and properties to examine the data that exists within the data source.

In addition to building a connection to the data source, we need to define the dataset (Recordset) with which we need to work. We can define the Recordset to open an existing table or query using the 4 common arguments: Source, ConnectString, CursorType, and LockType.

Recordset.Open Source, ConnectString, CursorType, LockType

Let’s understand all these 4 parameters.

Source in Recordset

The source data is typically a table, a SQL statement, or a query that retrieves records from the data source. Please see the below example of Source in different scenarios.

Providing MS Access table name ‘Product’ to Source

Recordset.Open “Product”

SQL statement to Source. In below code, MySQL is a variable holding SQL statement.

MySQL=”Select * from [Product] where Region=”North’” Recordset.Open MySQL

ConnectString in Recordset

ConnectString is the argument that we have already discussed while understanding the ConnectionString. We just need to pass the ConnectionString here so that Recordset can identify the data source.

So, suppose we are going to connect with MS Access table ‘Product’ in Recordset then the code will be like:

Recordset.Open Product, ConnectionString

CursorType in Recordset

A cursor is a mechanism that enables the Recordset to move over the records in a database. It allows developers to retrieve each row at a time and manipulate its data. In simple language, a cursor is nothing more than a point to a row on which you are performing the data manipulation work.

The CursorType that are commonly used in Recordset code:

adOpenForwardOnly: This is the default cursor type available in Recordset. If we don’t pass any CursorType in the code, then Recordset will automatically consider adOpenForwardOnly. This cursor is very efficient and allows us to move through the Recordset from beginning to end (one way only). This cursor is ideal for reporting purposes where we need to extract the data. This cursor does not allow to perform any changes to data.
adOpenDynamic: When we need to loop through the data, moving up and down in the datasets, and want to identify any edits made to the dataset then adOpenDynamic can be used in Recordset. As it performs almost all the activities required in database operation, this cursor takes a lot of memory and resources of the system and should be used only when needed.
adOpenStatic: This CursorType is ideal for quick return as it uses a static copy of data from the database. This is different from adOpenForwardOnly CursorType as it allows the developer to navigate the returned records. In addition to these, this CursorType allows data to be updateable by setting the LockType except adLockReadOnly (we will see LockType in upcoming part of this blog).

LockType: A LockType is a mechanism that helps developer to apply restrictions on a table or datasets to avoid unauthorized access or changes to the Recordset. We usually use two LockType in ADO:

adLockReadOnly: This is the default LockType in Recordset which indicates that there is no need to edit the data returned. If we don’t provide the LockType to Recordset then VBA considers this internally.

adLockOptimistic: This LockType is ideal when we need to edit the data returned to Recordset. We can use this if we want to perform Add, Update and Delete method in the database.

Referencing the ADO object library in VBA

Now, we have strong fundamentals of ADO and the codes/arguments. Let’s create our own ADO procedures to perform some basic operations. It will help us get more clarity and understanding of ADO in real projects.

To use the ADO in the Excel project, we need to add the reference of the ADO object library in the Visual Basic Application (VBA) window. Once we add the reference of ADO in the Excel project, Excel will start understanding the objects, properties, and methods of ADO.

Note: we can use ADO library without giving the reference of ADO in Excel with Late Binding technique, but it will increase the complexity of code and you will not be able to get the help while writing the code. So, we would recommend you start using the Early Binding i.e., giving the reference of ADO and then writing the code. Once you have expertise in the code then you can move to Late Binding technique.

To start adding the reference of ADO, just open the MS Excel application and create a new workbook and save the file with a Macro enabled extension e.g., .xlsm. Open the Visual Basic Editor window using the Shortcut key ALT + F11.

Saving Excel file with macro enabled extension

Once Visual Basic Editor window will appear on screen then click on Tools (available in the application menu) -> References….

Once you click on References.. then it will open the References dialog box as shown in the picture below. In the available references list, just scroll down until you find the latest version of the Microsoft ActiveX Data Objects Library. Just tick the Checkbox and click on the OK button available in the dialog box to add the ADO reference to the current Excel file.

Reference Dialog Box in VBA to Select ADO

Note: you can see several versions of the ADO library in Reference dialog box. We would recommend you select the latest version from the list or if you are developing a project for your client then check the version of MS Excel and available ADO on client system and then go with that library to make your code compatible.

After clicking on OK button, we can open the Reference dialog box again to ensure that whether the ADO reference is selected or not. If that is selected, then it will start appearing on top of the list as check marked (as you can see in above image).

VBA code to Use ADO and get data from MS Access database

Writing the code to get the data from Customer table of MS Access database.

Sub GetCustomerData ()
       Dim MyConnect as String
       Dim MyRecordset as ADODB.Recordset
       MyConnect= “Provider=Micorosft.ACE.OLEDB.12.0;” & _
                          “Data Source= D:\TheDataLabs\Sales.accdb”
       Set MyRecordset= New ADODB.Recordset
       MyRecordset.Open “Customer”, MyConnect, adOpenStatic, adLockReadOnly
      ThisWorkbook.Sheets(“Customer”).Range(“A2”).CopyFromRecordset MyRecorset
      With ActiveSheet.Range (“A1:C1”)
             .value = Array (“Name”, “Gender”, “Address”)
             .EntireColumn.AutoFit
      End With
End Sub

Now we are done with putting all the code together in a procedure to get the data from Customer table and provide the output in MS Excel worksheet “Customer”.

Understand VBA Codes line by line

For better clarity, let’s take a moment to understand the VBA code.

Sub GetCustomerData ()

With the help of this line, we are declaring a procedure named ‘GetCustomerData’.

Dim MyConnect as String

Declaring a string variable to hold the Connection sting so that VBA can identify the data source.

Dim MyRecordset as ADODB.Recordset

Declaring a Recordset object variable to hold the data that will be returned by the procedure.

MyConnect= “Provider=Micorosft.ACE.OLEDB.12.0; Data Source= D:\TheDataLabs\Sales.accdb”

Here, we are defining the connection string for the ADO procedure. As we are connecting the ‘Sales’ MS database to get the data from the Customer table hence, we are passing the Provider parameter as Micorosft.ACE.OLEDB.12.0 and Source as D:\TheDataLabs\Sales.accdb. The same has been already discussed in the Connection String section of the post.

Set MyRecordset= New ADODB.Recordset

With the help of the line of code, we are setting the reference of ADODB Recordset to MyRecordset object variable.

MyRecordset.Open “Customer”, MyConnect, adOpenStatic, adLockReadOnly

This line of code helps us in opening the Recordset to return static and read-only data.

ThisWorkbook.Sheets(“Customer”).Range(“A2”).CopyFromRecordset MyRecorset

Here, we are using Excel’s CopyFromRecordset method to get the data from the Recordset and provide the output in the range starting from the “A2” to the spreadsheet named ‘Customer’.

With ActiveSheet.Range (“A1:C1”) …. End With

These lines of Code help us in getting the column header and putting the header name in active sheet range A1 to C1. We need these lines of code because the CopyFromRecordset method does not return column headers or field names.

Live Project in Excel

Using ADO with Microsoft Visual Basic, we have developed the Data Entry Application Project in MS Excel and MS Access to transfer the data. Refer the below link.
Data Entry Application in Excel and Access

Interested in developing Data Entry Form without Using ADO
Easy-To-Follow: Create a Fully Automated Data Entry Userform in Excel and VBA in 5 Easy Steps
How to Create a Multi-User Data Entry Form in Excel (Step-by-step Guide)
Advance Data Entry Form

Definitive Guide To ADO in Excel and VBA – Get Started With These 5 Steps

Table of Contents