I believe I am having a problem with transactions that MS Access is creating on my behalf. The environment is an MS Access 2003 (DAO) front end and SQL Server 2000 backend.
I inherited a program that was originally getting tables out of balance with each other. I concluded that the complex program was sometimes not completing all the updates, so I converted the main accounting routine to explicit transactions (BeginTrans, CommitTrans, and Rollback). The new application had problems unpredictably locking up. I then migrated from MS-Access transactions to a strategy of commands being stored in a command sequence table for later execution (“later” being when the user closed the screen that he generated them on). So, the commands that are going to be used to update, are stored in a table, then I have an execute function that executes all the commands in the table. I can therefore execute the UPDATEs, INSERTs and DELETEs separate from the code that generates the sequence of commands.
The program continued locking up, but at more predictable points. Eventually, I was able determine that executing either of the two SQL statements (UPDATE … WHERE …, and DELETE … WHERE …) triggered the start of the sequence that would eventually lead to locking up. I converted the updates to using a SELECT statement (i.e., recordsets) and a loop with explicit .Edit / .Update statements. I converted the Deletes to a SELECT statement, and a loop with explicit .Delete statements. The locking up disappeared except for one place where I eventually surmised that deleting a record from a browsing subform (using the Delete Record option) causes MS Access to internally generate a DELETE … WHERE … statement.
“Locking up” is defined as execute some of the commands without problems, then stopping for 60 seconds, getting an error message saying that ODBC timed out, and other networked PCs are also stopped from utilizing the open tables. It is not at all unusual for the computer to execute identical commands two or three times without failure, then lock up on the next time the identical piece of code is executed.
From a locked up PC, if I try to “exit”, I receive two identical error messages saying “Operation not supported in transactions”, even though I have removed all the explicit transaction commands (as I now handle that functionality myself). All other networked PCs are freed to continue executing the instant the first PC exits. If the first PC was in stepped mode viewing the code, “exiting” is stopping the debugger. If the first PC was in not in the debugger, “exiting” required closing the application, but not necessarily closing MS Access.
I have done “compact and repair” of the database. I have created a new database, copied everything into that new database. I have even copied the source code to the clipboard from the module that used to do transactions, deleted the module, pasted the clipboard into Notepad, copied the code from Notepad back to the clipboard, and pasted the code into a new module.
Has anyone has similar problems with transactions? Is there some possibility that internally MS Access had remembered that certain functions contained transactions at one time, so it always handles those functions with transaction processing (even if the commands are no longer there)? Is there some setting on SQL Server that controls how many update commands can make up one transaction? Is there some way to turn off these implied transactions that MS Access generates?