Newly Created Querydef (Pass Through) not appended fast enough

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I try to append the records from a newly created Pass Through Query
(Oracle) I receive "Run-time error '3078' - The Microsoft Jet database engine
cannot find the input table or query 'Suspense'. Make sure it exists and
that its name is spelled correctly". I click debug and then F5 to continue
and everything runs fine.

It initially fails when it reaches the docmd.runsql line below. The code is
as follows:

Dim ws As Workspace
Dim mydatabase As Database
Dim myquerydef As QueryDef
Dim strSsys As String
Dim SQLString As String
Dim SPTQueryName As String

If Me!SSYS_ID1 <> "" Then
strSsys = Me!SSYS_ID1
Else: MsgBox "Please Enter a Ssys Id.", vbCritical, "Site System Id
Required"
Exit Sub
End If

SPTQueryName = "Suspense"

Set ws = DBEngine.Workspaces(0)
Set mydatabase = ws.Databases(0)

Set myquerydef = mydatabase.CreateQueryDef()
With myquerydef
.Name = SPTQueryName
.Connect = "ODBC;DRIVER={Microsoft ODBC for
Oracle};SERVER=prod;"

' Set the SQL property and concatenate the variables.
SQLString = "SELECT SSYS_ID, SOURCE_SYSTEM_NAME as ""SYSTEM"",
DATABASE_ID as ""DPI"", FILE_NAME, BATCH_FILE_ID, BATCH_STATUS_CODE,
NUMBER_OF_RECORDS, to_char(TOTAL_VALUE_OF_RECORDS) as ""Total Amt"",
DATE_TIME_CREATED, LAST_SUBMIT_DATE_TIME" & _
" from nsa_header" & _
" where batch_file_id > 120000" & _
" and DATE_TIME_CREATED BETWEEN '" & Format(Me!Date1,
("dd-MMM-yyyy")) & "' AND '" & Format(Me!Date2, ("dd-MMM-yyyy")) & "'" & _
" and ssys_id = '" & strSsys & "'"

.SQL = SQLString
.ReturnsRecords = True
.ODBCTimeout = 0

End With

mydatabase.QueryDefs.Append myquerydef
mydatabase.QueryDefs.Refresh

Application.RefreshDatabaseWindow
Set mydatabase = Nothing

DoCmd.RunSQL ("INSERT INTO SubHist ( SSYS_ID, SYSTEM, DPI, FILE_NAME,
BATCH_FILE_ID, BATCH_STATUS_CODE, NUMBER_OF_RECORDS, [Total Amt],
DATE_TIME_CREATED, LAST_SUBMIT_DATE_TIME )" & _
" SELECT [Suspense].SSYS_ID, [Suspense].SYSTEM,
[Suspense].DPI, [Suspense].FILE_NAME, [Suspense].BATCH_FILE_ID,
[Suspense].BATCH_STATUS_CODE, [Suspense].NUMBER_OF_RECORDS, [Suspense].[Total
Amt], [Suspense].DATE_TIME_CREATED, [Suspense].LAST_SUBMIT_DATE_TIME" & _
" FROM [Suspense];")
 
Indy said:
When I try to append the records from a newly created Pass Through Query
(Oracle) I receive "Run-time error '3078' - The Microsoft Jet database engine
cannot find the input table or query 'Suspense'. Make sure it exists and
that its name is spelled correctly". I click debug and then F5 to continue
and everything runs fine.

It initially fails when it reaches the docmd.runsql line below. The code is
as follows:
Click to expand...

Maybe the application needs a little time? DoEvents might help

I don't know if it makes a difference, but I've stopped using
docmd.runsql, the alternative since A97 is currentdb.execute
 
Thanks for your response. I tried both of your suggestions and unfortunately
the problem remains. The query is still not present when it is called by the
append query. Any additional suggestions would be greatly appreciated.
 
Back
Top