Public Sub PassThroughADO(ByVal strQdfName As String, _
ByVal strSQL As String, _
Optional varConnect As Variant, _
Optional fRetRecords As Boolean = True)
' strQdfName is the name of the query
' strSQL is the new SQL string, if you need to update the original query
' varConnect is the optional connection string, probably need this
' fRetRecords Optional returns records defaults to True(or yes, what you want)
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
' Open the catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
' Get the command to fire
'here the string parameter of the query name is passed
Set cmd = cat.Procedures(strQdfName).Command
' set pass-through properties
cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
' Update the SQL, or what to select from the query
cmd.CommandText = strSQL
' Update the Connection string, make sure we successfully get through
'your SQL Server
If Not IsMissing(varConnect) Then
cmd.Properties _
( "Jet OLEDB:Pass Through Query Connect String") = CStr(varConnect)
End If
' Update the ReturnsRecords property
' to return or NOT return records
cmd.Properties( _
"Jet OLEDB:Pass Through Query Bulk-Op") = Not fRetRecords 'or fRetRecords
' Save the changes you have fired
Set cat.Procedures(strQdfName).Command = cmd
'Clean up variables
Set cmd = Nothing
Set cat = Nothing