Transactions with Dao on SQL-Server

[swatty]

How can i use transactions on SQL-Server wiht DAO ?

I am working on an access mdb which must be transfered to sql db instead of access db.

Most updates and data access was writing using docmd's and dlookups , dmax, dcount.

When using same code on sql-server i encouter problems with locks on tables.Where I get a sql-server timeout error.

My boss wants DAO is still used so I can't change it to ADO.

Anyone know how to use dao with transactions ??

[PilgrimPete]

Take a look at the BeginTrans, CommitTrans and Rollback methods of the Workspace object.

[swatty]

Someone has a sample on how to use this.

I call different routines from a sub where different actions must be done on same db.

Find nothing usefull on previous answer.

[PilgrimPete]

Nothing at all?

Does this help any..?

Private Sub TransactionTest()
On Error GoTo TransactionTest_Error
    Dim ws As DAO.Workspace
    Dim cn As DAO.Connection
    Dim db As DAO.Database
    Dim bCommit As Boolean
    
    'create an ODBC direct workspace
    Set ws = DBEngine.CreateWorkspace("testws", "", "", dbUseODBC)
    Workspaces.Append ws
    'begin the transaction
    ws.BeginTrans
    
    'open a database using a DSN
    Set db = ws.OpenDatabase("test")
    
    'make some changes
    db.Execute ("UPDATE myTable SET Name = 'Pilgrim' WHERE PersonID='abc123'")
    db.Execute ("UPDATE myTable SET Name = 'Pete' WHERE PersonID='def123'")
    
    
    
TransactionTest_Exit:
    'shall we commit the changes?
    If bCommit Then
        ws.CommitTrans
    End If
    'tidy up
    db.Close
    ws.Close
    Set db = Nothing
    Set ws = Nothing
    Exit Sub
TransactionTest_Error:
    ws.Rollback
    Resume TransactionTest_Exit
End Sub