Do We Have Transactions in Ms-Access

Do we have transactions in MS-Access?

It looks like we do: MSDN - TRANSACTION Statement (Microsoft Access SQL)

Transactions are not started automatically. To start a transaction, you must do so explicitly using:

BEGIN TRANSACTION

Conclude a transaction by committing all work performed during the transaction:

COMMIT [TRANSACTION | WORK]

Conclude a transaction by rolling back all work performed during the transaction:

ROLLBACK [TRANSACTION | WORK]

Are we able to use MS Access transactions with acCmdSaveRecord?

No, not as a general rule. So the use of transactions is for VBA code, and sql or recordset code you have. Unfortunately, the transactions don't apply to forms.

However, you can as a work around:
start a transaction.
Create a recordset.
Shove (assign) the forms record source to the above created record set.

Now, the user can freely edit, change, add, delete to that form, but if you don't commit the record set after all is above and done, then the editing etc. will be ignored. It can be bit of a pain to set this up, and for sub forms, I never really cooked up a seamless coding approach that worked well.

So if you willing to create a recordset in code and then assign that reocrdset to the forms datasouce, the result is now a form that is bound to the transaction you started in code.

Access SQL Transactions?

Transactions are not supported in MSAccess when you are using them in a query. Try firing a function to create those queries, which you can do in a module and have it run on PageLoad or even an OnTimer event. You can use the BeginTrans and Rollback commands inside the module.

How to properly use transactions with workspaces in ms-access

This is pretty odd. DBEngine.Workspaces(0) is the default workspace, and you are not supposed to close it. Normally you only close workspaces that you have opened yourself with DBEngine.CreateWorkspace.

Your problems may result from mixing your own workspace variable with CurrentDb, which isn't part of your workspace (not really sure about that).

When using the default workspace (either for possible rollback or for performance), I always use a separate database variable, that is defined as "child" of the workspace:

Dim WS As Workspace
Dim DB As Database

Set WS = DBEngine(0)
Set DB = WS.Databases(0)

WS.BeginTrans

DB.Execute "Stuff"    ' Not CurrentDb 

If "everything ok" Then
    WS.CommitTrans
Else
    WS.Rollback
End If

' With a local WS variable, even this is not necessary, but definitely no WS.Close here!
Set WS = Nothing

In your case you'd have to declare DB as public variable, or pass it as parameter to testChildTransaction()

SQL query for last transaction on MS Access

To pull out the most recent transaction par customer, a correlated subquery should get the job done:

select t.*
from mytable t
where customerpaydate = (
    select max(Customerpaydate) from mytable t1 where t1.customerid = t.customerid      
)

How do I implement transactions in Access?

Researching these transactions, I found this link promising:

http://support.microsoft.com/kb/248011

Yet, there seem to be some other issues with it.

MS Access 2010: Adding transaction management into a form

you should use transactions only if you are inserting/updating multiple SQL statements which then make sense to for rollback. Also use transactions just before SQL execution and trap errors to find out which SQL statement triggers the failure.

pseudo:

1. Do the validation
2. Prepare SQL statements
3. Prepare SQL Statement 2
4. Begin Transaction
5. Execute SQL statements
6. Commit or rollback

in code it would be:

Private Sub mTrans()

    Dim myDB As DAO.Database
    Set myDB = CurrentDb

    Dim SQL_SET As String
    SQL_SET = "First sql statement"

    On Error GoTo ERROR_SQL1:
    DBEngine.BeginTrans
        myDB.Execute SQL_SET, dbFailOnError
        On Error GoTo ERROR_SQL2:
        SQL_SET = "second sql statement..." 'either use the same variable or use SQL_SET1 for better overview
        myDB.Execute SQL_SET, dbFailOnError
    DBEngine.CommitTrans

EXIT_SUB:
    On Error Resume Next
    Set myDB = Nothing
    Exit Sub
ERROR_SQL1:
    DBEngine.Rollback
    MsgBox "Error while executing sql_1. " & vbNewLine & "System msg: " & Err.description
    GoTo EXIT_SUB

ERROR_SQL2:
    DBEngine.Rollback
    MsgBox "Error while executing sql_2. " & vbNewLine & "System msg: " & Err.description
    GoTo EXIT_SUB
End Sub

---

Related Topics