ODBC timeout inside transaction

[blgilbert]

Hi,
I'm running an Access 2000 mdb connected to SQL 2000 with ODBC.I have a
transaction that looks something like this:

Public Sub DoSomething()
Dim rstThis as DAO.Recordset
Dim qdfThis As DAO.QueryDef
Set qdfThis = CurrentDb().QueryDefs("qryMyQueryName")
qdfThis.Execute dbFailOnError + dbSeeChanges

If qfdThis.RecordsAffected>0 Then
Set qfdThis=CyrrentDb().QueryDefs("qryMySecondQueryName") '
querydef inserts into 'tbl1'
qfdThis.Execute dbFailOnError + dbSeeChanges
Endif
DoSomethingElse
CommitTrans
End Sub

' The following is contained in a class module
Public Sub DoSomethingElse()
Dim rst As DAO.Recordset
Dim strSQL as String
strSQL = "Select * From tbl1 Where blah-de-blah"
Set rst = CurrentDb().OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
' do stuff here
Loop
End Sub

The table being read in DoSomethingElse is the same table being
inserted into in the 2nd execute statement in the main sub. When It
reaches the OpenRecordset command in DoSomethingElse, it hangs and
eventually returns DAO error 3146, ODBC call-failed.

This works fine when I'm connecting to a Jet back-end. It works fine
when I comment out the transaction statements. I've also tried using
Workspaces(0).BeginTrans, but it didn't help.

Any ideas or thoughts?

Thanks in advance,
Barry Gilbert

 

[david epsom dot com dot au]

ODBC transactions are broken in the current version of Jet.

You may wish to use ADO, or Access 97, or just give up
on the transactions.

(david)

 

[Brian Wilson]

Hi,
I'm running an Access 2000 mdb connected to SQL 2000 with ODBC.I have a
transaction that looks something like this:

Public Sub DoSomething()
Dim rstThis as DAO.Recordset
Dim qdfThis As DAO.QueryDef
Set qdfThis = CurrentDb().QueryDefs("qryMyQueryName")
qdfThis.Execute dbFailOnError + dbSeeChanges

If qfdThis.RecordsAffected>0 Then
Set qfdThis=CyrrentDb().QueryDefs("qryMySecondQueryName") '
querydef inserts into 'tbl1'
qfdThis.Execute dbFailOnError + dbSeeChanges
Endif
DoSomethingElse
CommitTrans
End Sub

' The following is contained in a class module
Public Sub DoSomethingElse()
Dim rst As DAO.Recordset
Dim strSQL as String
strSQL = "Select * From tbl1 Where blah-de-blah"
Set rst = CurrentDb().OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
' do stuff here
Loop
End Sub

The table being read in DoSomethingElse is the same table being
inserted into in the 2nd execute statement in the main sub. When It
reaches the OpenRecordset command in DoSomethingElse, it hangs and
eventually returns DAO error 3146, ODBC call-failed.

This works fine when I'm connecting to a Jet back-end. It works fine
when I comment out the transaction statements. I've also tried using
Workspaces(0).BeginTrans, but it didn't help.

Any ideas or thoughts?

Thanks in advance,
Barry Gilbert

Since you are using SQL Server as a back end, why not re-write this as a
stored procedure?

 

[blgilbert]

I have several options for rewrite, including a SQL Server stored proc
and switching over to ADO. This is a fairly complex app and I'm trying
to avoid a rewrite unless necessary.

It seems like this should work as written.

 

[Brian Wilson]

I have several options for rewrite, including a SQL Server stored proc
and switching over to ADO. This is a fairly complex app and I'm trying
to avoid a rewrite unless necessary.

It seems like this should work as written.

Indeed it should, but as David has pointed out, things do not work as they
should with odbc transactions and the current version of JET. In this case,
I believe a re-write is necessary and if I was doing it, I would use a
stored procedure to make sure either both steps failed or succeded.
.... and just because you have an sp on the server, you don't have to switch
to ADO coding to execute it.

 

[blgilbert]

This is disappointing because my code is somewhat OO; several of the
calls that are inside the transaction reside in a class module. It will
require a big rethink of my architecture. Bummer.

Thanks for your suggestions.