How handle VBA transaction code in split Access db application being migrated to SQL Server?

Philip Freihofner 1 Reputation point

I have a database application written using VBA and Access. I'm in the process of converting the back end to SQL Server, retaining the front end code. The guides provided have mostly been helpful, but I've run into a problem with converting code that is wrapped within DBEngine.BeginTrans and .CommitTrans methods. When more than one edit to the backend occurs while the transaction is open, there is a significant pause followed by the error message: OBDC call failed. For example, if the following code fragment is executed a second time, the error occurs on the line qdf.execute.

Set qdf = dbTrans.CreateQueryDef("", strSQL)  

qdf.Execute (dbSeeChanges)  

Set qdf = Nothing  

If this code is only called once, there is no error and the UPDATE occurs as desired. (The String strSQL is a legal UPDATE query which the new value coming from the field contents.)

The best info I've been able to find that provides clues is the following question:

Handling transactions with DAO while inserting into SQL tables

The situation described there is a little different from the typical case that I have. The main situation I have is that the customer wishes to go through the following workflow:

  1. explicitly open a form for editing
  2. make edits on that form
  3. explicitly commit the edits

There are at least a dozen areas handled in this manner in the database application. Each of those areas can easily have 10 or more fields that have their own UPDATE queries. We have buttons for Edit/Save/Undo which handle the enabled/locked properties of the fields, and the _AfterUpdate methods for those fields create and execute the QueryDefs that update the table fields. The Edit/Save/Undo buttons also handle calls to BeginTrans, SaveTrans, and RollbackTrans, respectively.

The answer to the linked question starts with this line: "Unfortunately, you cannot in a reliable way use transactions from JET/ODBC to sql server." What exactly does that mean? I am able, on my forms, to open, make a single edit and close the forms. However, attempts to make two edits gives the error ODBC call failed. Is there any documentation on this behavior?

If I am reading the above linked question correctly, I will have to rewrite each of the individual edits to the SQL Server backend table as Transact-SQL while making use of the SQL Server Management Studio (SSMS). Assuming I go through this rewrite, will there be a way "wrap" a form's set of fields in the desired Edit/Save/Undo setup that the customer is currently accustomed to?

Is there by chance a tutorial or guide that give an example of the required conversion process for the scenario I've described? I'm going to have to relearn Transact-SQL (I last used it 20 years ago) as well as learn to use SSMS if that is the preferred way to work with TSQL.

If there is another way to handle this situation, I would love to hear about it! There is a reference to ADO, but I recall going through an extensive process converting all the ADO in this application to DAO and do not like the idea of circling back, especially since I don't fully recall the circumstances that required this. There are also references to stored procedures, but I'm pretty sure I don't want to add another layer of executing software if it can be avoided. Using stored procedures and executing TSQL are two distinct processes, yes?

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
503 questions
{count} votes