Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

odbc timeout when using a transaction

108 views
Skip to first unread message

Roger

unread,
Feb 24, 2011, 4:02:21 PM2/24/11
to
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

stefan

unread,
Feb 24, 2011, 5:30:40 PM2/24/11
to
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

Travis Crow

unread,
Mar 9, 2011, 6:38:05 AM3/9/11
to
On Thu, 24 Feb 2011 13:02:21 -0800, Roger wrote:

> 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

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

0 new messages