Cannot repeat import table from Excel

[Alec M1BNK]

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

 

[Dale Fye]

Why are you recreating the table each time. Rather than importing the data
into a new table, import it into an existing table. Then, rather then
dropping the table, just delete all the records from it.

Usually, when I'm going to do this, I create the table in an mdb file that
is external to my application. Then I link it to my app, so that when that
table grows and shrinks, it doesn't actually affect the size of my
application.

HTH
Dale

 

[alecwood via AccessMonster.com]

Unfortunately that's not possible. The Excel sheets come from many different
outside sources, and I get lots of data type mismatch errors when I try to
import them into a pre-existing table.

In the end I managed to fix it.

I forgot to release the recordset before deleting the table, added the SET
rsRecordSet=Nothing and problem solved.

Why are you recreating the table each time. Rather than importing the data
into a new table, import it into an existing table. Then, rather then
dropping the table, just delete all the records from it.

Usually, when I'm going to do this, I create the table in an mdb file that
is external to my application. Then I link it to my app, so that when that
table grows and shrinks, it doesn't actually affect the size of my
application.

HTH
Dale

Hi All

[quoted text clipped - 38 lines]

End Sub

 

[Dale Fye]

I would argue that if you are getting data from multiple sources, in multiple
formats, that makes it even more important that you create temporary tables,
import specifications, and transformation queries for each of these data
formats.

Then, when you import the data, all you would have to do is identify what
the file name is, and what the source is, then your code could append the
data to the appropriate temporary table, and then run a transformation query
that takes that data and massages it into the right format before inserting
into some other (permanent) table.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Unfortunately that's not possible. The Excel sheets come from many different
outside sources, and I get lots of data type mismatch errors when I try to
import them into a pre-existing table.

In the end I managed to fix it.

I forgot to release the recordset before deleting the table, added the SET
rsRecordSet=Nothing and problem solved.

Why are you recreating the table each time. Rather than importing the data
into a new table, import it into an existing table. Then, rather then
dropping the table, just delete all the records from it.

Usually, when I'm going to do this, I create the table in an mdb file that
is external to my application. Then I link it to my app, so that when that
table grows and shrinks, it doesn't actually affect the size of my
application.

HTH
Dale

Hi All

[quoted text clipped - 38 lines]

End Sub