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.
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.