Start here: Excel, VBA and ADO for data access

This article illustrates how you can use Microsoft Excel workbooks, VBA and the ActiveX Data Objects (ADO) library and standard SQL to access and manipulate data tables and views in pretty much any relational database, and even in other Excel workbooks.

The ADO library allows for programmatic access (e.g., from VBA) to databases and other data sources and is included in any standard Office installation by default.

The ADO library can be graphically represented as a set of hierarchies of objects, as e.g. “Connection” and collections as e.g. “Fields”:

ADO Object Model — https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/ado-object-model

For database access and manipulation, we will almost exclusively use the following ones:

Connection: establishes a connection to a data source and executes commands
Recordset: represents the set of records from a base table or the results of an executed command (e.g. a view)
Fields: set of fields of a recordset
Field: a single field of a recordset

For a complete description and documentation about ADO, check the following links:

Setting a reference to the ADO library in the VBA editor

In order to use the library in VBA, you must set a reference to it. Open the VBA window (Alt-F11) and set a reference in the VBA editor to the Microsoft ActiveX Data Objects Library:

Tools > References …
Find and check “Microsoft ActiveX Data Objects 6.1 Library” (or any other version from 2.0 to 2.8)
Press Ok
You are ready to go!

Accessing a database via ADO/VBA

Now let’s test if we can connect to a database, starting with an Access database and an Excel database.

Please download the following files and put them in a C:\Test folder:

Creating the connection string

To access a database, you must feed ADO with a “connection string”, that is a string of parameters to initialize the connection driver.

Here’s the essential connection strings to use for an Excel or Access database:

Excel
- (xlsx files): “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\db.xlsx;Extended Properties=’Excel 12.0 Xml;HDR=YES’;” (notice the apostrophe ‘ for the Extended Properties)
- (xlsm files): … Extended Properties=’Excel 12.0 Macro …
- (xlsb or old xls files): … Extended Properties=’Excel 12.0 …
Access (mdb or accdb files): “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\NW2000_EN.mdb;”
- if the database is password protected: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\NW2000_EN.mdb;Jet OLEDB:Database Password=yourpassword;”

More on connection strings and data Providers:

Connect to an Excel db

Let’s use a generic Excel file to connect to the Excel db downloaded previously. Remember, the db.xlsx file should already be in C:\Test, or you must modify the connection string accordingly.

Open a blank Excel Workbook with a single sheet
Save As, Excel Macro-Enabled Workbook
Open the VBA Window (Alt+F11)
Insert a module (menu Insert > Module)
Copy the following code into the module

Public Sub PullData()
    
'Declare and set the variables needed
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim iCols As Long
    
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection

'Pass the connection string, open the connection, fetch the data from the sheet "Categories" into the recordset (rst)
'Notice that the sheet name must be followed by $ and included in square brackets
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\db.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';"
    cnn.Open
    rst.Open "[Categories$]", cnn
    
'Fetch the header of the "Categories" table and put it in Excel, starting from A9
        For iCols = 0 To rst.Fields.Count - 1
            Range("A9").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
            Range("A9").Cells(1, iCols + 1).Font.Bold = True
        Next

'Copy the data into Excel, starting from the cell A10
    Range("A10").CopyFromRecordset rst
    
End Sub

Now, clic into the code and press F5 (or menu Run > Run Sub …), then go to the Excel Window to see the data pulled from the db. You can also put a button into the Excel sheet to run the procedure: pick a shape from Insert > Shapes > right-click on the shape > Assign Macro … > Double click on the macro’s name > click off then on the button to run the procedure.

If you do know some SQL, you can use a SQL instruction instead of the table’s name: instead of – rst.Open “[Categories$]”, cnn – you could write, for example:

rst.Open “SELECT CustomerID, CompanyName FROM [Customers$] WHERE Country = ‘USA’ ORDER BY CompanyName;”, cnn

You can even pre-compute aggregates. Try this one instead of the previous one:

rst.Open “SELECT ProductID, ROUND(SUM(UnitPrice * Quantity)) AS Total FROM [Order Details$] GROUP BY ProductID ORDER BY ROUND(SUM(UnitPrice * Quantity)) DESC;”, cnn

As you can see, it sums UnitPrice * Quantity by ProductID from the Order Details table.

If you google “Microsoft Access SQL” you will find plenty of resources to learn something about SQL (for basic use, is an extremely simple language), here’s an introductory one in one page, and here’s another more comprehensive. From VBA you can use the Microsoft Access SQL “dialect” against any database, it will be automatically translated by the driver. Only in rare occasions you will need to send SQL instructions in the specific dialect of a particular db.

Connect to an Access db, or any other …

In order to connect to a Microsoft Access db (or any other db) we can use the very same routine above, we must simply change:

the connection string instruction, that becomes
- cnn.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\NW2000_EN.mdb;”
the rst.Open “[Categories$]”, cnn instruction: simply, we remove the $ from the table name

You can test on the Access db NW2000_EN previously downloaded (unzip it and put it in C:\Test).

As a further example, here’s the connection strings for a Microsoft SQL Server (v. 2008, but should work even with newer versions) and for a MySQL server.

SQL SERVER with the default driver included in any version of Windows: “Provider=SQLOLEDB.1;Password=your_password;User ID=your_user;Initial Catalog=YourDatabaseName;Data Source=ServerName”
the last two parameters are the database name and the network name of the server pc.
MySQL SERVER (you must install the ODBC drivers from the MySQL web site):
“DRIVER={MySQL ODBC 8.0 Unicode Driver};UID=your_user;PWD=your_password;SERVER=ServerName;DATABASE=YourDatabaseName;PORT=3306;”

Notice that, for server-based databases, you must tipically provide a user and a password (in clear text) in the connection string, if you cannot rely on some form of Windows authentication or other sophisticated authentication schemes.

Leverage the “Existing Connections” or “Get Data” built-in functionalities to build connection strings

So, the world of databases is open to Excel, even without having to rely on the “Existing Connections” or “Get Data” built-in functionalities. Anyway, they comes in handy when you have to build connection strings to use in VBA.

In the image below, underlined: “Get Data” and “Existing Connections” in Excel > Data.
“Query & Connections” shows the queries and connections created by “Get Data” (“Power Query”) and “Existing Connections”.

USING “EXISTING CONNECTIONS”

Click on “Existing Connections”, then on “Browse for More…” > “New Source…” (or simply browse the filesystem for a database file, e.g. an Access database or an Excel file):

For example, here’s what you get if you browse for an Access db (see below). You get something similar for an Excel file with multiple tables, or if you follow the “New Source…” path and connect to a db server:

To generate and discover the connection string:

choose any table from the table selector and press OK to import it;
right-click on the imported table > Table > External Data Properties … > “Connection properties” button > Definition tab (see the image below for the last two passages).You can see the connection string and you can simply copy and paste it in VBA (if the data source was password protected, check Save password before copying the string).

How to access an imported table’s connection string

You can also see the connection in the “Query & Connections” panel (in Excel > Data). Right-click > Properties to access the connection.

USING “GET DATA”

You can use any option, depending from the data source you want to access. Among the others, you can try one of the following two options:

From OLEDB > “Build” button (for OLE DB connections);
From Other Sources > Microsoft Query <New data source> > OK (for ODBC connections – you must install the ODBC driver for the specific database you are trying to access, if not already installed).

From OLEDB and, left-below, Microsoft Query (Choose Data Source)

If you manage to connect via “Get Data” (any option): right-click on the imported table > Table > External Data Properties … > “Query properties” button > Definition tab (see the image below for the last two passages). You can see the connection string or use “Export Connection file”.

Connection string from a table imported with “Get Data”

Enjoy