Cannot repeat import from Excel

[alecwood]

Hi All

I have an Access 2003 front end, SQL Server 2000 backend. I import data from
a spreadsheet to a table called Form_Import using TransferSpreadsheet, the
table doesn't exist at import so Access creates it, makes some mods to that
table and process the data in it using DoCmd.RunSQL and finally delete the
table.

This works one time, the next time I try I get a "Table 'Form_Import' cannot
be found......." error. If I close the database and repeat the process it
works again, but only once. It's as if TransferSpreadsheet has forgotten it
has to create the table, for some reason it expects it to still be there.

My code

Private Sub MyButton_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"Form_Import", tbFile, False

strSql = "DELETE FROM Form_Import WHERE (isnumeric(F1) = 0)"
DoCmd.RunSQL (strSql)
'
'
'More processing such as
strSql = "UPDATE Form_Import SET [F11]='" & strTempStr & "' WHERE
[F1]= '" & rsImport!F1 & "'"
DoCmd.RunSQL strSql
'
'
'
strSql = "SET dateformat dmy INSERT INTO tblPanels(logIsFOC, logIsRMA,
txtFOC_RMA, " & _
"txtB_In, txtSubName, txtMan, txtType, txtNo, txtWeek, " & _
"txtCtom, datRxDate, intUp,logWar) " & _
"SELECT F8, F7, F5, F4, F2, F13, F11, F12, F10, F9, F6, F3,War " & _
"FROM Form_Import WHERE (IsNumeric(F1) = 1) And (F14 Is Not Null)"
DoCmd.RunSQL (strSql)

DoCmd.RunSQL ("DROP TABLE Form_Import")

End Sub

 

[GolferGuy]

I don't know why your temporary table is thougth to still be there, but what I have always done is to actually build the table and leave it there. Then all I need to do is clean it out before I go to use it again. If you want to clean it out (DELETE * FROM MyTable at the end, that of course is fine too, I just believe in always doing it at the start which makes the loading of the table not dependent on another process having been run correctly.