Heya people,
Does anybody out there have any experiece with dealing with transactions in access 97?
I'm currently upgrading our access 97 database so that it uses SQL Server as a data store, rather than native access databases.
The latest hurdle, which I'm pretty sure I won't be able to get around is the way that transactions are handled when using ODBC linked tables.
A simplified version of our code is.
1. begin transaction
2. open a recordset on a table
3. add a new record to the table
4. get the id of the record just added
5. execute an update SQL statement based on the id of the record just added
6. Commit transaction.
With normal linked access tables this works fine, but when doing it on odbc linked tables step 5 takes an age to run and returns with the incredibly helpful error message of 'ODBC Call Failed" - thanks access!
The problem I suspect is with way jet handles transactions (I'm already aware that nested transactions aren't allowed on odbc linked tables).
I'm guessing that jet allows you to be flexible in your transactions ie. Reading records from tables as if they were there, but don't get put there until after the transaction has been committed. When using odbc linked tables your lose this flexibily because of the more limited interface that odbc provides you with.
Is there any way I can get around this problem using different types of locking. I've tried but am really just having a guess here and there.
Thanks,
lachlan
Does anybody out there have any experiece with dealing with transactions in access 97?
I'm currently upgrading our access 97 database so that it uses SQL Server as a data store, rather than native access databases.
The latest hurdle, which I'm pretty sure I won't be able to get around is the way that transactions are handled when using ODBC linked tables.
A simplified version of our code is.
1. begin transaction
2. open a recordset on a table
3. add a new record to the table
4. get the id of the record just added
5. execute an update SQL statement based on the id of the record just added
6. Commit transaction.
With normal linked access tables this works fine, but when doing it on odbc linked tables step 5 takes an age to run and returns with the incredibly helpful error message of 'ODBC Call Failed" - thanks access!
The problem I suspect is with way jet handles transactions (I'm already aware that nested transactions aren't allowed on odbc linked tables).
I'm guessing that jet allows you to be flexible in your transactions ie. Reading records from tables as if they were there, but don't get put there until after the transaction has been committed. When using odbc linked tables your lose this flexibily because of the more limited interface that odbc provides you with.
Is there any way I can get around this problem using different types of locking. I've tried but am really just having a guess here and there.
Thanks,
lachlan