HandsOn Creating a Pass Through Query with ADOX

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

2. In the module's Code window, type the Create_PassThroughQuery procedure shown below.

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

Sub Create_PassThroughQuery() Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Dim strPath As String Dim strSQL As String Dim strQryName As String Dim strODBCConnect As String

On Error GoTo ErrorHandle strSQL = "SELECT Customers.* FROM Customers WHERE " _ & "Customers.Country='France';"

strQryName = "French Customers"

' modify the following string to connect ' to your SQL Server strODBCConnect = "ODBC;Driver=SQL Server;" & _ "Server=JULITTA733\JKDESKTOP1;" & _ "Database=Northwind;" & _

Creating and Manipulating Databases with ADO

' strODBCConnect = "ODBC;DSN=ODBCNorth;UID=sa;PWD=;" Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection

Set cmd = New ADODB.Command With cmd

.ActiveConnection = cat.ActiveConnection .CommandText = strSQL

.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True .Properties("Jet OLEDB:Pass-Through Query Connect String") = _ strODBCConnect End With cat.Procedures.Append strQryName, cmd

Set cmd = Nothing Set cat = Nothing

MsgBox "The procedure completed successfully.", _

vblnformation, "Create Pass-Through Query" Exit Sub

ErrorHandler:

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

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

The above procedure creates a pass-through query named French Customers in the current database. The SQL statement used to generate this query and the output of this statement were illustrated earlier in Figures 15-4 and 15-5. Notice that to connect to the SQL Server database, the following string is built and later assigned to the CommandText property of the Command object:

strODBCConnect = "ODBC;Driver=SQL Server;" & _ "Server=JULITTA733\JKDESKTOP1;" & _ "Database=Northwind;" & _ "UID=;" & _ "PWD="

Needless to say, if you want to try this procedure, you must have access to a remote data source (such as an SQL Server database) and you'll need to modify the above string to point to your server. The above string allows you to connect via the DSN-less connection. You may build your connection string to the remote data source using the Data Source Name (DSN) that you define in the Control Panel via Administrative Tools (ODBC). Your connection string could then look like this:

strODBCConnect = "ODBC;DSN=myDSN;UID=sa;PWD=;"

Part II

To create a pass-through query you must also set two provider-specific properties of the Command object: Jet OLEDB:ODBC Pass-Through Statement and Jet OLEDB:Pass-Through Query Connect String.

To permanently store the pass-through query in your database, you need to append it to the Catalog's Procedures collection, like this:

cat.Procedures.Append strQryName, cmd

After you run the Create_PassThroughQuery procedure, the query can be viewed and accessed from the Queries screen in the Microsoft Access database window.

0 0

Post a comment