Importing from Excel

[ttutela]

I have read previous threads, but am still having trouble importing from Excel. I can successfully import the spreadsheet, but then when I try to do something to that table, I get an error "Microsoft Jet Base Engine can not find input table or query 'tblOpisImport" when I try to Set rstimport = dbsSTG.OpenRecordset(strTableName) after the import. If I chose to debug it highlights the line Set rstimport = dbsSTG.OpenRecordset(strTableName)
but then if I hit F5 the processing continues.

This is my code:

Dim dbsSTG As Database
Dim rstimport As Recordset
Set dbsSTG = CurrentDb()
Dim strTableName As String

strTableName = "tblOpisImport"


DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
TableName:=strTableName, _
FileName:="c:\STG\monthly requests\carb.xls", _
HasFieldNames:=True

Set rstimport = dbsSTG.OpenRecordset(strTableName)

rstimport.MoveFirst
Do While Not rstimport.EOF
If IsNull(rstimport!DOW) Then
rstimport.Delete


End If

rstimport.MoveNext
Loop

DoCmd.OpenQuery ("qryAppendCarb")

rstimport.Close
dbsSTG.TableDefs.Delete strTableName


End Sub

 

[IgorB]

I've seen last line of code:
dbsSTG.TableDefs.Delete strTableName

Next time you execute you procedure database could not find table as Recordsource because you just simply deleted it(see above syntax)

Igor.

 

[ttutela]

I don't think that's the problem. I want to create a new table each time since each spreadsheet I'm importing has different fields.

 

[modest]

Everything looks fine, except declare your variable data types.

Instead of Database/Recordset do DAO.Database/DAO.Recordset

Post your database, I'd like to take a look. Also run the database "Compact and Repair" feature.