Multiple transactions fails (delete and insert) (1 Viewer)

hitech70 · Jun 20, 2018

This is an MS Access application connecting to SQL sever database through ODBC
I have a login where I need to delete records from the table before adding another records t the same table.

I get the existing records in a record set and then I loop through and delete them one by one

Next, I try adding the new records one by one from the form to the same table.

The connection times out when I try to get the table definition using CurrentDb.TableDefs.

I will post some of the code below.

Code:

Private Sub Command_Click()
 

    Dim ws          As DAO.Workspace
      
    Dim rs3         As DAO.Recordset
    Dim rs5         As DAO.Recordset
    Dim rs3Log      As DAO.Recordset
    Dim rs5Log      As DAO.Recordset


    Dim lngID       As Long
 

Set ws = DBEngine.Workspaces(0)
ws.BeginTrans



Set rs3 = GetRecords(Me.ID.Value)


Do Until rs3.EOF = True Or rs3.BOF = True ' loop until all records were processed
    rs3.Delete
    rs3.MoveNext
Loop
rs3.Close
 

  
        Set rs5 = CurrentDb.TableDefs(S_TN_Table).OpenRecordset(dbOpenDynaset, dbPessimistic + dbSeeChanges)

 

        For i = 1 To NoOfControls Step 1
            
            ctrl1_1 = ctrlName1 & CStr(i)
            ctrl1_2 = ctrlName2 & CStr(i)
            
            Set cboctrl1_1 = sfrm.Controls(ctrl1_1)
            Set cboctrl1_2 = sfrm.Controls(ctrl1_2)
                                           
            If (cboctrl1_1.Value <> vbNullString) Then
                
                With rs5
                    .AddNew
                   
                    .Fields(s_FN_Data2).Value = Nz(Me.ID.Value, .Fields(s_FN_Data2).DefaultValue)
                    .Fields(s_FN_Data3).Value = Nz(cboctrl1_1.Value, Null)
                    .Fields(s_FN_Data4).Value = Nz(cboctrl1_2.Value, Null)
                    
                    
                    .Update
                    .Bookmark = .LastModified ' This repositioning allows us to look what the value the identity column was assigned.
                    lngID = .Fields(PK_ID).Value
                
                End With
         
            End If
        Next I

       
    ' Attempt a commit of the transaction.
    ws.CommitTrans

 

End Sub
 

Private Property Get GetRecords(Optional ByVal PK_ID As Long = 0) As DAO.Recordset
       
   

    Dim rs2 As DAO.Recordset
    
    Dim sSql As String
    Dim fltr As String
 
    fltr = " WHERE  ID = " & CStr(Nz(PK_ID, 0))     
    
    sSql = "SELECT * FROM " & S_TN_Table
    sSql = sSql & fltr
    Set rs2 = CurrentDb.OpenRecordset(sSql, dbOpenDynaset, dbPessimistic + dbSeeChanges)
    
    
    Set GetControlByRecord_ID = rs2
    
    Set rs2 = Nothing

End Property

Frothingslosh · Jun 20, 2018

This is a common beginner mistake. Instead of pulling a recordset and deleting each row individually, you should create a delete query. You can use a parameter to enter the PK_ID you're matching against, and then just run the query.

The parameter can be filled a few ways:

If the PK_ID appears on an open form, you can reference it directly in the query with Forms!FormName!ControlName right in the query itself.
If the PK_ID is not in any form, then you have two other ways to supply it as a parameter:
1. You can create a TempVar, and reference in the query with TempVars!VariableName
2. You can use VBA to assign it directly.

Pulling this off in VBA is done thusly:

Method 1

Code:

Set qdf = CurrentDb.QueryDefs("SavedQueryName")
For Each prm in qdf
    prm.Value = Eval(prm.Name)
Next prm
 
qdf.Execute dbSeeChanges + dbFailOnError

Basically, when calling a query through VBA, the Forms!FormName!ControlName becomes the name of the parameter itself. Eval simply makes the computer process the supplied string like it's a function, so in this case, it returns the value of ControlName in open form FormName, just like if you were to use Forms!FormName!ControlName in any other piece of code.

Method 2.1
Since the TempVar itself is written into the query, you just need to assign the tempvar, then run the query.

Code:

TempVars.Add "PK_ID", PK_ID
CurrentDb.Execute("QueryName", dbSeeChanges + dbFailOnError)
TempVars.Remove "PK_ID"

Method 2.1
Here you have to supply the parameter directly.

Code:

Set qdf = CurrentDb.Queries("QueryName")
qdf.Parameters("ParameterName").Value = PK_ID
qdf.Execute dbSeeChanges + dbFailOnError

The saved delete query would be something just like this
DELETE * FROM TableName WHERE ID = [Forms]![FormName]![ControlName];
or
DELETE * FROM TableName WHERE ID = [EnterIDValue]

Then see if your add works.

Other Notes
ID is a REALLY bad name for a field. At the very least, you should use the table name coupled with ID, such as EmployeeID, ContractID, etc. It makes it a lot easier to see what's linking to what when every ID field in the database has a unique name rather than just all being called ID.

hitech70 · Jun 21, 2018

Thank you Frothingslosh.

Your code worked for me. But I would really appreciate if you can tell me why my code didn't work, or where I made a mistake?

And you are right about the "ID", I have a different variable name in my code, but I changed it here for posting the code.

Now, I went a step further in my code, and before deleting the records, I need to ad then to a log table, and similarly, after adding the new records, I need to add them to the same log table.

The application times out when executing the last query to insert the new records to the Log table.

Code:

Set ws = DBEngine.Workspaces(0)
ws.BeginTrans

        TempVars.Add "ID", Me.ID.Value
        CurrentDb.Execute "INSERT INTO " & S_TN_Log & " ( list of columns ) " & " SELECT  list of values " & " FROM " & S_TN_Table & " WHERE ID = " & [TempVars]![ID], dbSeeChanges + dbFailOnError
        TempVars.Remove "ID"
        
        TempVars.Add "ID", Me.ID.Value
        CurrentDb.Execute "DELETE * FROM " & [S_TN_Table ] & " WHERE ID = " & [TempVars]![ID], dbSeeChanges + dbFailOnError
        TempVars.Remove "ID"
          

        Set rs5 = CurrentDb.TableDefs(S_TN_Table).OpenRecordset(dbOpenDynaset, dbPessimistic + dbSeeChanges)

 

        For i = 1 To NoOfControls Step 1
            
            ctrl1_1 = ctrlName1 & CStr(i)
            ctrl1_2 = ctrlName2 & CStr(i)
            
            Set cboctrl1_1 = sfrm.Controls(ctrl1_1)
            Set cboctrl1_2 = sfrm.Controls(ctrl1_2)
                                           
            If (cboctrl1_1.Value <> vbNullString) Then
                
                With rs5
                    .AddNew
                   
                    .Fields(s_FN_Data2).Value = Nz(Me.ID.Value, .Fields(s_FN_Data2).DefaultValue)
                    .Fields(s_FN_Data3).Value = Nz(cboctrl1_1.Value, Null)
                    .Fields(s_FN_Data4).Value = Nz(cboctrl1_2.Value, Null)
                    
                    
                    .Update
                    .Bookmark = .LastModified ' This repositioning allows us to look what the value the identity column was assigned.
                    lngID = .Fields(PK_ID).Value
                
                End With
         
            End If
        Next I

 
        TempVars.Add "ID", Me.ID.Value
        CurrentDb.Execute "INSERT INTO " & S_TN_Log & " ( list of columns ) " & " SELECT  list of values " & " FROM " & S_TN_Table & " WHERE ID = " & [TempVars]![ID], dbSeeChanges + dbFailOnError
        TempVars.Remove "ID"
        
    ' Attempt a commit of the transaction.
    ws.CommitTrans

Mark_ · Jun 21, 2018

Not sure of all possible issues, but your coding does an add of the record, fills fields with values, then does an update for EACH record. This is two times you try to deal with the SAME record. You would normally simply insert a record that contains all of the fields filled.

At the least how you are doing this will double the time it takes to run your code.

Multiple transactions fails (delete and insert) (1 Viewer)

hitech70

New member

Frothingslosh

Premier Pale Stale Ale

hitech70

New member

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread