Establishing a Connection to Your Database
In any data‑centric application, the first step is to open a communication line with the database server. In ADO, that line is an instance of the Connection object. Think of it as dialing a number on your phone before you can talk to someone. To create the Connection, you write:
Dim conn As New ADODB.Connection
Once you have the object, the next move is to feed it a connection string. This string is a compact recipe that tells the engine where the database lives, which provider to use, and what credentials to present. For example, a typical SQL Server string might look like:
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=MyDatabase;User ID=sa;Password=YourPassword;"
Notice that the string packs several key/value pairs. The provider keyword determines the underlying driver, Data Source is the server name or IP, Initial Catalog picks the database, and User ID/Password provide authentication. Some providers also allow flags for encryption, timeout, or pooling. Tailor the string to match your environment.
With the string ready, you establish the session with:
conn.Open
The Open method attempts to connect and throws a runtime error if something goes wrong - such as a wrong password or an unreachable server. You should wrap the call in error handling to catch these situations gracefully. A simple pattern is:
On Error GoTo ConnectError
conn.Open
On Error GoTo 0
Exit Sub
ConnectError:
MsgBox "Connection failed: " & Err.Description
Once opened, the Connection object stays active until you call Close or the object is destroyed. Closing frees network resources and allows other applications to use the server. A tidy practice is to close inside a Finally block or at the very end of your routine. In VBScript you can also rely on the object’s automatic cleanup when the script ends.
That’s the entire handshake. A single line of code creates the Connection, a string tells it where to go, and the Open method opens the door. Keep the Connection alive only as long as you need it; opening and closing repeatedly can hurt performance, but holding it open for a long time can lock resources. Find the sweet spot for your scenario.
Sending SQL Statements and Retrieving Results
Once a Connection is alive, the next logical step is to ask the database to do something. ADO offers several ways to send commands, but the most direct is the Command object. Think of a Command as a written letter you hand to the server: it contains the request and any attached data.
Creating a Command is straightforward:
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT FirstName, LastName FROM Employees"
cmd.CommandType = adCmdText
The CommandText holds the SQL text, and CommandType tells ADO that it is plain SQL. For stored procedures you would set CommandType to adCmdStoredProc. Once the Command is configured, you can get a Recordset by calling Execute:
Dim rs As ADODB.Recordset
Set rs = cmd.Execute
Execute returns a Recordset that contains the result rows. If you only need to read data, you can skip the Command object entirely and call:
Set rs = conn.Execute("SELECT FirstName FROM Employees")
Both approaches give you the same Recordset, but the Command approach shines when you need parameters, stored procedures, or a clear separation between connection and query logic. After the call, the Recordset is positioned at the first row of the result. If the query returns no rows, the Recordset is at EndOfFile immediately.
When you are done with the Command, you can release it by setting it to Nothing. This frees up memory but does not close the underlying Connection. The Recordset stays open until you close it or the Command is destroyed. Always remember to close the Recordset explicitly when you finish reading data.
This simple three‑line pattern - create a Command, set its text and connection, execute - forms the backbone of most ADO data access code. It keeps the logic clear and modular, making later changes easier to implement.
Navigating and Reading Data with Recordsets
With a Recordset in hand, the real work of data manipulation begins. A Recordset behaves like a cursor that can point to any row in a table or result set. The default type is adOpenForwardOnly, which is fast but only lets you move forward. For many read‑only scenarios, that is sufficient. If you need to scroll back and forth, choose adOpenStatic or adOpenKeyset, which create a snapshot of the data in memory or maintain a live link to the database, respectively.
Before reading, set the Recordset type explicitly to make your intentions clear:
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
The LockType defines how changes are handled - read‑only, optimistic, or pessimistic. In read‑only mode, the engine guarantees you won’t accidentally write back data.
To read column values, you can index the Fields collection by name or ordinal:
Dim firstName As String
firstName = rs.Fields("FirstName").Value
Dim lastName As String
lastName = rs.Fields(1).Value
Accessing by name is clearer, while ordinals can be faster when you are looping through many rows.
The most common iteration pattern looks like this:
Do While Not rs.EOF
Debug.Print rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value
rs.MoveNext
Loop
Check the EOF (End Of File) property before the loop to avoid errors on an empty result set. Similarly, if you need to work from the bottom up, you can use BOF (Beginning Of File) and MovePrevious. The cursor type you choose determines whether MovePrevious is supported; adOpenStatic does, but adOpenForwardOnly does not.
When dealing with large tables, consider using a forward‑only cursor combined with a read‑only lock. That way, ADO streams data from the server instead of loading everything into memory, which saves memory and speeds up the process for read‑only scenarios.
In short, the Recordset gives you a powerful, flexible way to traverse query results. Understanding cursor types, lock types, and how to read fields is essential for working effectively with ADO.
Using Parameterized Queries for Safety and Performance
Hard‑coding values directly into SQL strings opens the door to injection attacks and makes repeated queries slower because the database can’t reuse execution plans. ADO solves this by letting you create parameter placeholders in the command text and then supply the values separately.
Suppose you want to fetch employees by department ID. Instead of:
conn.Execute "SELECT * FROM Employees WHERE DeptID = " & DeptID
you build a Command with a placeholder and add parameters:
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Employees WHERE DeptID = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , DeptID)
Each question mark in the SQL matches a parameter in the order they’re appended. The CreateParameter method lets you specify data type, direction (Input, Output, or InputOutput), size, and the value. For string types, you need to provide the size so ADO can allocate the right buffer.
Once the parameters are set, call Execute to retrieve the Recordset:
Set rs = cmd.Execute
Parameterized queries give the database engine a chance to compile a plan once and reuse it for each different value. They also keep user input away from the SQL engine, which is a primary defense against injection.
When you need multiple parameters, add them in the same order as they appear in the SQL text:
cmd.CommandText = "SELECT * FROM Employees WHERE DeptID = ? AND Salary > ?"
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , DeptID)
cmd.Parameters.Append cmd.CreateParameter(, adMoney, adParamInput, , MinSalary)
Notice the use of adMoney for a currency value; choose the data type that matches your column to avoid type conversions that slow the query.
In practice, always use parameters for any user input, whether it’s a number, a string, or a date. The pattern is the same regardless of the language - VB.NET, C#, or VBScript - so the learning effort pays off across projects.
Updating, Inserting, and Deleting Records with Recordsets
Beyond reading data, ADO lets you edit it directly through the Recordset. If the cursor type and lock type permit, you can modify field values, create new rows, and flag rows for deletion, then push those changes back to the database with Update.
Begin by opening a Recordset that allows edits:
Set rs = conn.Execute("SELECT * FROM Employees WHERE DeptID = ?", adOpenKeyset, adLockOptimistic)
rs.LockType = adLockOptimistic
Once you have a writable Recordset, change a value like so:
rs.Fields("Salary").Value = rs.Fields("Salary").Value * 1.10
When you’re ready to commit, call:
rs.Update 
No comments yet. Be the first to comment!