Passthrough query from access to oracle

[MJD1]

Hi, is it possible to query an oracle db via passthrough where the date field is between two fields that the sql code would fetch from a form?

for instance, I'm looking for all the fields where the tran_date is between [forms]![password]![text156] and [Forms]![password]![text158] and also where the totalcost is greater then 0.

I tried a few things and I always get an ODBC call fail. however, when I do a simple select query, it works fine so I can rule out an ODBC connection issue.

thanks for your help

 

[sxschech]

You may need two queries. The first would be the pass-thru

SELECT * FROM ODBCTable

and save as qryPassThru

The second one would be a standard access query that uses the pass-trhu as its source.

SELECT * FROM qryPassThru
WHERE tran_date between [forms]![password]![text156] and [Forms]![password]![text158]

Don't forget to change the name of your queries and fields to the actual names. So for example qryPassThru and ODBCTable would be changed to the names you need for your actual queries and ODBC Table/View. Also, if you only need certain field names, then you would change SELECT * to SELECT Field1, Field2, etc.

 

[MJD1]

Hi, thanks for the quick reponse. Do you think that the above would be faster then using a linked table with a query? Il test anyways, but would like your thoughts.

Thanks

 

[dhookom]

There are times when the easiest method of creating complex queries with multiple filters is to change the SQL property of a saved query. This works well when you need to send form/control parameters to a pass-through query.

Assuming you have a query "qselMyQuery" that needs a couple dates from controls on a form. Your code might look like:

Dim strSQL as String
Dim strOldSQL as String
strSQL = "SELECT field1, field2, field3 FROM tblMyTable " & _
    "WHERE OrderDate BETWEEN #" & Me.txtStart & "# AND #" & _
    Me.txtEnd & "# " & _
    "ORDER BY field2, field1 DESC"
strOldSQL = fChangeSQL("qselMyQuery",strSQL)

The function is:

Function fChangeSQL(pstrQueryName As String, strSQL As String) As String
[green]'=============================================================
' basQueryDefs.ChangeSQL
'-------------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String)        SQL Statement
'-------------------------------------------------------------
' Returns: the previous SQL statement
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block[/green]
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(pstrQueryName)
    fChangeSQL = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[MJD1]

Thanks for the help, very appreciated!

martin