Invalid Data Precision - SQL Pass Through Query in Access 5

[LevelThought]

Not able to use the following SQL pass-through query within 2003 Microsoft Access to extract data from an Oracle 8.1.7 database!

SELECT distinct A1.ACCOUNT_ID, A2.ACCOUNT_NO, trunc(A2.DATE_INSERTED)
FROM ACCOUNT_PAYOR A1, ACCOUNT_PAYMENT_DETAIL A2
WHERE A1.ACCOUNT_NO = A2.ACCOUNT_NO
AND A1.ACCOUNT_ID = 'C24'

The error that I receive is "Invalid precision for decimal data type." This appears to be related to the use of "trunc" on the "date inserted" field!

Any idea as to the solution to this problem?

Thanks in advance.

 

[LevelThought]

Upon creating the passthru query, I dragged it to a new Access and converted to a Append query.

However, the issue is - where do I setup the parameter?

Currently, I have to manually type in the date inserted value within the passthru query and then run the append query.

Not using ADO or stored procedures at the present time.

 

[jsteph]

What you need to do is create the sql dynamically. You're going to be running this from some Access Function. So it would go something like:

Public Function DoImp()
Dim db As DAO.Database, qdPT As DAO.QueryDef, qdPTBase As DAO.QueryDef
Dim qdApnd As DAO.QueryDef, sq As String, sqW As String
Dim qdApndSource As DAO.QueryDef
Dim strParm As String
Set db = CurrentDb
[COLOR=green]'this date could be passed as an arg, I'm not sure how you get this[/color]
strParm = Format(Date, "mm/dd/yyyy")

Set qdPTBase = db.QueryDefs("qryAppendPTBase")
[COLOR=green]'qdPTBase has sql somthing like:
'Select blah, blah, blah From SourceTable
'...qdBase exists ONLY to store text--you could hardcode it here if you like
'...however I like to have it in a saved query for easier testing.

'Extract the 'base' sql from the saved query[/color]
sq = qdPTBase.SQL
[COLOR=green]'Now the hardcoded where clause plus the dynamic date parm[/color]
sqW = "WHERE (AL14.TRANSACTION_CODE in ('5470','5471')))"
sqW = sqW & " AND trunc(AL7.DATE_INSERTED) = '" & strParm & "'"

[COLOR=green]'Now set the 'real' Passthru's sql[/color]
Set qdPT = db.QueryDefs("qryAppendPTSource")
qdPT.SQL = sq & " " & sqW

[COLOR=green]'now execute the JET query with qryApndSource as it's input.
'!! It's very important that the # of fields and their position...
'...stay put in order for this to work--a change in one requires a change in the other.[/color]
Set qdApnd = db.QueryDefs("qryAppendJetFinal")
qdApnd.Execute
Debug.Print qdApnd.RecordsAffected & " Inserted for " & strParm

End Function

 

[LevelThought]

jsteph,

Thanks for the info. I will test and post back the outcome.

I was able to setup the append query to run once per day using an AutoExec macro. Once I solve the "date inserted issue", everything should work seamlessly.

In essence, the data from Oracle should be imported into the local access table every day.