As discussed in the section, "Executing
Queries" the ADO Command object is similar to the
DAO QueryDef object in that it specifies an SQL string
and parameters and executes the query. However, unlike the DAO QueryDef object, the ADO Command object
cannot be used directly to persist a query. By specifying a name for the QueryDef when it is created, the DAO QueryDef
is automatically appended to the QueryDefs collection
and persisted in the database. This differs from ADO in which all Command objects are temporary queries. You must explicitly
append the Command to the ADOX Procedures
or Views collection in order to persist it in
the database.
The Microsoft Jet Provider defines Microsoft Jet
queries as Views if the query is a row-returning,
non-parameterized query. The provider defines a procedure as either a non
row-returning query (a bulk operation) or a parameterized row-returning query.
Creating a Stored Query
The following listings demonstrate how to create
a row returning, non-parameterized query.
DAO
Sub DAOCreateQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create query
Set qry = db.CreateQueryDef("AllCategories", _
"SELECT * FROM Categories")
db.Close
End Sub
ADOX
Sub ADOCreateQuery()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the query
cmd.CommandText = "SELECT * FROM Categories"
cat.Views.Append "AllCategories", cmd
Set cat = Nothing
End Sub
In this example, because the SQL statement is a
non-parameterized, row-returning query, the ADO Command
object is appended to the ADOX Views collection. Note,
that when using the Microsoft Jet Provider, it is possible to append a Command object to either the Views or
Procedures collection regardless of the type of query
that is being created. However, if a query such as the one in this example is
appended to the Procedures collection, then the Procedures and Views collections are
refreshed, you'll notice that the query is no longer in the Procedures
collection, but is now in the Views collection.
Likewise, you can append a parameterized query,
or a non row-returning bulk operation query to either the Views
or Procedures collection. However, ADOX will
actually store these types of queries in the Procedures collection.
If you append to the Views collection, then refresh
both the Views and Procedures collections,
you'll find that the newly appended query is now in the Procedures
collection.
Creating a Parameterized Stored Query
The following listings demonstrate how to create
a parameterized query and save it in the database.
DAO
Sub DAOCreateParameterizedQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create query
Set qry = db.CreateQueryDef("Employees by Region", _
"PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion]")
db.Close
End Sub
ADOX
Sub ADOCreateParameterizedQuery()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the Command
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion]"
' Create the Procedure
cat.Procedures.Append "Employees by Region", cmd
Set cat = Nothing
End Sub
The code for creating a parameterized query is
very similar using DAO and ADOX. Note, though that although the ADO Command object allows you to create parameters using the CreateParameter method, this information will not be saved
when creating or updating a Procedure. You must specify
the parameters as part of the SQL string.
Also note that Microsoft Jet will interpret the
SQL statement differently when a query is created with ADOX and the Microsoft
Jet Provider rather than DAO. The Microsoft Jet Provider always sets a
Microsoft Jet database engine option for ANSI compliance. This may cause
differences in behavior between DAO and ADO when creating or executing queries.
For example, if the SQL statement in the code above had been written as
follows:
"PARAMETERS [prmRegion] TEXT;" & _
"SELECT * FROM Employees WHERE Region = [prmRegion]"
omitting the (255) after the Text keyword, the
parameter would be created as a text field (dbText, adVarWChar) when using DAO, but as a memo field (dbMemo, adLongVarWChar) when using
ADO.
Further, some SQL statements that execute when
using DAO will fail to execute when using ADO due to additional reserved words.
For a list of reserved words, see "Appendix D: Microsoft Jet 4.0 ANSI
Reserved Words."
Modifying a Stored Query
The following listings demonstrate how to modify
an existing query.
DAO
Sub DAOModifyQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Get the query
Set qry = db.QueryDefs("Employees by Region")
' Update the SQL and save the updated query
qry.SQL = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
db.Close
End Sub
ADO
Sub ADOModifyQuery()
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Get the query
Set cmd = cat.Procedures("Employees by Region").Command
' Update the SQL
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
' Save the updated query
Set cat.Procedures("Employees by Region").Command = cmd
Set cat = Nothing
End Sub
In the ADO code, setting the Procedure
object's Command property to the modified Command object saves the changes. If this last step were not
included, the changes would not have been persisted to the database. This
difference results from the fact that ADO Command objects
are designed as temporary queries while DAO QueryDef objects
are designed as saved queries. You need to be aware of this when working with Commands, Procedures, and Views. You may think that the following ADO code examples are
equivalent:
Set cmd = cat.Procedures("Employees by Region").Command
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
Set cat.Procedures("Employees by Region").Command = cmd
and
cat.Procedures("Employees by Region").CommandText = _
"PARAMETERS [prmRegion] TEXT;" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
However, they are not. Both will compile, but
the second piece of code will not actually update the query in the database. In
the second example, ADOX will create a tear-off command object and hand it back
to Visual Basic for Applications. Visual Basic for Applications will then ask
ADOX to update the CommandText property, which it does.
Finally, Visual Basic for Applications moves to execute the next line of code
and the Command object is lost. ADOX is never asked to
update the Procedure with the changes to the modified Command object.
Creating an SQL Pass-Through Query
SQL pass-through queries are SQL statements that
are sent directly to the database server without interpretation by the
Microsoft Jet database engine. When creating an SQL pass-through query, you
must specify the SQL statement to execute as well as an ODBC connection string.
With DAO, pass-through queries provide a means
of improving performance when accessing external ODBC data. With ADO, it is not
necessary to create SQL pass-through queries in your Microsoft Jet database in
order to have good performance when accessing external data. With ADO, you can
use the Microsoft OLE DB Provider for SQL Server to directly access SQL Server
without the overhead of Microsoft Jet or ODBC. You can also use the Microsoft
OLE DB Provider for ODBC to access data in any ODBC data source.
While it is no longer necessary to create SQL
pass-through queries in your Microsoft Jet database, it is still possible to do
so using ADOX and the Microsoft Jet Provider. The following code demonstrates
how to create an SQL pass-through query.
DAO
Sub DAOCreateSQLPassThrough()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create query
Set qry = db.CreateQueryDef("Business Books", _
"SELECT * FROM Titles WHERE Type = 'business'")
qry.Connect = "ODBC;DSN=ADOPubs;UID=sa;PWD=;"
qry.ReturnsRecords = True
db.Close
End Sub
ADOX
Sub ADOCreateSQLPassThrough()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the Command
Set cmd.ActiveConnection = cat.ActiveConnection
cmd.CommandText = "SELECT * FROM Titles WHERE Type = 'business'"
cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
cmd.Properties("Jet OLEDB:Pass Through Query Connect String") = _
"ODBC;DSN=ADOPubs;database=pubs;UID=sa;PWD=;"
' Create the Procedure
cat.Procedures.Append "Business Books", cmd
Set cat = Nothing
End Sub
EmoticonEmoticon