|
Using ADO in VB
and Access
ActiveX Data
Objects (ADO) Design Philosophy
ActiveX Data Objects
(ADO) was originally designed as a simple and relatively
lightweight COM based database API for 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 totally new API rather than extending
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 are 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 will be planned. The long term plan is to get the
customers to migrate to ADO and to focus the 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. This includes
methods for making database transactions (BeginTrans,
CommitTrans and RollbackTrans). Also, it includes an Execute
method for executing SQL Data Manipulation Language (DML) and
Data Definition Language (DDL) statements that do not require
parameters. There is 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
– It holds
information about a command such as a query string, parameter
definitions, etc. You can also 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 when 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
- It is a
container that holds the set of records returned from a database
through a query or stored procedures. 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
– It 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 for holding all of its
Parameter objects. Also, ADO’s Parameter’s collection has a
handy “auto-populate” feature that makes it much easier to use
with SQL Server stored procedures and parameterized SQL
statements provided 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
To connect to a
database with ADO you can use one of two general methods.
·
Connection.Open method
·
Using
Command or Recordset Object
Connecting using
Connection.Open
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 its 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.
Using 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 will allow you to pass 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 when using 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.
http://msdn.microsoft.com
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 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
http://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
Conclusion
Thus we have learnt some basics of
ADO with examples in VB. Also, this article will help you to
solve some of the common problem that may occur when we use ADO.
Happy Coding!!!
|
