Using ADO in VB and Access
ActiveX Data Objects (ADO) Overview
ActiveX Data Objects (ADO) Design Philosophy
ActiveX Data Objects (ADO) was originally designed as a simple and relatively lightweight COM based database API for use with Active Server Pages (ASP) on Internet Information Server (IIS). ADO was designed from the ground up to be thread-safe, highly memory efficient, and easily remotable (which is highly desired when using any API with IIS). Also, ADO is the first high level API from Microsoft that supports the new OLE DB core API (OLE DB is Microsoft’s new COM based database core API which is destined to eventually replace the ODBC API). The Microsoft Data Access Group felt that it was a better idea to create a total new API rather than try to extend the existing DAO and RDO API’s to support OLE DB and the various other “IIS Friendly” characteristics. DAO and RDO were written when all of the database clients were single threaded monolithic applications – hence they were not written from the ground up to be thread safe. Microsoft will continue to support DAO and RDO but no new functionality is planned. The long term plan is to get our customers to migrate to ADO and to focus our efforts on making ADO the high level API of choice for all database clients.
The ADO Object Model Overview
The ADO object model is very simple (3 major objects and 4 minor objects) and fairly simple and intuitive compared to Microsoft’s previous database object models:
Connection - Maintains connection information with the data provider. Includes methods for conducting database transactions (BeginTrans, CommitTrans, RollbackTrans). Includes an Execute method for executing SQL Data Manipulation Language (DML) and Data Definition Language (DDL) statements that do not require parameters. Includes an OpenSchema method for querying a database system for schema (sometimes called meta) information such as lists of system tables, indexes on tables, etc…
Command - Maintains information about a command, such as a query string, parameter definitions, etc. You can execute a command string on a Connection object or a query string as part of opening a Recordset object, without defining a Command object. The Command object is useful where you want to define query parameters, or execute a stored procedure that returns output parameters. Basically, the Command object is useful for preparing a SQL statement and providing parameters to the statement if needed.
Recordset - A set of records returned from a query and a cursor into those records. You can open a Recordset (i.e., execute a query) without explicitly opening a Connection object. However, if you do first create a Connection object, you can open multiple Recordset objects on the same connection. The ADO Recordset is very similar to DAO’s Recordset and RDO’s rdoResultset object. The ADO Recordset has the additional advantage of allowing complete disconnection from the back-end database system (this is called a “disconnected” Recordset) as well as built-in cross-process and cross-machine remotability.
Field - Contains information about a single column of data within a Recordset. The Recordset object features a Fields collection to contain all of its Field objects. With the Field object you can gather information about a column in a table, such as the data-type of the column, nullability, and the amount of data it can hold (maximum characters for example). The ADO Field object is similar to both DAO’s and RDO’s Field/rdoField objects.
Parameter - A single parameter for a parameterized Command. The Command object features a Parameters collection to contain all of its Parameter objects. ADO’s Parameter’s collection also has a handy “auto-populate” feature which makes it much easier to use with SQL Server stored procedures and for parameterized SQL statements if the driver is sophisticated enough to provide parameter meta-information (SQL Server and Oracle Drivers are good in this respect -- the Microsoft Access ODBC Driver is not as good).
Error - Contains extended error information about an error condition raised by the provider. Since a single statement can generate two or more errors, the Errors collection can contain more than one Error object at a time, all of which result from the same incident. Similar to Error/rdoError in DAO/RDO.
Property - A provider-defined characteristic of an ADO object. Every ADO object contains a properties collection which can be iterated programmatically.
Connecting To Databases With ADO
Connecting using Connection.Open
To connect to a database in ADO, you can use one of two general methods. The first method is to use the Connection.Open method. The resulting opened connection object can be used directly or passed to other ADO objects.
Note that ADO provides access to both OLE DB providers and ODBC drivers. ADO does this by using a special OLE DB provider that translates ADO’s OLE DB calls to correspondingly equivalent ODBC calls. This translator provider is code named “Kangera”and it’s provider name is “MSDASQL”. The MSDASQL provider is the default provider for ADO. In other words, if you do not explicitly specify a provider, then the MSDASQL provider is used automatically. This makes using ADO with ODBC drivers very straightforward. If the customer understands ODBC connection strings, then connecting to an ODBC driver using ADO will be very simple. Just pass the ODBC connection string as the first parameter to the Connection.Open method:
Sub ADO_ODBC_CONNECTION_TEST()
Dim conn As New ADODB.Connection
conn.Open "DSN=LocalServer;DATABASE=pubs;UID=sa;PWD=;"
If conn.State = adStateOpen Then
Debug.Print "Connection successfully opened."
Else
Debug.Print "Connection failed."
End If
End Sub
If the customer wants to use an OLE DB provider, then the specific provider name must be set to over-ride the MSDASQL default provider. This can be set individually (by using the Provider property of the Connection object) or by adding a PROVIDER= statement to the OLE DB connection string as below:
Sub ADO_OLEDB_CONNECTION_TEST()
Dim conn As New ADODB.Connection
conn.Provider = "SQLOLEDB"
conn.Open "SERVER=UKDUDE;DATABASE=Pubs;UID=sa;PWD=;"
If conn.State = adStateOpen Then
Debug.Print "Connection successfully opened."
Else
Debug.Print "Connection failed."
End If
End Sub
Once the Connection object is open, you can then pass the connection to an ADO Command or Recordset object by setting the next object’s ActiveConnection property to the connection object:
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
‘ Open connection ...
Set rs.ActiveConnection = conn
Please note the use of the “Set” keyword here which is required when assigning object type variables in VBA.
Connecting via connection string passed to a Command or Recordset Object
ADO also has a short-hand method of opening a database connection that bypasses the connection object altogether. Both the recordset and the command object allow you to pass in a connection string instead of a connection object to their respective ActiveConnection properties. Just set the Command/Recordset’s ActiveConnection property to the desired connection string, and the object is ready to use.
Sub ADO_COMMAND_CONNECTION_TEST()
Dim cmd As New ADODB.Command
Dim rs As ADODB.recordset
Dim strConn As String
cmd.ActiveConnection = " DRIVER={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
cmd.CommandText = "byroyalty"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 25
Set rs = cmd.Execute
' Recordset now has authors with 25% royalty.....
End Sub
Note that here I did not use the “Set” keyword as in the previous example. I am assigning a string to a property which is not the same as assigning a VBA object.
Note that this second method is actually a more desired approach than explicitly using a connection object in code. In general, most ODBC drivers do not support more than one active statement per connection. This means that sharing a single Connection object over more than one Command/Recordset object can cause errors due to multiple active statements running. If the customer uses the connection string approach, then each Command/Recordset object will have it’s own individual connection object internally and “multiple active statements per connection” errors will be avoided.
Sample ODBC and OLE DB Connection Strings
Many customers are confused by ODBC and OLE DB connection strings. One of the most helpful articles I found to explain various connection strings is the “Setting Connection String Parameters in DAO” whitepaper.
Here are some simple examples to connect to various ODBC drivers and OLE DB Providers.
Microsoft Access:
ODBC = “DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\NW.MDB”
OLE DB = “PROVIDER=Microsoft.JET.OLEDB.3.51;DATA SOURCE= C:\NW.MDB”
Microsoft SQL Server:
ODBC = “DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
OLE DB = PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
Microsoft Oracle
ODBC = “DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
OLEDB = “PROVIDER=MSDAORA;SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
Microsoft Excel
ODBC= "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Book1.xls"
Microsoft FoxPro
ODBC=”DRIVER={Microsoft FoxPro Driver (*.dbf)};DBQ=C:\FoxFiles;”
Using the ADO Command Object
General Command Object Issues
The ADO Command object is used when one needs to provide parameter information to a stored procedure, a SQL DML statement, or a SQL select statement. Parameters are indicated by embedding a single question mark in the location when the parameter is desired. Parameters can only replace literal values in a SQL statement, they cannot be used to indicate a variable field names for example.
Here are some example SQL statements that use parameters:
select * from authors where au_id=’243-11-2334’
select * from authors where au_id=?
insert into MyTable (field1,field2) values (1,’hello’)
insert into MyTable (field1,field2) values (?,?)
{call MyStoredProcedure(‘la’,’dee’,’dah’)}
{call MyStoredProcedure(?,?,?)
Parameter markers are simply replaced from left to right as encountered. The first parameter encountered is parameter 0 (parameter’s collection is zero based).
ADO provides an “auto-populate” feature for parameters which is quite handy. If the driver supports it, you can call ADO’s Parameters.Refresh method and this will automatically build the parameters collection for you (rather than manually adding each parameter one at a time programmatically).
Sub ADO_PARAM_TEST()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
cmd.ActiveConnection = "Driver={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
cmd.CommandText = "select * from authors where au_id=?"
cmd.CommandType = adCmdText
cmd.Parameters.Refresh ‘ Auto-populate here…
cmd.Parameters(0).Value = "213-46-8915"
Set rs = cmd.Execute
' Read record here...
End Sub
For more information on manually populating the parameters collection, see the online examples in the ADO documentation. Unfortunately, the Microsoft Access ODBC and OLE DB drivers do not currently support automatic parameter population. This has been submitted as a feature request for future versions of the driver.
Using Stored Procedures With Command Objects
When calling a stored procedures using the Command object, set the Command’s CommandText to just the name of the stored procedure, then set the CommandType property to the adCmdStoredProc constant to let ADO now that the SQL statement in the CommandText property is a stored procedure.
Sub ADO_STORED_PROC_TEST()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
cmd.ActiveConnection = "Driver={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
cmd.CommandText = "byroyalty"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
' Skip parameter 0 which is the return value!
cmd.Parameters(1).Value = 25
rs.Open cmd, , adOpenStatic, adLockOptimistic, -1
End Sub
You can determine which parameters are bound by running the code example in the following KB article against your particular stored procedure:
HOWTO: Determine How ADO Will Bind Parameters
https://support.microsoft.com/kb/q181199/
Using The ADO Recordset Object
General Recordset Issues
The ADO Recordset object is very similar to the Recordset objects in Microsoft’s previous database API’s. The Recordset has the concept of a “current record” or “record pointer” which points to the currently selected record. The programmer can move the current record pointer forwards and backwards by using the MoveNext and MovePrevious methods. In most cases the developer simply loops through a set of records until the Recordset EOF flag is set to True:
Dim rs As New ADODB.Recordset
rs.ActiveConnection = "Driver={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
rs.Open “select * from authors”
While Not rs.EOF
‘ Process record here…
Rs.MoveNext
Wend
The developer can also use the Fields collection to dynamically gather more information about the various columns in the recordset. The Fields collection of the Recordset object allows the “For Each” VBA collection syntax which makes coding quite efficient:
Dim rs As New ADODB.Recordset
Dim f As ADODB.Field
‘ Open recordset...
While Not rs.EOF
For Each f In rs.Fields
‘ Display various field properties.
Debug.Print f.Name & “=” & f.Value
Next f
Rs.MoveNext
Wend
Comments
Anonymous
March 19, 2008
Your code works great. I'm fairly new to VBA and SQL, so this was a big help.Anonymous
October 23, 2009
10q for ur help! it was my problem for long time. Meet u next time in other portion of database and visual basic programming portion.