TransferSpreedsheet, importing to temporary table

C

Chris

My problem is that I'm not sure what connection transfer spreadsheet
uses.

Currently I'm using
--------------------------------------------------------
Dim conn As Connection
Dim rs As New RecordSet

Set conn = CurrentProject.Connection
conn.BeginTransaction

DoCmd.TransferSpreadsheet TransferType:=acImport _
, SpreadsheetType:=acSpreadsheetTypeExcel9 _
, tableName:="#data" _
, FileName:=strFile _
, HasFieldNames:=True _
, Range:="A:U"

rs.Open "SELECT count(distinct name) FROM #data", conn

'More processing below
--------------------------------------------------------

This gives an error at the statment rs.Open, "Invalid object name
#data". This goes away if I use ##data, but that raises the problem
that if an error occurs, when I call conn.RollbackTrans the table
##data is still left behind. The next time the code is called
TransferSpreadsheet will fail to work whilst ##callfile exists.

I have also tried creating #data before calling TransferSpreadsheet but
the count return that there are no rows in #data. Thus I have currently
concluded that TransferSpread sheet does not correctly work with
temporary tables? and that it runs on a different connection?

Any comments or solutions would be welcome, even if all they do is
confirm that this won't work and I have to import using something like
and ADO connection to the excel file.

Thanks, Chris.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top