DB ransaction use in Access 2000/3: significant example sought

[Guest]

I'm developing an Access app in which I must simultaneously modify three
different tables, ensuring that either all three tables are modified
"simultaneously" or none is. Is there a way to do this without using
recdordsets? I've done quite a bit of DB transaction processing on another
platform using SQL and PL/SQL but that other platform doesn't use DAO, or
ADO, or recordsets by that name. Are there any sample applications of the DB
transaction concept in the MS DB templates? If not, does anyone know of a
good publicly available example?
(This duplicates my earlier post to the Access Programming group. I'm
cross-posting it here because the table mods in question will happen or not
happen depending on what users do on a parent-and-child form linked to three,
not two, tables.)

 

[Albert D.Kallal]

Is there a way to do this without using

recdordsets?

For your update code, or you UI code?

I've done quite a bit of DB transaction processing on another
platform using SQL and PL/SQL but that other platform doesn't use DAO, or
ADO, or recordsets by that name.

You don't have to use a reocrdset to process data. You simply execute sql
commands. The fact of you having used a procedural system such as pl/sql
means you should be right at home with writing code in VBA that executes
sql.

Simply start a transaction such as

Dim strSql As String
Dim rst As DAO.Recordset

BeginTrans

' now, start your update routines....

CurrentDb.Execute "update someTable set City = 'Edmonton' where City
= 'ed'"

' if you wnat, put the sql in a string
strSql = "Update someTable set City = 'New York' where City =
'N.Y.'"
currentdb.Execute strSql

' and, you can throw in reocrdset code if you want. Lets now set the
area code for all
' those citys we just fixed

strSql = "select * from someTable where City = 'New York'"
Set rst = CurrentDb.OpenRecordset(strSql)
Do While rst.EOF = False
rst.Edit
rst!AreaCode = "212"
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

If MsgBox("do you want to committ/update all of the above udpates?",
vbYesNo) = vbYes Then
CommitTrans
Else
Rollback
End If

Note how in the above I used some reocrdsets, and SOME sql statements to
update data. So you do NOT have to always use rocrdsets.

Note how in the above the 2nd reocdset loop is DEPENDS ON the previous sql
of setting the city to new York. So, do note that the updates to your data
DOES occur WITHIN the transaction.

However, if you don't comment, then NONE of the updates that occurred in
code will appear. You can also nest transactions.

So, the flow, and wring of the code is going to be somewhat like the pl/sql
language, but, you have the full coding abilities of VB in this case....

Note that transactions such as above do NOT apply to the user interface
(forms) in MS-access, but only for your code. So, you can well take a
classic data processing routine, and for payroll, or other types of
"posting" routines, you can commit, or rool back ALL of the updates....if
you don't like the results...

So, you are not restricted to using recordsets, or sql statements, but can
use a mix of either..and the transaction will still withhold the updates
until you commit. (in fact, any, and all changes to data you make in *code*
will be withheld)..


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal





being trans


Are there any sample applications of the DB