VBA and ADO Database Access

Analogy categoryAnalogies

What ADO Gives You

ADO (ActiveX Data Objects) is a COM library that lets VBA talk to almost any database — SQL Server, Access, Oracle, MySQL — through a common object model. The three objects you use constantly are Connection (the session to the database), Command (a prepared SQL statement, ideal for parameters), and Recordset (the rows returned by a query). You reach the database via a connection string that names a provider or ODBC driver plus server, database, and credentials.

🏏

Cricket analogy: ADO is a universal net session that works whether you face a spinner or a quick; Connection is booking the nets, Command is the ball you bowl, and Recordset is the highlights reel of every delivery that came back.

Opening a Connection

You can bind ADO early by adding a reference to 'Microsoft ActiveX Data Objects 6.1 Library' and declaring Dim conn As New ADODB.Connection, or late-bind with CreateObject("ADODB.Connection") to avoid version-specific references. Either way you set the connection string and call .Open. For SQL Server a modern string looks like Provider=MSOLEDBSQL;Server=.;Database=Sales;Trusted_Connection=yes;. Always close the connection in cleanup and set it to Nothing to release the COM object promptly.

🏏

Cricket analogy: Early binding is naming your XI before the toss so everyone knows their role; late binding is picking a super-sub on the day. Opening the connection is walking out to the middle once the umpire signals play.

vba

Sub LoadCustomers()
    Dim conn As Object, rs As Object, cmd As Object
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=MSOLEDBSQL;Server=.;Database=Sales;Trusted_Connection=yes;"

    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = "SELECT Id, Name, City FROM Customers WHERE City = ?"
    cmd.Parameters.Append cmd.CreateParameter("@city", 200, 1, 50, "London")

    Set rs = cmd.Execute

    ' CopyFromRecordset dumps all rows starting at A2
    If Not rs.EOF Then
        ThisWorkbook.Sheets("Data").Range("A2").CopyFromRecordset rs
    End If

    rs.Close: conn.Close
    Set rs = Nothing: Set cmd = Nothing: Set conn = Nothing
End Sub

Parameterized Commands Beat String Concatenation

Building SQL by gluing user input into a string invites SQL injection and breaks on values containing apostrophes or the wrong date format. The Command object solves this: put ? placeholders in the SQL, append typed Parameter objects with CreateParameter(name, type, direction, size, value), and let the provider handle quoting and conversion. This is safer, and because the statement is prepared, it can also be reused efficiently for many parameter sets in a loop.

🏏

Cricket analogy: Concatenating input is like a no-ball you don't notice until it's punished; parameters are the crease markings that keep every delivery legal, so a name like O'Brien never oversteps into an injection.

CopyFromRecordset is the fastest way to spill query results onto a sheet — it writes the whole recordset in one operation. It does not write headers, so loop over rs.Fields to place column names first if you need them.

Always close Recordset and Connection objects and set them to Nothing, ideally in an error handler, or you'll leak connections and eventually exhaust the pool. Also match the connection string's provider to what is installed: MSOLEDBSQL is current for SQL Server, while the old SQLOLEDB and Jet providers may be missing on 64-bit Office.

ADO exposes Connection, Command, and Recordset objects for database access from VBA.
You can early-bind via a library reference or late-bind with CreateObject for version independence.
A connection string specifies the provider/driver, server, database, and authentication.
Use Command with ? placeholders and typed Parameters to prevent SQL injection and formatting errors.
CopyFromRecordset dumps an entire recordset to a range in one fast operation, but omits headers.
Close and release every ADO object, and match the provider to what is installed on the machine's bitness.