none
access to sql server, is it possible to use transactions RRS feed

  • Question

  • Option Compare Database
    Option Explicit
    
    Private Sub TestDoInsert(intTest As Integer)
    Dim rstOut As Recordset
    Set rstOut = CurrentDb().OpenRecordset("select * from Test", dbOpenDynaset, Options:=dbFailOnError + dbSeeChanges)
    If Not rstOut Is Nothing Then
      With rstOut
        .AddNew
        !TestField = intTest
        .Update
      End With
      rstOut.Close
    End If
    Set rstOut = Nothing
    End Sub
    
    Private Sub TestInsert()
    BeginTrans
    TestDoInsert 1
    TestDoInsert 2
    EndTrans False
    End Sub
    
    

    The above code works fine if the second call to "TestDoInsert" is commented out.

    Otherwise the second call to OpenRecordset() times out and gives a 3146 (ODBC call failed) error.

    Is this something that just doesn't work?

    Do I need to do something to configure it so it does?

    MTIA,
    e.

    Wednesday, June 27, 2018 12:11 PM

All replies

  • Further, any subsequent calls to the connection fail.

    That is, in a form, opening tables from the IDE, using DCOUNT, etc.

    Also, any open SSMS instances also hang when required to access data in the transaction.

    Even after the transaction is closed along with Access.

    What is going on?

    Thanks,
    e.

    Wednesday, June 27, 2018 1:55 PM
  • Are these linked tables to SQL Server? If so, I think you have two separate connections, which probably will not support a transaction. Also, closing the Recordset will end the transaction.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Wednesday, June 27, 2018 3:22 PM
  • Hello MTIA,

    I failed to test your code since the EndTrans is an undefined method. Did I missed any reference?

    Besides, what's the test table? Is it linked table as Paul said?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 28, 2018 7:43 AM
  • Sorry, EndTrans is a wrapper for Commit or Rollback, depending on the value of the parameter.

    The test table is a linked table to a SQL back end.

    So closing the recordset implicitly ends the transaction.

    That complicates things.

    Hmm. Perhaps it's time to push everything back into SQL.

    And none of this explains why Access gets so upset and refuses to do anything until reboot after the first failure.

    Many thanks for your help,
    e.

    Friday, June 29, 2018 2:36 PM
  • There no reason I can see why the transaction does not work.

    I would consider creating “one” database instance, but what you have looks “close” to what should work.

    I would try:

    Dim db    as dao.database

    Set db = currentDB

    BeginTrans

    TestDoInsert 1,db

    TestDoInsert 2, db

    CommitTrans

    Private Sub TestDoInsert(intTest As Integer, db as dao.database)

    Dim rstOut As Recordset

    Set rstOut = db.OpenRecordset("select * from Test"….

    Etc. etc. etc.

    Also, does your code compile? Do a debug->compile to ensure no syntax errors.

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Friday, June 29, 2018 8:59 PM