Access and transaction processing...

[Brad Pears]

Can Access 2000 even support transaction processing? I have an area in an
existing application that would be ripe for a begin/end and rollback if user
cancels the process... I am currently just using DAO in this app. It is
scheduled for redevelopment in VB .net 2005 and using SQL server 2000
instead - which obviously supports transaction processsing. Just do not know
whow long this app will actually live in it's current state - hence thinking
about adding some Xaction processing to existing app...

Thanks,

Brad

 

[Allen Browne]

Yes. You can RollBack or CommitTrans in DAO.

There are several traps. For a discussion and example, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[Brad Pears]

Great thanks for that!!

Brad

Yes. You can RollBack or CommitTrans in DAO.

There are several traps. For a discussion and example, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[Brad Pears]

Ok, a few more questions...

In my situation I have a procedure that calls sub procedures that update,
insert, delete rows etc.. etc..

If I begin a transaction in the main calling procedure, am I able to use the
"currentdb.execute strSQL dbfailonerror" code in all called sub procedures
and have those sub procedures be WITHIN the same transaction I started from
the main calling procedure? - so that if any called sub fails, then when
I get back to the main calling procedure and issue the rollback - will it
rollback all the updates etc.. that have happened in the various called
subs??

Here is some sample code how I would handle it - assuming what I want to do
above can be done...

Also, instead of dimming a db object, can I just use the
"currentdb.exeecute" statement?? (as shown in the 'called' subroutine below)


Example...

Sub MainSub(strQuoteNo as string)
'**** Main sub
dim ws as dao.workspace
dim db as dao.database
dim strSQL as string
dim bTransAct as boolean
dim bSubFailed as sboolean

on error goto SQL_ErrorMsg

' Set subroutine failed flag to false
bSubFailed = false

' start a trans
set ws = dbengine(0)
ws.begintrans
bTransAct = true
set db = ws(0)
strSQL = "delete from Quotes where QuoteNo=" & strQuoteNo
db.execute strSQL dbFailOnError

' Call another subroutine to insert some rows
call InsertSub

' Check to see if the sub failed - if so, roll back...
if bSubFailed then
goto SQL_Error
else
' Commit it!
ws.commitTrans
bTransAct = false
endif

SQL_Error:
on error resume next
set db = nothing
if bTransAct then
ws.rollback
endif
set ws = nothing
exit sub

SQL_ErrorMsg:
msgbox "Error: " & err.number
resume SQL_Error

end sub

***** Called Sub routine

Sub InsertSub
dim strSQL as string

on error goto SQL_Error

strSQL = "insert into QUOTES fields (fld1, fld2, fld3) values (val1, val2,
val3)"
currentdb.execute strSQL dbFailOnError
exit sub

SQL_Error:
' Set flag to indicate to calling sub that this sub failed
bSubFailed = true

end sub

 

[Allen Browne]

Sure you can call other event procedures while you are in a transaction.

But instead of using CurrentDb in the child procedure (this creates another
instance), pass the Database variable to the child.

Example:

Function Main()
dim ws as dao.workspace
dim db as dao.database
dim strMsg As String

set ws = dbengine(0)
ws.begintrans
set db = ws(0)

strMsg = vbNullString
If WorkedOk(db, strMsg) Then
ws.CommitTrans
Else
ws.RollBack
MsgBox strMsg, vbExclamation, "No go"
End If
End Function

Function WorkedOk(db As DAO.Database, strMsg As String) As Boolean
On Error Goto Err_Handler

db.Execute strSql, dbFailOnError
WorkedOk = True

ExitHandler:
Exit Function

Err_Handler:
strMsg = "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function

 

[david epsom dot com dot au]

Note that DAO transaction handling does not play well
with SQL Server anymore. Any kind of complex transaction
will create multiple connections, which will block each
other. The transactions are asynchronous when run against
ODBC connections (transactions are not committed when you
use committrans), so you won't even see the blocking until
it times out.

(david)

 

[david epsom dot com dot au]

If you use Currentdb.execute, you won't be able to use
..recordsaffected as shown in the example. CurrentDB creates
a new instance for each line, so the RecordsAffected
information is lost.

(david)

 

[Brad Pears]

OK, so I am using "currentdb.execute (strSQL)" all over the place in my app.
Are you saying that I should not be using that at all?? Will I have severe
memory issues by doing this as opposed to passing a "db" object along
through each procedure/function in order to use the db.execute code? I am
not using recordsaffected anywhere so no probs there. However, this app is
being run on a terminal server and multiple users will be running it...

Should I publically declare a dao.db object once so I can use it wherever,
without having to modify all my procedures and functions to pass it around
as a parameter?

Thanks, Brad

 

[Guest]

No, I'm just saying that you can't use .RecordsAffected if all
you use is CurrentDB.execute.

No, you won't have memory issues if you use a temporary
object instance every time you do database access.

No, you don't need to pass a db variable if you are using
the default workspace, to which the current database is
a member.

On the other hand, if you want to move code into a library
database, you will sometimes wish to pass a db instance
to the library.

If you know that you are working inside a transaction with
several database actions, you may wish to speed up the
process by not refreshing the ws.databases collection on
each line.

I very strongly recommend that you not use module-level
or form-level database instances. (Only global or local)

Apart from that, how you declare and use your database
variables is not something that I regard as critical.

(david)

 

[Allen Browne]

David is correct, that each time you use CurrentDb, Access creates a new
object and points it at the currently open database which is typically
dbEngine.Workspaces(0).Databases(0). Because it is a new instance, you can
run into difficulties with any code where you are trying to examine
something that depends on the previous instances still existing, which
includes the RecordsAffected property or examining objects that depend on
the db.

But creating a public variable and holding it open is not a good solution.
Particularly while debugging, it makes it hard to know whether the variable
is set, so every piece of code needs to either test it and reinitialize if
necessary or use error recovery to reinitialize if it it's not set. And even
after you have done that, you still don't have the certainty that you are
checking the correct value or a value left over from a previous run (e.g.
with RecordsAffected.)

My personal approach is to create a Database variable in each main routine
that needs one. If that routine calls subs that need it (good programming
practice to modularize), pass the db to the subs. This provides complete
independence between independent routines, correct dependence between
dependent routines, and no issues with knowing that it is correctly
initialized.

HTH.

 

[Brad Pears]

Ok... hmmm that would exxplain why I see db update/insert delays sometimes
then I suspeect - becasue it is acting on a differnt instance of the DB!

1) So, if using "currentdb" creates another instance, is Access also
destroying each additional instance that is being created at some point as
well or is the instance simply kept around and not being destroyed at all???
That would be huge memory issues I would think. Is there a command you can
issue to have access destroy instances of objects when you are done with
them...

2) I am thinking that if I create a global "MyDB" DAO database object once,
set it, and then simply replace in my code all "currentdb.execute" with
"mydb.execute"... that should work just fine... I can't see how as long as I
am creating the database object and setting it right up front it would be a
problem... I use other public variables in this manner and have no
issues... Comments?

Thanks, Brad

 

[Allen Browne]

Strictly, Currentdb creates another object (not instance), and points that
object to the default database of the default workspace of the dbEngine
object, after clearing all the collections of that object.

In a perfect world, Access would release the memory assigned to the
Currentdb object as soon as it goes out of scope. However, if you create a
loop that refers to Currentdb a few hundred times, they tell me that you
will run out of databases because it is not being released. So, you need to
explicitly clear it, e.g.:
Dim db As DAO.Database
Set db = CurrentDb()
'do something useful
Set db = Nothing

The only public variables I use any more are those that are used for passing
an object in an event procedure that does not expose the object. Typically,
the lifetime I rely on it for would be measured in milliseconds. I also find
that public variables complicate debugging: e.g. (a) reset and use lose them
all, or (b) is this bug caused by some interaction with the public
variables?

As an example for this particular case, it is not unusual to see bad code
that executes a transaction on the default workspace and then cleans up by
closing it, e.g.:
Dim ws As DAO.Workspace
Set ws = dbEngine(0)
ws.BeginTrans
'do some stuff
ws.CommitTrans
ws.Close
That code is faulty because you did not open ws, and you should not close
it. But Access is too kind and merely opens the default workspace again
without telling you. But at this point, anything that depended on the
default workspace (and that included your public Database variable) has been
destroyed.

My suggestion would be to follow good programming principles: scope your
variables as narrowly as possible, for just the lifetime of the procedure
where they are needed. Public variables are for people who don't mind
writing buggy code and spending their life trying to find the problems.

 

[Brad Pears]

Ok, I hear what you are saying ...

Specifically in your transaction code - that is close to the way I have mine
written as well... But never is there an actual "ws.close" issued.. I am
issuing a "set ws=nothing" upon exiting the sub that does the
commit/rollback etc... ...(that is the way it was written in the document
you had a link too...)

Brad

 

[Sam Hobbs]

In you sample code that uses DAO.Workspace it is unfortunate that VBA does
things implicity such as what you describe without explicit code. VBA for
Access is trying to make things easier but there are many situations such as
what your sample shows that can make things very difficult.

C++ programmers use globals (public variables), but well-written C++
programs use globals only when there is a valid reason for doing so.
Certainly it is good programming to avoid globals when possible but there
are situations when they are appropriate. If globals in VBA for Access is a
problem then it is likely a problem with Access.