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

[swoozie]

Good morning! Is there anyone familiar with this topic?

[Mark Gambo]

Good morning! Is there anyone familiar with this topic?

Found these during a quick Google Search:
Search 1
Search 2

[disruptivehair]

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

I recognize this...'Microsoft Access Developer's Guide to SQL Server' by Mary Chipman and Andy Baron.

I used the DAO version myself...easier to read.

[swoozie]

Yep, text book and well text book doesn't work if you (me) do not know how to maniputlate it correctly.

How do you use/or what do you use with DAO? Currently we are creating a querydef and then running it and it takes 5 minutes to return the dataset. That is way too long for a user to wait. There has to be something better.

This ADO stuff looked better but I do not know how to change it for my purposes.


P.S.
I know how to write a pass thru query it is how do I do it with ADO and have it work, is the question.

[disruptivehair]

Yep, text book and well text book doesn't work if you (me) do not know how to maniputlate it correctly.

How do you use/or what do you use with DAO? Currently we are creating a querydef and then running it and it takes 5 minutes to return the dataset. That is way too long for a user to wait. There has to be something better.

This ADO stuff looked better but I do not know how to change it for my purposes.


P.S.
I know how to write a pass thru query it is how do I do it with ADO and have it work, is the question.

The PassThroughDAO sub itself takes milliseconds to run. Access is slow with native data or data on an external server, though.

This is how we've implemented the DAO sub:

' Adapted from 'Microsoft Access Developer's Guide to SQL Server' by Chipman and Baron
Public Sub PassthroughDAO( _
    ByVal strQdfName As String, _
    ByVal strSQL As String, _
    Optional fRetRecords As Boolean = True)
    
    'Modifies pass-through query properties
    'inputs: strQdfName = name of the query
    'strSQL = new SQL string
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQdfName)
    
    qdf.ReturnsRecords = fRetRecords
    qdf.sql = strSQL
    db.QueryDefs.Refresh
 
End Sub

It's very easy to use. Of course, ADO is the preferred method in Access 2003; we were using Access 97 (we're no longer developing that app) so DAO was much easier to use in our case. I'm afraid I don't really understand that book's ADO method either, which is why I used the DAO method. Also, since pass-through queries are native Access objects DAO was the logical choice though I'm sure there are some here who would disagree with that. MS is trying to get people to switch to ADO so in terms of backward compatibility it would be a good idea to get the ADO version working but this will hold you over while you continue getting to grips with it. I wouldn't recommend it to be part of your permanent solution though. HTH!

[si_the_geek]

I don't know if it will help clarify the ADO code you have, but here is Microsofts example:
http://support.microsoft.com/kb/304323

[swoozie]

Okay, here is a really stupid question. We have been so hung up here on passthru queries, I didnt even think. Is there a reason we can not just go grab a recordset and return it to a temp table then have the report pull from the temp table? Or is it because we can not use the same query we would use in a passthru for obtaining a recordset?

What would you use if you had to pull specific data into access from Oracle to populate a report?

[disruptivehair]

Okay, here is a really stupid question. We have been so hung up here on passthru queries, I didnt even think. Is there a reason we can not just go grab a recordset and return it to a temp table then have the report pull from the temp table? Or is it because we can not use the same query we would use in a passthru for obtaining a recordset?

What would you use if you had to pull specific data into access from Oracle to populate a report?

We use SQL Server, not Oracle, so I can't really comment on Oracle's capabilities but the object library is the same no matter what DB you use (I think, the others will definitely jump in if I'm wrong). The VB version of our app obviously doesn't have pass-through queries and Access 2003 uses the same dll for its ADO reference. Why not try it out, see how it goes?

We used pass-through queries in the original Access app because I was still finding my way around VB and found it easier to do it that way, but you definitely don't have to do it that way if you don't want to and you can save yourself the overhead of having to modify a native Access object every time you want to retrieve data.

[swoozie]

i would love to try it out but every one is on vacation that "owns" the project. Can't wait until after the holidays.

[Pradeep1210]

As far as I know, the EXECUTE method of Connection object executes a Passthrough query.

Things like this work easily.

'' your query
sQuery = "Delete FROM Customer WHERE 0=1"     '' whatever your passthrough query
 
''Then execute the query
cn.Execute sQuery
 
 
''incase you expect something returning, get it in a recordset object
sQuery = "SELECT * FROM Customer"
Set rs = cn.Execute(sQuery)

Pradeep