RollbackTrans Issue

[cefrancke]

I'm trying to use a BeginTrans block, where I'm importing data outside
of Access (an Excel spread sheet) and if I have an empty data cell or a
cell with bad data, I would like to call a routine to update a table
with the file info, date, record number, column name, etc. and then
Rollback any records already imported during the transaction.
The rollback works on the Import Table but it also seems to rollback my
Error Log table. I'm using two different connection objects, just on
the same database.

'After creating rs and cn objects...
'All connections are to the CurrentProject.Connection
'except the spread sheet connection

cnAccess.BeginTrans 'Start the transaction on this database

Do Until rsSpreadSheet.EOF

rsImportTable.AddNew
For i = 0 to rsSpreadSheet.Fields.Count - 1 'Go thru each field
if IsNull(rsSpreadSheet.Field(i).Value) then 'or empty string
'This call a subroutine that opens another connection
'to this database and stores the error info
LogError "ImportErrorTable", Filespec ' and other data
Goto Import_Error
End If

'Rest of code to insert the data

next i

'Rest of code to update the recordset

Loop
cnAccess.CommitTrans ' Commit all the updates
Goto CleanUp_Import
Import_Error:
rsSpreadSheet.CancelUpdate 'Cancel the current record
cnAccess.RollbackTrans
CleanUp_Import
'Close all recordsets and connections

The LogError procedure creates a separate connection and recordset
object, to the current database, and updates the ImportErrorTable table
with the necessary data.

However, when I have an error(I can cause one if I want) the
ImportErrorTable is empty.

I suspect that the Rollback on the cnAccess cancels all updates on the
database in this block of code.

Any ideas?

TIA

 

[Graham R Seach]

Transactions are at connection-level, so any transaction methods will apply
to everything that occurs on that connection. If you want actions to be
isolated from the transaction, they must be on a different connection.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html