The Microsoft Jet database engine cannot find the input table

[MaltaC]

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.



Thanks for your help,

Gerry