Transactions using DAO and Sql Server linked tables

Question

I'm migrating a clasic Access application to Sql Server, i.e., DAO+Linked tables.

I've found a fustrating behavior: when i make changes using recordsets over linked tables, Access use more than one connection. More than one connection means more than one transaction at time on server side. These transactions are independent. Not nested.

Standard MS-Access behavior using linked tables to a .mdb files is different. There is only one transaction at time. Every db change is visible by any code that runs in the same DAO.Workspace before executing commit.

Rules has been changed and existing DAO code using client side transactions will fail.

If i add or update a record using a recordset open as dbOpenDynaset, any code trying to read them after will fail: Doesn't find new records and see existing records in the original state. Why? Because operations are maded in multiple and independent transactions

Executing the sample provided code, sql profiler will show you that different operations are made with different transactions ID's.

I've tested this using ADO and everything works well. But there are thousands code lines.

Is there any solution other than rewrite code using ADO?

Can i modify standard Access behaviour? ( use read uncommitted isolation level, instruct to not open new connections, ...)

Below code reproduces the problem. It's very simple:

1.- Open a recordset on existing record
2.- Add new record
3.- Try to read recently added record

If i use dbOpenDynaset in (1), i'll not see new record in (3).

I'm using Acc-2010, .accdb format files and Sql Server 2008 R2

Thanks.

    Private Sub test0()
     Dim bResult As Boolean

    Dim bUseTrans As Boolean 'New record added in transaction

    Dim rsExist As DAO.Recordset2 'Dummy recordset
     Dim tRecordsetExist As DAO.RecordsetTypeEnum 'Dummy recordset type:
                                                  '  with dbOpenDynaset fail.
                                                  '  Any other works fine

    Dim rs2Add As DAO.Recordset

    Dim rs2Read As DAO.Recordset 'Used to read recently added record
     Dim tRecordset2Read As DAO.RecordsetTypeEnum 'Recordset type used to read new record. Doesn't affect

    Dim bTranInitiated As Boolean 'Track if we are in transaction

    Dim lngExistingNumber As Long
     Dim lngNewNumber As Long
     Dim lngNewID As Long
     Dim strSQL As String
 On Error GoTo HandleErr

    'Invoices table definition in SS. Table is linked as [dbo_Invoices]:
     '   CREATE TABLE [dbo].[Invoices](
     '       [IdInvoice] [int] IDENTITY(1,1) NOT NULL,
     '       [InvoiceNumber] [int] NOT NULL,
     '       [InvoiceDescription] [varchar](50) NOT NULL,
     '    CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
     '   (
     '       [IdInvoice] Asc
     '   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
     '   ) ON [PRIMARY]

    Set wks = DBEngine.Workspaces(0)
     Set dbs = wks.Databases(0)

    bUseTrans = True 'Without transaction everything works well

    tRecordsetExist = dbOpenDynaset 'Dummy recordset type:
                                     '  dbOpenDynaset makes fail.
                                     '  Any other works fine

    tRecordset2Read = dbOpenForwardOnly 'Does not affect

    lngExistingNumber = 12001
     lngNewNumber = -lngExistingNumber

    'Clean previous runs of the test and make sure that referenced invoice exists.
     dbs.Execute "Delete from dbo_Invoices Where InvoiceNumber = " & lngNewNumber, dbFailOnError Or dbSeeChanges
     On Error Resume Next
     strSQL = "Insert Into dbo_Invoices (InvoiceNumber, InvoiceDescription) " & _
             " Values (" & lngExistingNumber & ", 'Original invoice' )"
     dbs.Execute strSQL, dbFailOnError Or dbSeeChanges
     On Error GoTo HandleErr

    If bUseTrans Then
         wks.BeginTrans
         bTranInitiated = True
     End If

    strSQL = "Select IdInvoice, InvoiceNumber from dbo_Invoices " & _
             " Where InvoiceNumber = " & lngExistingNumber
     If tRecordsetExist = dbOpenDynaset Then
         Set rsExist = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
     Else
         Set rsExist = dbs.OpenRecordset(strSQL, tRecordsetExist)
     End If
     If rsExist.BOF And rsExist.EOF Then
         Err.Raise vbObjectError, , "Original invoice " & lngExistingNumber & " not found"
     End If

    Set rs2Add = dbs.OpenRecordset("Select * from dbo_Invoices", dbOpenDynaset, dbAppendOnly Or dbSeeChanges)

    rs2Add.AddNew
     rs2Add!InvoiceNumber = lngNewNumber
     rs2Add!InvoiceDescription = "Invoice anulation, ref " & lngExistingNumber
     rs2Add.Update

    'After executing .Update rs2Add goes to .EOF. This action reposition the recordset on the new record
     rs2Add.Move 0, rs2Add.LastModified

    lngNewID = rs2Add!IdInvoice
     Debug.Print "New record added: IdInvoice = " & rs2Add!IdInvoice & ", InvoiceNumber = " & rs2Add!InvoiceNumber

    'Try to read the new record
     strSQL = "Select * from dbo_Invoices Where IdInvoice = " & lngNewID
     If tRecordset2Read = dbOpenDynaset Then
         Set rs2Read = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
     Else
         Set rs2Read = dbs.OpenRecordset(strSQL, tRecordset2Read)
     End If
     If (rs2Read.BOF And rs2Read.EOF) Then
         Err.Raise vbObjectError, , "rs2Read: Not found using IdInvoice = " & lngNewID
     End If
     Debug.Print "New record found with IdInvoice = " & rs2Read!IdInvoice
     rs2Read.Close

    bResult = True
 ExitHere:
     If Not wks Is Nothing Then
         If bTranInitiated Then
             If bResult Then
                 wks.CommitTrans
             Else
                 wks.Rollback
             End If
             bTranInitiated = False
         End If
     End If
     On Error Resume Next
     If Not rs2Add Is Nothing Then
         rs2Add.Close
         Set rs2Add = Nothing
     End If
     If Not rs2Read Is Nothing Then
         rs2Read.Close
         Set rs2Read = Nothing
     End If
     Exit Sub
 HandleErr:
     Dim e As Object
     If Err.Description Like "ODBC*" Then
         For Each e In DBEngine.Errors
             MsgBox e.Description, vbCritical
         Next
     Else
         MsgBox Err.Description, vbCritical
     End If
     bResult = False
     Resume ExitHere
     Resume
 End Sub

Answer 1

Unfortunately, Microsoft states the following about Workspace.IsolateODBCTrans Property: http://msdn.microsoft.com/en-us/library/office/bb208483(v=office.12).aspx

Some ODBC servers, such as Microsoft SQL Server, don't allow simultaneous transactions on a single connection. If you need to have more than one transaction at a time pending against such a database, set the IsolateODBCTrans property to True on each Workspace as soon as you open it. This forces a separate ODBC connection for each Workspace.

Not sure if this will help you deciding what to do.