Create passthrough query using ADO

  • Thread starter Thread starter Steven K
  • Start date Start date
S

Steven K

Hello, I am trying to use the following code to create a passthrough query.
The code runs, but when I click on the query tab, the query is not there.
Based on the following code, I must be appending it to the procedures
object, but don't know what or where that is:
catPrice.Procedures.Append strQryName, cmdPrice

Any help with this would be appreciated.

--
Thanks in advance,

Steven



Sub CreatePassThroughQry()
Dim strDBPath As String, strSQL As String, strODBCConnect As String,
strQryName As String
Dim catPrice As New ADOX.Catalog
Dim cmdPrice As New ADODB.Command
Dim varProp As Variant

Dim strParm00 As String, strParm01 As String
Dim strParm02 As String, strParm03 As String, strParm04 As String

'Define parameters
strParm01 = "MyData"
strParm02 = Me.StartDate.Value
strParm03 = Me.EndDate.Value
strParm04 = ""
strParm00 = "'" & strParm01 & "', " & "'" & strParm02 & "'," & "'" &
strParm03 & "', " & "'" & strParm04 & "'"

' SQL statement.
strSQL = "exec sp_web_WinLoss " & strParm00

' Connection String
strODBCConnect =
"ODBC;DSN=MyDSN;DATABASE=MyDatabase;Trusted_Connection=Yes"

' Query Name
strQryName = "a_qsp_Test"

' Open the Catalog object.
catPrice.ActiveConnection = CurrentProject.Connection

' Set cmdPrice = New ADODB.Command
' Define SQL statement for query and set provider-specific properties for
query type and ODBC connection string.
With cmdPrice
.ActiveConnection = catPrice.ActiveConnection
.CommandText = strSQL
.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
.Properties("Jet OLEDB:Pass Through Query Connect String") =
strODBCConnect
End With

' Name and save query to Procedures collection.
catPrice.Procedures.Append strQryName, cmdPrice

Set catPrice = Nothing
End Sub
 
Back
Top