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.
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 SubParameterized 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.
Practice what you learned
1. Which three objects form the core of ADO in VBA?
2. What is the main advantage of using a Command with parameters over concatenating SQL strings?
3. What does Range("A2").CopyFromRecordset rs do?
4. Why prefer CreateObject("ADODB.Connection") (late binding) in some deployments?
5. What is a common cause of a connection-pool exhaustion problem in ADO code?
Was this page helpful?
You May Also Like
Reading and Writing Cell Data
How to read from and write to worksheet cells in VBA using the Range and Cells objects, and how to move data efficiently between the sheet and VBA arrays.
Dictionaries and Collections in VBA
Comparing VBA's built-in Collection with the Scripting.Dictionary for storing keyed data, and choosing the right one for lookups, de-duplication, and counting.
Automating Outlook and Word from VBA
Driving Outlook and Word from Excel VBA through Automation: creating application objects, composing and sending emails, and generating Word documents from templates.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics