HandsOn Creating a Select Query with ActiveX Data Objects

1. Create a new Microsoft Office Access database or open the Acc2003_ Chap15.mdb file from the book's downloadable files.

2. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

3. In the Visual Basic Editor window, choose Insert | Module.

4. Choose Tools | References and select the following object libraries:

Microsoft ADO Ext. 2.7 for DDL and Security Object Library Microsoft ActiveX Data Objects 2.7 Object Library

5. In the module's Code window, type the Create_SelectQuery procedure shown below.

6. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Create_SelectQuery() Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim strPath As String Dim strSQL As String Dim strQryName As String

On Error GoTo ErrorHandler

' assign values to string variables strPath = CurrentProject.Path & "\Northwind.mdb" strSQL = "SELECT Employees.* FROM Employees WHERE " _ & "Employees.City='London';"

strQryName = "London Employees"

' open the Catalog

Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPath

' create a query based on the specified SELECT statement Set cmd = New ADODB.Command cmd.CommandText = strSQL

' add the new query to the database cat.Views.Append strQryName, cmd

ExitHere:

Set cmd = Nothing

Part II

Set cat = Nothing

MsgBox "The procedure completed successfully.", _

vbInformation, "Create Select Query" Exit Sub

ErrorHandler:

If InStr(Err.Description, "already exists") Then cat.Views.Delete strQryName Resume Else

MsgBox Err.Number & ": " & Err.Description Resume ExitHere End If End Sub

The Create_SelectQuery procedure opens the Catalog object and sets its ActiveConnection property to the Northwind database:

Set cat = New ADOX.Catalog cat.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPath

As you may recall from Chapter 11, the Catalog object represents an entire database. It contains objects that represent all the elements of the database: tables, stored procedures, views, columns of tables, and indexes (see the ADOX Object Model in Chapter 11). The ActiveConnection property of the Catalog object indicates the ADO Connection object the Catalog belongs to. The value of this property can be a reference to the Connection object or a connection string containing the definition for a connection.

Next, the procedure defines a Command object and uses its CommandText property to set the SQL statement for the query:

Set cmd = New ADODB.Command cmd.CommandText = strSQL

The CommandText property contains the text of a command you want to issue against a provider. In this procedure, we assigned the string variable's value (strSQL) to the CommandText property.

The ADO Command object always creates a temporary query. So, to create a stored (saved) query in a database, the procedure must append the Command object to the ADOX Views collection, like this:

cat.Views.Append strQryName, cmd

If you open the sample Northwind database after running the above procedure, you will find the London Employees query in the Queries section of the Database window.

Creating and Manipulating Databases with ADO

0 0

Post a comment