Simple parameterized queries using ADO in VBA

You’re likely here because I referred you here to use parameters for your ADO queries. Using ADO queries is usually a tedious job, with a lot of reuse of code between different queries. You might have already found a tutorial, where the final code looked a little something like this:

This, of course, is a tiny bit extreme, but a usual, optimal way to parameterize a query. However, doing so for every query would take a lot of time

Many steps can be automated. We want our end result to be way easier, like the following:

And have the same end result. Let me outline the steps required for doing that.

(The following code has been tested under Microsoft Access, but should work under other VBA applications. It does require a reference to the Microsoft ActiveX Data Objects Library).

The first step for every query in ADO is creating the ADODB.Connection object. If you’re going to execute your query in Access, that’s pretty easy, it’s just CurrentProject.Connection. However, on SQL server, it’s a little more difficult.

The following assumes you’re going to use SQL server:

Let’s start at the top of our module, by declaring the connection string. Having a Public Const connectionString will help speed up coding a lot if you’re making a lot of database connections, and make it way easier when you need to move your database:

Then, let’s make a small function that returns an opened ADODB.Connection using that connection string:

If you’re going to use an Access connection, you can use the following function, which allows you to easily change the connection for all queries in the future:

One of the most difficult things we have to do with our easy query using parameters, is use the appropriate variable type. The function I’m going to use for that, is inspired by Gustav Brock’s CSQL. We’re going to map the different types in VBA to ADODB.DataTypeEnum enum values using VarType:

Now, we can build our VBA function that executes the query, like we intend to. We have automated all the difficult steps. To pass the parameters, we’re going to use a ParamArray:

Since the function returns a recordset, we can both use it to open up recordsets using parameters, and to execute action queries: