ADO Connection Issues in Production
ADO (ActiveX Data Objects) is a Microsoft data access API that's been around since the late 90s. It's still used everywhere in legacy systems, which means you'll likely run into it whether you want to or not. The interface itself is straightforward, but the places where things go wrong are nowhere near obvious. Here's how a typical connection object looks when you're actually using it, not the sanitized version from documentation: Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVER01;Initial Catalog=ProductionDB;User ID=appuser;Password=p@ssw0rd"
conn.CommandTimeout = 30
conn.Open
The CommandTimeout setting is something nobody thinks about until a query hangs for 120 seconds and blocks your entire thread pool. I had a deployment where a stored procedure was doing a full table scan on a 40-million-row fact table, and because I hadn't set the timeout, the connection would sit there silently. Set it to 30 and wrap your calls in error handling. The default is 0, which means no timeout, which means your app just stalls forever. For recordset operations, the Execute method is the standard path: Dim rs As New ADODB.Recordset
rs.Open "SELECT Id, Name, Status FROM Customers WHERE Region = 'WEST'", conn, adOpenStatic, adLockReadOnly
The cursor and lock type choices matter more than people admit. adOpenStatic gives you a snapshot, which is fine for read-only reporting. adOpenDynamic keeps the data live but adds overhead. If you're building a grid control or doing batch processing, static is what you want. I spent an afternoon debugging records that kept disappearing between one iteration and the next because I'd used adOpenDynamic on a multi-user connection without realizing the underlying data was being modified by other sessions.
Get the Full Details
Parameterized Queries
String concatenation for SQL queries is the fastest way to introduce SQL injection vulnerabilities and get someone fired. Here's the correct approach: Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE Orders SET Status = ? WHERE OrderId = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("@Status", adVarChar, adParamInput, 20, "Shipped")
cmd.Parameters.Append cmd.CreateParameter("@OrderId", adInteger, adParamInput, , 45892)
cmd.Execute ADO parameter binding handles type conversion automatically. That means you don't need to format dates as strings or worry about locale-specific number formats. It also means the database engine treats parameters as data, not as executable SQL. This is non-negotiable for anything touching user input.
Batch Operations and Performance
When you need to process more than a handful of rows, recordset navigation in a loop is slow. The MoveNext approach works but becomes painful around 10,000 rows. If you're doing bulk updates, use a command object with a loop instead of a recordset: Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE Inventory SET Quantity = Quantity + ? WHERE SKU = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("@Qty", adInteger, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@SKU", adVarChar, adParamInput, 50)
Dim row As Variant
For Each row In myDataArray
cmd.Parameters("@Qty").Value = row(0)
cmd.Parameters("@SKU").Value = row(1)
cmd.Execute
Next For truly large datasets, disable server-side cursors and use client-side caching. Set the CursorLocation to adUseClient before opening your recordset. This moves the cursor management to your application memory rather than the database server, which eliminates a lot of the round-trip overhead and lets you do operations like Find or Filter without hitting the server again. The tradeoff is memory usage on your machine, but for anything under 500,000 rows it's usually worth it.
Resource Management
The most common issue I see is connections and recordsets being left open. ADO objects don't garbage collect cleanly in VB6 and early VBA environments. You need to explicitly close and release them: If Not rs Is Nothing Then
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End If
If Not conn Is Nothing Then
If conn.State = adStateOpen Then conn.Close
Set conn = Nothing
End If The State check is important because calling Close on an already-closed object throws a runtime error. In a production system with hundreds of users, missing these cleanup steps will leak connections until your database server refuses new connections. I once traced a "database unavailable" error back to a single developer who had forgotten this pattern in a background worker that ran for 18 hours straight. The connection pool was exhausted by noon every day.
Known Limitations
ADO has significant constraints that aren't obvious if you're coming from modern ORMs. It doesn't support async operations natively, so long-running queries block your thread. It has no built-in migration framework, so schema changes are manual. The COM-based architecture means deployment requires registered DLLs on the target machine, which causes issues in containerized or minimal environments. For new projects, consider whether ADO is actually the right tool or if a newer data access layer would serve you better. The provider ecosystem is also limited. SQLOLEDB works for SQL Server but has known issues with encryption negotiation on newer versions. The newer MSOLEDBSQL provider exists but requires explicit installation on the target system, and not all hosting environments include it. If you're building something that needs to run on multiple Windows setups, test the provider availability first rather than assuming it's there.
