ERROR 3078: Jet Engine can't find input table or query 1

[jadams0173]

I'm coping the table in question from another DB to my DB using VBA code. It is one of 4 tables that gets copied and is the last one in the sequence.

The next step is to run a DELETE query on the newly copied table. This is where I sometimes get the error: "The MS jet engine can't find the input table or query ------ ...."

The table existis because I just copied it. Is this a timing issue or something? The error does not occur every time. I will post the code if needed.

With cnn
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & Path1 & db4 & ";" & _
                      "Jet OLEDB:Database Password="

  .Open
End With

cnn.Execute "SELECT PRIMARY_LINE.* into PRIMARY_LINE_C IN '" & MyDB & "' FROM PRIMARY_LINE"

cnn.Close
Set cnn = Nothing

    ' DELETES ALL NONE 27053 ITEMS FROM PRIMARY_LINE_C
 [red]DoCmd.OpenQuery "QRY_27053_ONLY", acNormal, acEdit[/red]

 

[Sumitdev]

You may need to refresh the JET cache using the JRO.JetEngine Object (Microsoft Jet and Replication Objects 2.6 Library)

   Dim pJE As New JRO.JetEngine
   pJE.RefreshCache cnn
   Set pJE = Nothing

 

[JerryKlmns]

PRIMARY_LINE_C resides in a different database where the DoCmd.OpenQuery reffers to ....
You'll have to either open a connection opbject to MyDB and run the delete sql using the Execute method of the connection object or use a different SQL statement

cnn.Execute "SELECT PRIMARY_LINE.* into PRIMARY_LINE_C IN '" & MyDB & "' FROM PRIMARY_LINE WHERE Item<>27053",,129

Option 129 = adCmdText+ adExecuteNoRecords = evaluates the execution of a textual SQL statement and does not return any records resulting in faster completetion of the instruction from the provider. Just refine as needed the WHERE clause

 

[jadams0173]

JerryKlmns,
Upon completion of the copy, PRIMARY_LINE_C and the delete query live in the same DB. I will give your suggestion a go.

Sumitdev,
Thanks for the reply. I will also see if that will solve my problem.

Thanks to the both of you for repling.

 

[JerryKlmns]

Just to clear something:

DoCmd.OpenQuery is supposed to find the query in the database that runs that code and not in an external database!

 

[jadams0173]

Jerry,

Just to make sure I understand what you are saying and that we are on the same page becauase I'm a little confused at this moment.

Primary_Line lives in a different database. I am copying the complete table (Primary_Line) into my DB where all of my queries and Primary_line_C (the copy) live. I'm not, at least I don't think, telling access to run my delete query on Primary_LINE_C in the OTHER DB becuase that table does not exist.

In summary I guess what I'm trying to say is the DoCMD.OPEN query and the PRIMARY_LINE_C table are in the same DB.

Geeze...I think I've confused myself more... %-)

 

[JerryKlmns]

jadams0173

Sounds like Sumitdev was correct at the first time! Sorry for the mess I made

 

[jadams0173]

No problem Jerry. Thanks for taking the time to reply. You've helped me a lot more times than you'll ever know through archived posts!!!

 

[jadams0173]

All,

I've tried the JRO solution but still have the same error. This is driving me nuts. Any more ideas?

Jerry,
I'm going to try your suggestion now.

It's more of a challenge now to find out why the delete query says it can't find the table.

I've take then query to a sql string and executed it with DoCmd.RunSQL and I've done currentproject.connection.execute sql and all have produce the error.

 

[jadams0173]

One thing I fogort to add. If I let the debugger set on the line for 10 or 15 seconds (that's a guess) and press the RUN button at the top of the debugger the query will run!

 

[jadams0173]

I got this fixed. I was my query for this one table that was causing the problems.