The Microsoft Jet database engine cannot find the input table

[Gerry G.]

I receive the following error message when my code attempts to import a
comma-quote delimited file:

"The Microsoft Jet database engine cannot find the input table or query
'Daily_Price_Pre_Import'. Make sure that it exists and that its name is
spelled correctly."

Also, the file is not imported nor is the table created.

Approximately 1 out of 10 times I do NOT get the error message and the code
runs just fine.


My code is as follows:


Sub Import_Daily_Prices(cnxn As ADODB.Connection)

Dim TableStr As String
Dim zMessage As String
Dim zTitle As String
Dim zDefault As String
Dim zFileAndPath As String

On Error GoTo Import_Daily_Prices_Err

'Delete Pre_Import table
TableStr = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
& _
"WHERE TABLE_NAME = 'Daily_Price_Pre_Import') " & _
"DROP TABLE Daily_Price_Pre_Import"

cnxn.Execute TableStr

zMessage = "Enter the file name and path of the DTN Export file" ' Set
prompt.
zTitle = "DTN EXPORT FILE" ' Set title.
zDefault = "C:\DTN\Export DM2.csv" ' Set default.

' Display message, title, and default value.
zFileAndPath = InputBox(zMessage, zTitle, zDefault)

'Import Pre_Import table
DoCmd.TransferText acImportDelim, , "Daily_Price_Pre_Import", zFileAndPath

'Clean up before exiting
Import_Daily_Prices_Exit:

Import_Daily_Prices_Err:
If Err.Number <> 0 Then
MsgBox Error$
cnxn.Close
Set cnxn = Nothing
End
End If
DoCmd.SetWarnings True
Exit Sub

End Sub


any ideas on wht is causing the problem.

The import should create the table therefore the Jet database engine should
not have to find the table, and not generate this error message.

The code is in an Access ADP project using Access 2003.

Thanks for your help,

Gerry

 

[Robert Morley]

When importing text files, I believe Jet gives this error when it can't find
the file (which it perceives as a table in this context). Are you sure
you're just not making a common typo in the name somewhere? Try it with the
name being a constant for the sake of testing and see if it becomes
reliable.



Rob

 

[Gerry G.]

It does correctly import the file and create the table about once every 10
times I run the code, without changing anything.

 

[Guest]

Are you running the same code over and over again to get
"1 in 10 times", or do you mean that 1 day in 10 in works?

Are you using the design wizards to design forms and reports?

(david)

 

[Robert Morley]

While it may be the same code every time, it's still *prompting* you for the
name of the file...are you sure you aren't making typos when entering the
name?



Rob