Microsoft KB Archive/255782

= How To Use ADO and ADOX to Modify the Base Query of an Access QueryDef Object in Visual Basic =

Article ID: 255782

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q255782



SUMMARY
This article demonstrates how to use ActiveX Data Objects (ADO) and ADO Extensions for DDL and Security (ADOX) to modify the underlying SQL query of an Access 97 or Access 2000 QueryDef object from a Visual Basic application at run time.



MORE INFORMATION
The following example uses the Northwind Access database that ships with Visual Basic. For illustration purposes, a new Query object named "All Customers" is created in this database at design time using Access. The underlying query for the QueryDef object is a simple SQL SELECT statement that retrieves all the records in the Customers table. Using ADO and ADOX, the Visual Basic code modifies the SQL query of the "All Customers" QueryDef object.

Step-by-Step Example
 Use Microsoft Access to open the Northwind.mdb database, which is located in the Visual Basic installation directory. Create a new Query object in the database that retrieves all the records in the Customers table. Specify the SQL query for the new Query object as "Select * from Customers". Save the query object as "All Customers". Save your changes to the database, and exit Access. Create a new Standard EXE Project in Visual Basic. On the Project menu, click References, and select the Microsoft ActiveX Data Objects 2.1 library and '''Microsoft ADO Ext. 2.1 for DDL and Security''' check boxes. Add a command button to Form1.  Add the following code to the form's General Declarations section: Dim cn As Connection Dim mcat As ADOX.Catalog Dim mview As ADOX.View   Add the following code to the command button's Click event procedure: Private Sub Command1_Click Dim cmd As ADODB.Command

Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=nwind.mdb" Set mcat = New ADOX.Catalog Set mcat.ActiveConnection = cn

Set mview = mcat.Views("All Customers") Set cmd = mview.Command cmd.CommandText = "Select * from customers Order by CompanyName" Set mview.Command = cmd

MsgBox "Querydef [All Customers] has been modified !" Set mview = Nothing Set cmd = Nothing Set mcat = Nothing cn.Close End Sub The Query objects that are defined in an Access database are listed in the Views collection of the ADOX Catalog object. Each ADOX.View object has a Command property that contains a reference to an ADODB.Command object, which defines the underlying query of the QueryDef object. The preceding code modifies the CommandText property of this Command object to modify the query definition of the Access Query object. </li> Press the F5 key to run the project.</li> Click the command button that appears on the form. The code in the Click event runs and modifies the query definition of the "All Customers" QueryDef object.</li></ol>

Keywords: kbhowto KB255782

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.