Creating and Modifying Stored Queries in Microsoft Access Databases with ADOX
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Microsoft Corporation
January 1999
**Important **Although it is possible to create and modify a stored query in an Access database by using Microsoft ActiveX® Data Objects Extensions for Data Definition Language and Security (ADOX), if you do so your query won't be visible in the Access Database window or in any other part of the Access user interface—for example, you can't set the RecordSource property of a form to a query created with ADOX, nor can you import a query created with ADOX into another database. However, you can still run stored queries created by using ADOX from ADO code.
This is so because the Microsoft Jet 4.0 database engine can run in two modes: one mode that supports the same Jet SQL commands used in previous versions of Access, a new mode that supports new Jet SQL commands and syntax that are more compliant with the ANSI SQL-92 standard.
Queries created with ADOX can support the new Jet SQL mode, and so are flagged internally to identify them as using that mode, whether the query contains the new commands or not. Access 2000 can open an Access database only while using the mode that supports the older Jet SQL commands and syntax. To prevent error messages and conflicts between the new Jet SQL commands and syntax and the Access query editing tools, Access hides queries that are flagged as containing the new Jet SQL commands and syntax. For this same reason, if you try to create a query in an SQL view window in the Access user interface by typing Jet SQL statements that contain the new commands and syntax, Access will return a syntax error message when you try to save the query. Additionally, if a user opens a database containing queries that were created and saved by using ADOX, and that user tries to save a new query to the same name as an ADOX query, Access displays a message that a query of the same name exists and provides the user with the option to overwrite the query.
For these reasons, you should use ADOX to create queries only in a database that you will use as a code library, or in a solution that doesn't expose the Database window to users. If you need to use code to create stored queries that are available from the Access user interface, you must use the DAO CreateQueryDef method to do so. For information about how to do so, see Microsoft DAO 3.6 Help or the Microsoft Jet Database Engine Programmer's Guide, Second Edition (Microsoft Press®, 1997).
You can still use the new Jet SQL commands and syntax from ADO code running against the Microsoft Jet 4.0 OLE DB Provider. Because ADO opens a new connection to the database, ADO is always able to open this connection while using the mode of the Jet database engine that supports these commands. However, you can't use the new Jet SQL commands and syntax from DAO code because DAO isn't able to open the database while using a mode that supports these commands. For information about how to run SQL commands without using a stored query, see "Running a Temporary Query," in Chapter 14, "Working with the Data Access Components of an Office Solution," in the Microsoft Office 2000/Visual Basic® Programmer's Guide (Microsoft Press, 1999).
Creating and Modifying Stored Queries
The ADO Command object is similar to the DAO QueryDef object in that it is used first to specify an SQL string and any parameters, then to execute the query. However, unlike the DAO QueryDef object, the ADO Command object is inherently a temporary object. In DAO, if you specify a name for a QueryDef object when it is created, the object is automatically appended to the QueryDefs collection and saved in the database. To save a query in a database when using ADO, you must append a Command object to the ADOX Views or Procedures collections, as described in the following sections.
Additionally, the ADOX Views and Procedures collections are used to distinguish between the kinds of queries. A View object is used to work with queries that return records and have no parameters; a Procedure object is used to work with any query that doesn't return records, such as an update or delete query, or a query with parameters that returns records.
Creating a Query Without Parameters That Returns Records
The following example uses a Command object to create (but not execute) a query that returns records and has no parameters. (In Access, this is called a select query.) To save the query, you append the Command object to the database's Views collection.
Sub CreateQuery(strDBPath As String, _
strSQL As String, _
strQryName As String)
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath
Set cmd = New ADODB.Command
' Define a Command object to contain the query's SQL, and then
' save it to the database's Views collection. StrSQL must
' contain only a SELECT statement.
cmd.CommandText = strSQL
catDB.Views.Append strQryName, cmd
Set catDB = Nothing
End Sub
For example, to use this procedure to create a query named AllBeverages that returns all products that are beverages from the Northwind database, you can use a line of code like this:
CreateQuery "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"SELECT Products.* FROM Products WHERE Products.CategoryID=1;", _
"AllBeverages"
The CreateQuery procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the Samples\CH14 subfolder on the companion CD-ROM to the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999).
Creating a Parameter Query
Creating a parameter query is practically identical to creating a query without parameters that returns records, except that you use the Procedures collection to save the query. Even though the Command object provides a CreateParameters method that allows you to create Parameter objects on a Command object, these parameters are not saved if you append the Command object to the Procedures collection. To save the parameters with your query, you must define them by using a PARAMETERS declaration as part of the SQL statement defined in the CommandText property for the Command object.
The following code shows how to create a parameter query by using the ADOX Procedures collection.
Sub CreateParamsQuery(strDBPath As String, _
strSQL As String, _
strQryName As String)
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Set catDB = New ADOX.Catalog
' Open the Catalog object.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath
'Create a Command object to contain the query's definition.
Set cmd = New ADODB.Command
cmd.CommandText = strSQL
'Add the query to the Procedures collection.
catDB.Procedures.Append strQryName, cmd
Set catDB = Nothing
End Sub
For example, to use this procedure to create a parameter query named Employees by Region that returns all employees from the Northwind database based on the value of a parameter named prmRegion, you can use a line of code like this:
CreateParamsQuery "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"PARAMETERS prmRegion Text; SELECT * FROM Employees " & _
"WHERE Region = prmRegion", _
"Employees by Region"
The CreateParamsQuery procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the Samples\CH14 subfolder on the companion CD-ROM to the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999).
Modifying a Query
To modify a query, retrieve the Command object for the query from either the Views or Procedures collection, update the CommandText property setting for the query, then save the query back to the appropriate collection. The following code example shows how to modify an existing query.
Sub ModifyQuery(strDBPath As String, _
strQryName As String, _
strSQL As String)
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Set catDB = New ADOX.Catalog
' Open the Catalog object.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath
Set cmd = New ADODB.Command
' Get the query from the Procedures collection.
Set cmd = catDB.Procedures(strQryName).Command
' Update the query's SQL statement.
cmd.CommandText = strSQL
'Save the updated query.
Set catDB.Procedures(strQryName).Command = cmd
Set catDB = Nothing
End Sub
For example, to use this procedure to update the parameter query created by the previous example so that it now sorts by the City field, you'd use a line of code like this:
ModifyQuery "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"Employees by Region", "PARAMETERS prmRegion Text;" _
& "SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City;"
The ModifyQuery procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the Samples\CH14 subfolder on the companion CD-ROM to the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999).
Note that the process of updating a saved query when you are using ADO and ADOX is somewhat different from the process when you are using DAO. In DAO, QueryDef objects are designed as saved queries, so opening and changing an existing QueryDef object's sqltext argument automatically saves the changes to the saved query. In ADO, a Command object is inherently a temporary object. You need to be aware of this when you are working with Command objects and the Procedures and Views collections. For example, you may think that the following ADO code fragments are equivalent
Set cmd = catDB.Procedures("Employees by Region").Command
cmd.CommandText = "PARAMETERS prmRegion Text;" & _
"SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City"
Set catDB.Procedures("Employees by Region").Command = cmd
and:
catDB.Procedures("Employees by Region").CommandText = _
"PARAMETERS prmRegion Text;" & _
"SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City"
However, they aren't. Both will compile, but the second piece of code won't actually update the query in the database. In the second example, ADOX will create a temporary Command object and update its CommandText property, but the Command object will not be saved to the Procedures collection.
Creating an SQL Pass-Through Query
An SQL pass-through query bypasses Microsoft Jet's query processor and sends an SQL statement directly to an ODBC data source, such as SQL Server™. The SQL statement must use the appropriate keywords and syntax for the ODBC data source (not Jet SQL keywords and syntax) and can perform any action supported by that data source. As a result, an SQL pass-through query may or may not return records, depending on the commands in the SQL statement. ADO always saves SQL pass-through queries in the Procedures collection, regardless of whether records are returned or not. To create an SQL pass-through query, you must set two provider-specific properties of the Command object. Set the Jet OLEDB:Pass-Through Statement property to True, and set the Jet OLEDB:Pass Through Query Connect String property to a valid ODBC connection string. The following code example shows how to create an SQL pass-through query.
Sub CreatePassThroughQry(strDBPath As String, _
strSQL As String, _
strODBCConnect As String, _
strQryName As String)
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Dim varProp As Variant
Set catDB = New ADOX.Catalog
' Open the Catalog object.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath
Set cmd = New ADODB.Command
' Define SQL statement for query and set provider-specific
' properties for query type and ODBC connection string.
With cmd
.ActiveConnection = catDB.ActiveConnection
.CommandText = strSQL
.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
.Properties("Jet OLEDB:Pass Through Query Connect String") = _
strODBCConnect
End With
' Name and save query to Procedures collection.
catDB.Procedures.Append strQryName, cmd
Set catDB = Nothing
End Sub
For example, to use this procedure to create a pass-through query that returns only books with business titles from the Titles table in the SQL Server Pubs sample database, you'd use a line of code like this:
CreatePassThroughQry _
"c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"SELECT * FROM Titles WHERE Type = 'business'", _
"ODBC;DSN=pubs;UID=<your_user_name>;PWD=<your_password>;", _
"Business Titles"
---------------------------------------------
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, this paper should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft, ActiveX, Microsoft Press, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.