I've got a linked sql server table (tblFoo) in access2007
and I'm trying to process a transaction, see below
but it fails setting rs2 with
ODBC--call failed. ,DAO.Database ,3146
- [Microsoft][ODBC SQL Server Driver]Timeout
expired ,ODBC.Database ,0

can I not open two recordsets for the same record within a
transaction ?
what are the alternatives ?
using one recordset for both is not an option


Set wrk = DBEngine.Workspaces(0)
wrk.BeginTrans
Set db = wrk.Databases(0)
set rs = db.openRecordset("SELECT id FROM tblFoo WHERE id = 1")
...

set rs2 = db.openRecordset("SELECT * FROM tblFoo WHERE id = 1")
....

wrk.commitTrans

Hi Roger,
the error 3146 "ODBC call failed" means, there occurs an error at your BE-ODBC-database.
This error occurs often if you have sql-errors.

In your case, I think, it is a locking problem.
With your first recordset you lock the record and the second recordset cannot use the locked record! This lasts until timeout which causes the error.
Probably you only want to read the record at least in your recordset rs.
I think it depends on your BE-DBMS, how you can ensure, that the reading doesn't raise a lock. Then the second access on the same record must be possible.
grts
Stefan

On Thu, 24 Feb 2011 13:02:21 -0800, Roger wrote:

The problem with transactions is if you update anything, what you update
is locked until you commit or rollback, attempting to open the second
recordset on the same table may encounter that lock, it will wait for
that lock to be cleared but the code that is waiting is the same code
that locked it hence the timeout.

You may get better results re-writing your updates as a stored procedure
or try opening the second recordset before you update anything in the
first.

--
Travis Crow