Does Access fully support SQL transactions? Once again, code that works with SQL Server is giving me trouble when I try to make it work with MS Jet on ODBC. I have a series of actions - data reads and writes, then table creation - which are all enlisted in a transaction (I'm using ADO.Net). At one time during the transaction I try to create a table, which has a Primary Key consisting of two fields, one of which is a foreign key. The error I get refers to a failure in locking the table, which contains that foreign key. Here is the error:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The database engine could not lock table 'DlIndexTable' because it is already in use by another person or process.
and here is the SQL command that caused it:
CREATE TABLE DlNumericData1_1_0Table (SessionStartTime DATETIME NOT NULL REFERENCES DlIndexTable, ChTimestamp DOUBLE NOT NULL, Channel001data REAL NOT NULL, Channel002data REAL NOT NULL, Channel003data REAL NOT NULL, Channel004data REAL NOT NULL, Channel005data REAL NOT NULL, Channel006data REAL NOT NULL, Channel007data REAL NOT NULL, Channel008data REAL NOT NULL, CONSTRAINT NumPriKey1_1_0 PRIMARY KEY (SessionStartTime, ChTimestamp))
Any ideas? Thanks.
P.S. I just tried commenting out all the transaction code and it worked. What to do...?
By the way, if I dropped the ADO.Net-style transaction enlistment and stayed with classic SQL, it'd still fail, albeit in a different way. As soon as I issue the "BEGIN TRANSACTION" command I get the following error:
ERROR  [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
I thought Jet 4.0 supported BEGIN TRANSACTION, COMMIT and ROLLBACK?
P.S. Do those only work with OLEDB, not ODBC?
Here's what I found out. According to the documentation, Access/Jet only supports SQL-style transactions (BEGIN TRANSACTION, COMMIT, etc.) under OLEDB but the problem I had was not with lack of support of transactions. I went back to ADO.Net-style transaction handling and changed something else. Apparently, even though it works fine under SQL Server, creating tables within the same transaction as other commands is not a good idea. I made it a separate transaction and it's working now.