OK, I understand that I can't use BeginTrans/CommitTrans with a disconnected recordset (ARRRGH!); so just HOW do I trap for errors when I'm updating/adding to multiple tables?
I have a sneaky workaround which works(and makes me even MORE nervous that it does!) using BeginTrans and updating a disconnected recordset:
Dim cnn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Set cnn1 = New Connection
cnn1.Open g_strConnection
'BEGIN TRANSACTION!!
cnn1.BeginTrans
If cnn1.State = adStateOpen Then
Set rs1 = cnn1.Execute(SQL),,adCmdText
Else
MsgBox "NO CONNECTION!"
Exit Sub
rs1.Close
End If
' SECOND TRANSACTION on the 2nd table involved!
cnn1.Execute "UPDATE blah, blah"
' THIRD transaction on the 3rd table involved!
cnn1.Execute SQL, , adCmdText
' NOW we ADD records from a DISCONNECTED RECORDSET to a TABLE in the DB!
Set rs1 = New ADODB.Recordset
rs1.CursorLocation = adUseClient
rs1.Open "TestData", g_strConnection, adOpenStatic, adLockOptimistic, adCmdTable
While not rsDisconnected.EOF
rs1.Addnew
rs1!TestNumber = TestNum
rs1!QuestionNumber = Counter
rs1!Question = rsDisconnected!Question
rs1.Update
rsDisconnected.MoveNext
cnn1.CommitTrans
ErrHandler:
cnn1.RollbackTrans
dim cnnRevise as ADODB.Connection
Set cnnRevise = New Connection
cnnRevise.Open g_strConnection
cnnRevise.Execute "DELETE FROM TestData WHERE TestNumber = " & TestNum
cnnRevise.Close
Now BEFORE you freak - THIS ACTUALLY WORKS!! When I force an error the rollbacktrans stops the cnn1.Execute statements, AND the cnnRevise DELETES any fields entered into the Test table! BUT, is there a better, more robust way to do this?? I'm afraid there's a potential HUGE database destroying error that could occur here.
If anybody knows a better way to error trap for the disconnected recordsets, I'd LOVE to hear it! Or if the above is an acceptable way to handle it, I'd appreciate hearing that as well. TIA!!
