AutoCommit

[Steven]

[e-mail] Dec 24, 5:47 am show options

Newsgroups: comp.lang.perl.modules
From: [e-mail] - Find messages by this author
Date: 24 Dec 2004 05:47:04 -0800
Local: Fri, Dec 24 2004 5:47 am
Subject: AutoCommit
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I am writting an application in MS Access/VBA. I have an event that
will enter information into (so far) two tables via dynamic SQL
scripts:
1. INSERT info. into table A
2. INSERT info. into table B
But, I want to somehow turn an AutoCommit off and Commit when I want it
to commit.
Whereas if the INSERT into table B fails for some add reason. I just
want to exit the event and not have the INSERT into table A already
commited. I want to commit it when all INSERTS have done their job.
Thanks...

[Rick Brandt]

Steven wrote:

[e-mail] Dec 24, 5:47 am show options

Newsgroups: comp.lang.perl.modules
From: [e-mail] - Find messages by this author
Date: 24 Dec 2004 05:47:04 -0800
Local: Fri, Dec 24 2004 5:47 am
Subject: AutoCommit
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I am writting an application in MS Access/VBA. I have an event that
will enter information into (so far) two tables via dynamic SQL
scripts:
1. INSERT info. into table A
2. INSERT info. into table B
But, I want to somehow turn an AutoCommit off and Commit when I want
it to commit.
Whereas if the INSERT into table B fails for some add reason. I just
want to exit the event and not have the INSERT into table A already
commited. I want to commit it when all INSERTS have done their job.
Thanks...

You need to use a transaction. Here is a sample from the Access 97 help file.

Sub BeginTransX()

Dim strName As String
Dim strMessage As String
Dim wrkDefault As Workspace
Dim dbsNorthwind As Database
Dim rstEmployees As Recordset

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")

' Start of outer transaction.
wrkDefault.BeginTrans
' Start of main transaction.
wrkDefault.BeginTrans

With rstEmployees

' Loop through recordset and ask user if she wants to
' change the title for a specified employee.
Do Until .EOF
If !Title = "Sales Representative" Then
strName = !LastName & ", " & !FirstName
strMessage = "Employee: " & strName & vbCr & _
"Change title to Account Executive?"

' Change the title for the specified employee.
If MsgBox(strMessage, vbYesNo) = vbYes Then
.Edit

!Title = "Account Executive"
.Update
End If
End If

.MoveNext
Loop

' Ask if the user wants to commit to all the changes
' made above.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If

' Print current data in recordset.
.MoveFirst
Do While Not .EOF
Debug.Print !LastName & ", " & !FirstName & _

" - " & !Title
.MoveNext
Loop

' Roll back any changes made by the user since this is
' a demonstration.
wrkDefault.Rollback
.Close
End With

dbsNorthwind.Close

End Sub

[Bas Cost Budde]

Steven wrote:

But, I want to somehow turn an AutoCommit off and Commit when I want it
to commit.

Use a transaction. It is a method of the Workspace object.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea