Creating and Modifying Stored Queries

  • Article

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.

Although it is possible to create and modify a stored query in an Access database by using ADOX, if you do so your query won't be visible in the Access Database window or 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, or 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, and another new mode that supports the new Jet SQL commands and syntax that are more compliant with the ANSI SQL-92 standard.

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.

For information about how to create stored queries by using ADOX code, see ADOCreateQueries.doc in the Appendixes folder on the companion CD-ROM. ADOCreateQueries.doc also contains additional information about using the new Jet 4.0 SQL commands and syntax. 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 4.0 SQL commands and syntax in ADO code that is 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 the new commands. However, you can't use the new Jet 4.0 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 the following section, "Running a Temporary Query."