Workspace.CommitTrans not saving records into table

[kc2npu]

I am a little confused. I have a set of processes that I am running. i have encapsulated them all in a transaction

Dim myWorkSpace As DAO.WorkSpace
   Set myWorkSpace = DBEngine.Workspaces(0)
   myWorkSpace.BeginTrans

{.... my code and work ] (Each function/sub that runs if it encounters an error it changes the Module level variable sys_STN_Continue from TRUE to FALSE and the subsequent steps do not run.

After each of the steps run i check if we should commit and store the records into the table.

If sys_STN_Continue Then
   myWorkSpace.CommitTrans
   Set Meter = Nothing
   Else
   myWorkSpace.Rollback
   MsgBox "Error Occured. Process: StudentTestNeeds_Controller. Unable to Populate Staging Table. Cannot Continue", vbCritical Or vbOKOnly, SYS_APPNAME
   Set Meter = Nothing
   GoTo Error_Handler_Exit
   End If

While stepping through the code, sys_STN_Continue remains TRUE and it enters the final if/end if section. It appears to run the myWorkspace.Committrans statement. However, I do not see any of the data in the table at the end of the process.

What am I doing wrong?

 

[jdraw]

Here is an article by Philipp Stiefel on Using Transactions in MS Access. See if it sheds any light on your issue.

 

[Gasman]

I would have thought you would need to commit after each sub?
If the last one fails and they are all within one transaction, they will all rollback?

That is what transactions do?

Are transactions carried over in subs/functions? Never used them in Access.

 

[ebs17]

Each function/sub that runs ...

I am critical of the integration of a series of VBA procedures into a transaction.
I think a transaction should be limited to direct actions on the database engine, i.e. append queries and update queries, and should be defined as narrowly as possible anyway.

 

[kc2npu]

@ebs17 - what I did was break up each step of the process.
The VBA procedures serve three purposes
A) building long SQL strings and passing them to the Execute statement
B) Allowing for iteration over records
C) Encapsulating into sub steps and allowing for me to easily catch fails.

 

[cheekybuddha]

The VBA procedures serve three purposes
A) building long SQL strings and passing them to the Execute statement

Please show the code how you do this.

 

[kc2npu]

@cheekybuddha
for example

Public Sub StudentTestNeeds_Controller()
   If blnUseError Then On Error GoTo Error_Handler
   Dim SYS_MY_PROC As String
   SYS_MY_PROC = "StudentTestNeeds_Controller()"
   Debug.Print SYS_MY_PROC
  
   sys_STN_Continue = True
  
   Dim myWorkSpace As DAO.WorkSpace
   Set myWorkSpace = DBEngine.Workspaces(0)
  
   Dim dtStart As Date
      dtStart = Now
  
  
   Dim Meter As clsDualMeter
   Dim MeterSteps_Populate As Long
   Dim MeterStepCount As Long
  
      MeterSteps_Populate = 35
  
   Set Meter = New clsDualMeter
  
   Meter.InitializeProject MeterSteps_Populate, "Student Test Need Process: Build Profiles", True, 0.1, True, 2, 1489151, 3937808
  
   MeterStepCount = 0
  
   myWorkSpace.BeginTrans
  
   MeterStepCount = MeterStepCount + 1
  
   If sys_STN_Continue Then
      StudentTestNeeds_Prep_S_00_Reset_Staging_Table Meter, Format(MeterStepCount, "000") & vbTab & "Prepare Staging Table: Reset Staging Table"
      DoEvents
   End If
  
   If Not sys_STN_Continue Then
      'An Error Occured - End Processes. Rollback Actions
      myWorkSpace.Rollback
      
      MsgBox "Error Occured. Process: StudentTestNeeds_Controller. Unable to Prepare Staging Table. Cannot Continue", vbCritical Or vbOKOnly, SYS_APPNAME
  
      Set Meter = Nothing
  
      GoTo Error_Handler_Exit
  
   End If
  
   MeterStepCount = MeterStepCount + 1
   If sys_STN_Continue Then StudentTestNeeds_Populate_S_001_StudentInfo Meter, Format(MeterStepCount, "000") & vbTab & "Staging Table: Populate Student Demographic"
   DoEvents
  
   MeterStepCount = MeterStepCount + 1
   If sys_STN_Continue Then StudentTestNeeds_Populate_S_002_TRIN Meter, Format(MeterStepCount, "000") & vbTab & "Staging Table: Populate Student Group TRIN"
   DoEvents
  
...

Private Function StudentTestNeeds_Populate_S_001_StudentInfo(Meter As clsDualMeter, sTaskName As String) As STN_SuccessStatus
      If blnUseError Then On Error GoTo Error_Handler
      Dim SYS_MY_PROC As String
      SYS_MY_PROC = "StudentTestNeeds_Populate_S_001_StudentInfo()  "
      Debug.Print SYS_MY_PROC

      Dim myDB As DAO.Database
      Set myDB = CurrentDb
      
      Const StepSQL As String = "INSERT INTO sys_Stage_Determine_Student_Test_Needs (EMPLID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, CUNYLogin, Admit_Term, Admit_Type, UAPC1_ESL, IS_TRN, PREV_CUNY) SELECT Distinct A.EMPLID, A.Last_Name, A.First_Name, A.Middle_Name, A.CUNYLogin, A.Admit_Term, A.Admit_Type, IIF(A.UAPC1_ESL='ESL','Y','N') AS UAPC1_ESL, IIF(A.Admit_Type='TRN' OR A.Admit_Type='TRD', 'Y','N') AS IS_TRN, A.PREV_CUNY FROM AT_tblStudent A  "

      Meter.InitializeTask 2, sTaskName
      Meter.UpdateTask 1
      
      myDB.Execute StepSQL, dbFailOnError
      Debug.Print myDB.RecordsAffected
      
      Meter.UpdateTask 2

      GoTo Reg_Exit
      
Error_Handler:
      MsgBox "The following Error has occured  " & vbCrLf & vbCrLf & _
                   "Error Number:   " & Err.Number & vbCrLf & vbCrLf & _
                   "Error Source:   " & SYS_MY_PROC & vbCrLf & vbCrLf & _
                   "Error Description:   " & Err.Description & vbCrLf & _
                  Switch(Erl = 0, "  ", Erl <> 0, vbCrLf & "Line No:   " & Erl) _
                  , vbOKOnly + vbCritical, "An Error has Occured!  "
      Resume Error_Handler_Exit
      
Error_Handler_Exit:
      On Error Resume Next
      sys_STN_Continue = False
      StudentTestNeeds_Populate_S_001_StudentInfo = SS_FAIL
      Exit Function
      
Reg_Exit:
   StudentTestNeeds_Populate_S_001_StudentInfo = SS_SUCCESS
      
      Exit Function
End Function

 

[arnelgp]

try Creating a Global variable for the workspace and it's Database:

Global myWorkSpace As DAO.WorkSpace
Global myDB as DAO.Database

'on Load event of your form
Set myWorkSpace = DBEngine.Workspaces(0)
Set myDB = myWorkSpace.Databases(0)

replace all your Currentdb with myDB, since myDB is the database for your Transaction.

 

[ebs17]

What I see:

building long SQL strings

This is a simple and not very long table to table append query, nothing exciting or complicated. When using a constant, nothing is build, it is simply used. A saved query that you call with its name would be more practical for me than a whole VBA procedure that seems to be more concerned with displaying progress and other things than with the query process itself. It is estimated that the extra things involved increase the time required for the actual appending many times over.
Personally, I would always strive to use queries that are fast enough to render a progress bar ineffective and thus question its very existence.

Given my current level of knowledge, I would have a list of the queries to be executed (array, table). This is done in a simple loop. The dbFailOnError shows the error and immediately triggers the rollback in the error handling. Together with the query name, I then have all the information I need about the query.

By the way, I wouldn't use a new instance of the database every time via CurrentDb, but rather the same instance, created once within the workspace (which is being monitored).

You clearly put a lot of love and detail into your VBA project. For me, a transaction is an SQL project (direct backup with the database engine). VBA is only there for calling and some convenience, but it shouldn't serve any purpose in itself.
I can't determine where the error lies in your presentation. But I think that with a different and shorter course of action, the probability of not making this error in the first place increases.

 

[ebs17]

However, I do not see any of the data in the table at the end of the process.

Shyly asked back: Didn't you happen to have already opened the table and then didn't execute a requery (F5) in the table view after the query execution?

CommitTrans not saving records into table

In my understanding, CommitTrans does not carry out any explicit saving, changes made are already saved, but are still in observation and access before the CommitTrans, so that a Rollback can undo all changes and restore the original state.
So if no error triggers a rollback, you should see progress with each query in the (updated) tables. The return via myDB.RecordsAffected should show the same thing.

 

[cheekybuddha]

This all seems very familiar: UtterAccess

I agree here with @ebs17 - put all the SQL statements in an array, loop through them and use the same instance of CurrentDb to perform the .Execute's.

Better yet, create a stored procedure on your SQLServer that performs all the queries in a single transaction there. You can make it return a value which can indicate which statement fails if required.