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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brad Pears" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

Great thanks for that!!

Brad
"Allen Browne" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Brad Pears" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Brad Pears" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brad Pears" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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...

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)




"Brad Pears" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Brad Pears" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>
>>
>
>

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

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>> --
>>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>>> Tips for Access users - http://allenbrowne.com/tips.html
>>> Reply to group, rather than allenbrowne at mvps dot org.
>>>
>>> "Brad Pears" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>
>>>
>>
>>
>
>

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)


"Brad Pears" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >>> 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
> >>>
> >>> --
> >>> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >>> Tips for Access users - http://allenbrowne.com/tips.html
> >>> Reply to group, rather than allenbrowne at mvps dot org.
> >>>
> >>> "Brad Pears" <(E-Mail Removed)> wrote in message
> >>> news:(E-Mail Removed)...
> >>>> 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
> >>>
> >>>
> >>
> >>
> >
> >
>
>

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brad Pears" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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...