mistux Site Admin
Joined: 25 Jun 2004 Posts: 1042 Location: South Bend, Indiana USA
|
Posted: Thu Jan 15, 2009 6:06 pm Post subject: Create an SQL Pass-Through Query Using Code |
|
|
Here is how you can create Query using code. It will show up in your database window after you click on an entry in it then hit F5 to refresh the display.
Code: |
DoCmd.Hourglass True
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim SPTQueryName As String 'Query name
Dim strSQL As String 'Your query
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
SPTQueryName = "MyTestSP"
strSingleQ = Chr(39) 'Single quote mark
strSQL = "sp_report DepositDetail show TxnType, Date, Account, Amount Parameters DateFrom= {d" & strSingleQ & "2009-01-01" & strSingleQ & "}, DateTo= {d" & strSingleQ & "2009-08-23" & strSingleQ & "}"
cat.ActiveConnection = CurrentProject.Connection
Set cmd.ActiveConnection = cat.ActiveConnection
'Delete query if exists, then recreate it using current strSQL criteria
cat.Procedures.Delete SPTQueryName
cmd.CommandText = strSQL
cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
'Modify the following connection string to reference an existing DSN for your system.
cmd.Properties _
("Jet OLEDB:Pass Through Query Connect String") = _
"ODBC;DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerCurrency=O;OptimizerAllowDirtyReads=N;SyncFromOtherTables=Y;IAppReadOnly=Y"
cat.Procedures.Append SPTQueryName, cmd
Set cat = Nothing
Set cmd = Nothing
DoCmd.Hourglass False
MsgBox "done"
|
NOTE: The sample code in this article uses both ADO and ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX). For this code to run properly, you must click References on the Tools menu in the Visual Basic Editor and make sure that the following two references are selected:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.6 for DDL and Security |
|