PassThrough Query

[swoozie]

I am using Access 2003 for a user gui and the original data is pulled from Oracle.

I need to lower the processing time and would like to write a pass through query to retreive the information via VBA/VB code behind an access form

I have queried this forum an I have only located a version using ADO but it really didn't focus on the query itself or maybe it does and I just do not undersstand all it says.

does anyone have any suggestions or references for me to look at?

This is what I have located currently:

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