Problem with transactions & using data

[Julia B]

Hi

I've got a module that is very complex. There's a sub (ProcessRecords)
that's called to validate and do all sorts of things to a group of records.
The sub then calls many other subs/functions to do the job.

The problem is that I'm using one transaction for the sub, so all the
processing/changes/queries that are used within the other called subs
functions are not committed until the end of the ProcessRecords. I'm doing
this because I don't want to save anything until I know it's all completed,
this is critical.

However I've got 1 of the called subs that creates a new lot of records,
then an action query that creates a temporary table from the new records.
Herein lies the problem! The action query is returning no rows, I'm assuming
because the newly created records have not been committed to the database.

How is it that I can check uncommitted records using ADO, but an action
query won't work? Has anyone any ideas?

The original sub is below:

Public Sub ProcessRecords()
'this sub is called for first and this calls on all the other subs/functions
within the module
Set db = CurrentProject.Connection
dbError = False
dbErrorPlace = ""
dbErrorString = ""

'on error go to
On Error GoTo errHandler

'turn on the hourglass
DoCmd.Hourglass True

'begin the transaction
db.BeginTrans

'the first thing to do is to create a new header level record for each
new record in the shipments
'entry table & mark the original record as processed
CreateHeaderLevelRecords (True)

'now check if any imported records don't have matching manual records
'then create a header level record for those
CreateHeaderLevelRecords (False)

'now, before creating detail level records to match, ensure that all pns
imported match those
'in the pn table otherwise create a new/amended pn record
CheckPNs

'then create new detail level records for each of the records in imported
'record and mark the original record as processed
CreateDetailLevelRecords

'now do the validation for each record in turn
'do it at detail level first, creating a validation error record if
necessary & updating the record
'then at header level (can't be done until all details are complete)
ValidateDetailRecords
ValidateHeaderRecords

'commit the records
db.CommitTrans

exitProc:
'close the db connection & turn the hourglass off
Set db = Nothing
DoCmd.Hourglass False
Exit Sub

errHandler:
'this error handler is only used if the data has not been updated for
some reason
Dim strError As String
strError = "{Err no}: " + CStr(Err.Number) + " {Desc}: " +
CStr(Err.Description)
dbError = True
dbErrorString = "There's been an unexpected error and the
validation/processing has failed." + vbCrLf + _
"The system error details are: " + strError + vbCrLf + _
"The error happened here in the process: '" + dbErrorPlace + "'"
+ vbCrLf + _
"Please try again. If the error occurs again, please raise a
ticket." + _
"Thank you and apologies for the inconvenience."
MsgBox dbErrorString, vbOKOnly + vbCritical, "Critical error"

'don't commit the records to the tables
db.RollbackTrans
Resume exitProc
End Sub

And then this is where I'm having the problem:

Private Sub ValidateDetailRecords()
Dim Valid As Boolean
Valid = True
dbErrorPlace = "ValidateDetailRecords"
'turn the default warnings off so the user doesn't get bothered by the
temp table
'creation and deletion
DoCmd.SetWarnings False

'create a temp table locally
DoCmd.RunSQL "SELECT ShipmentsHeaderLevel.GRef,
ShipmentsHeaderLevel.ShipType, " + _
"ShipmentsHeaderLevel.AWBill, ShipmentsHeaderLevel.CofSupply
, " + _
"ShipmentsHeaderLevel.TaxPoint,
ShipmentsHeaderLevel.ValueTotal, " + _
"ShipmentsHeaderLevel.TradeTerms,
ShipmentsHeaderLevel.Channel, " + _
"ShipmentsHeaderLevel.SPOFF, ShipmentsHeaderLevel.Invoices,
" + _
"ShipmentsHeaderLevel.Packages,
ShipmentsHeaderLevel.Comment, " + _
"ShipmentsHeaderLevel.SplitShipment,
ShipmentsHeaderLevel.Period, " + _
"ShipmentsHeaderLevel.Status,
ShipmentsDetailsLevel.RecordNo, " + _
"ShipmentsDetailsLevel.SDISplit, " + _
"ShipmentsDetailsLevel.FinalPN, ShipmentsDetailsLevel.Plant,
" + _
"ShipmentsDetailsLevel.FinalAWB,
ShipmentsDetailsLevel.FinalPurchDoc, " + _
"ShipmentsDetailsLevel.PurchPrefix,
ShipmentsDetailsLevel.FinalSDDoc, " + _
"ShipmentsDetailsLevel.SplitInvCode,
ShipmentsDetailsLevel.SplitInvInd, " + _
"ShipmentsDetailsLevel.FinalQty,
ShipmentsDetailsLevel.FinalUOM, " + _
"ShipmentsDetailsLevel.FinalValue,
ShipmentsDetailsLevel.FinalCurrency, " + _
"ShipmentsDetailsLevel.FinalCOO,
ShipmentsDetailsLevel.FinalDest, " + _
"ShipmentsDetailsLevel.CPC,
ShipmentsDetailsLevel.ItemComment, " + _
"ShipmentsDetailsLevel.Status INTO TempAllInvalidRecords " + _
"FROM ShipmentsHeaderLevel INNER JOIN ShipmentsDetailsLevel
ON " + _
"ShipmentsHeaderLevel.GRef = ShipmentsDetailsLevel.GRef " + _
"WHERE ShipmentsHeaderLevel.Status='Invalid' " + _
"OR ShipmentsHeaderLevel.Status='' OR
ShipmentsHeaderLevel.Status='Valid'"
'then open it and looping through it
Dim rsTemp As New ADODB.Recordset
Dim strTemp As String
strTemp = "SELECT * FROM TempAllInvalidRecords"

Thanks in advance.
Julia

 

[Julia B]

Thanks for this. I've done a bit of research and I think I need to use nested
transactions. I'm going to try that and see what happens.

Any comments or examples?

Julia