MS Access linked table insert transaction commit

Question

I have an Access 2016 database that started off as a classic access MDB. Along the way, it was converted to a .accdb, and linked to a backend SQL Server. All of the tables used were converted to linked tables.

There is code that performs code something like this:

sql = "insert into [TableA]..."
CurrentDB.Execute sql

DoCmd.OpenReport(...)

What I'm finding is that intermittently the Insert into the backend SQL doesn't seem to complete before the OpenReport command selects the data from the same table.

Would it be reasonable to solve this by using. Assuming my issue is that the data is not committed to the SQL table in time for the Report Execution to see it, would the following code insure this?

sql = "insert into [TableA]..."
CurrentProject.Connection.BeginTrans
CurrentProject.Connection.Execute sql
CurrentProject.Connection.CommitTrans
DoCmd.OpenReport(...)

To be sure of the problem, just before opening the report, check the data in the table with a dlookup or something to see if the data is there. — wazz
No, that code would not insure the INSERT succeeded. Really need to determine why the INSERT would fail. What about the data would cause failure? Access should not open report until it completes the INSERT action. — June7

Erik A Erik A · Accepted Answer · 2018-01-06T16:45:58

If you're using ADO, you can use the RecordsAffected output parameter to determine if a record got inserted.

You can use the following:

sql = "insert into [TableA]..."
Dim recordsAffected As Long
CurrentProject.Connection.Execute sql, recordsAffected
If recordsAffected <> 0 Then
    DoCmd.OpenReport(...)
Else
    'Nothing got inserted
End If

Or, if you don't like the extra variable, you can consider using a helper function:

Public Function ADOExecute(Query As String) As Long
    CurrentProject.Connection.Execute Query, ADOExecute
End Function

sql = "insert into [TableA]..."
If ADOExecute(sql) <> 0 Then
    DoCmd.OpenReport(...)
Else
    'Nothing got inserted
End If

Note that I highly recommend using a connection to your SQL Server instead of CurrentProject.Connection, which is an ADO connection to Access.

MS Access linked table insert transaction commit

1 Answers