References to CurrentDb? Persistent?

[Tony M.]

I'm trying to execute an append query. I have a case that works, and
one that returns an object not set error.

THIS WORKS:

Private Sub cmdArchiveRecs_Click()
BeginTrans
CurrentDb.Execute "qapp_RecsNotYetArchived", dbFailOnError
CommitTrans
End Sub

THIS DOESN'T WORK. It returns OBJECT NOT SET on dbLocal.Execute():

Dim dbLocal As DAO.Database

Private Sub Form_Load()
Set dbLocal = CurrentDb
End Sub

Private Sub Form_Unload(Cancel As Integer)
dbLocal.Close
Set dbLocal = Nothing
End Sub

Private Sub cmdArchiveRecs_Click()
BeginTrans
dbLocal.Execute "qapp_RecsNotYetArchived", dbFailOnError
CommitTrans
End Sub

Why isn't "dbLocal" set for the life of the form? Of what value is
setting a reference vice always using CurrentDb?

Thanks,

-Tony M.

[w_a_n_n_a_l_l_ -]

I don't remember the reason anymore, but I do remember that when you use
CurrentDB you're getting a transient refefence to the DB. The way to cope
is to declare a variable of type dao.database. Then:

set db = currentdb
do stuff
do stuff
do stuff
set db = nothing

Maintaining a reference to the same instance (?) of the db pointer buys you
some things, such as being able to do a db.execute and followup with a test
on db.RecordsAffected.

(If your data is in SQL Server: Quite often you may prefer to define an
adodb.connection object and an adodb.command object, get your connection,
construct your command, and execute the command object, which moves the
processing out of Access and up to the server.)

[Allen Browne]

The reason is that when VBA hits a CurrentDb, it stops what it is doing, and
flushes all the collections of all the objects in the DAO tree. It then
creates a new object, and points it to the default database, which is
typically dbEngine.Workspaces(0).Database(0). Since all collections are
flushed and a new object is created, anything that depended on the existence
of the previous object is no available.

The original question was about the traps when handling transactions. There
is an example and discussion of the pitfalls in this article:
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.

<w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t> wrote in message
news:df*******************@newssvr27.news.prodigy. net...

I don't remember the reason anymore, but I do remember that when you use
CurrentDB you're getting a transient refefence to the DB. The way to cope
is to declare a variable of type dao.database. Then:

set db = currentdb
do stuff
do stuff
do stuff
set db = nothing

Maintaining a reference to the same instance (?) of the db pointer buys
you
some things, such as being able to do a db.execute and followup with a
test
on db.RecordsAffected.

(If your data is in SQL Server: Quite often you may prefer to define an
adodb.connection object and an adodb.command object, get your connection,
construct your command, and execute the command object, which moves the
processing out of Access and up to the server.)

[David W. Fenton]

"Tony M." <an*************@navy.mil> wrote in
news:11**********************@j55g2000cwa.googlegr oups.com:

I'm trying to execute an append query. I have a case that works,
and one that returns an object not set error.

THIS WORKS:

Private Sub cmdArchiveRecs_Click()
BeginTrans
CurrentDb.Execute "qapp_RecsNotYetArchived", dbFailOnError
CommitTrans
End Sub

THIS DOESN'T WORK. It returns OBJECT NOT SET on dbLocal.Execute():

Dim dbLocal As DAO.Database

Private Sub Form_Load()
Set dbLocal = CurrentDb
End Sub

Private Sub Form_Unload(Cancel As Integer)
dbLocal.Close
Set dbLocal = Nothing
End Sub

Private Sub cmdArchiveRecs_Click()
BeginTrans
dbLocal.Execute "qapp_RecsNotYetArchived", dbFailOnError
CommitTrans
End Sub

Why isn't "dbLocal" set for the life of the form? Of what value is
setting a reference vice always using CurrentDb?

A truly persistent cached reference is much more useful. I've often
posted my code for a public function called dbLocal() that can be
used just like a public DAO.Database variable, and the code for that
is posted below after my sig. A discussion of some of the issues of
how it works is found in the thread beginning with this post:

http://groups.google.com/group/comp..../msg/9520d63d8
9decbb3

Paste the code into a public module and then you don't have to
declare a database variable anywhere in your app, or maintain a
public variable -- just use dbLocal anywhere you'd have used a
database variable initialized with CurrentDB() and forget about it.

The one thing you might want to do (though it's not strictly
necessary) is to de-initialize the variable when closing your app.
This is done by calling dbLocal with the optional argument set to
False.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function dbLocal(Optional ysnInitialize As Boolean = True) _
As DAO.Database
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being closed
' would (3420) would then be jumping back into the middle of an
' If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String

If Not ysnInitialize Then GoTo closeDB

retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing, test
' if it's Open
strTest = dbCurrent.Name

exitRoutine:
Set dbLocal = dbCurrent
Exit Function

closeDB:
If Not (dbCurrent Is Nothing) Then
'dbCurrent.close
Set dbCurrent = Nothing
End If
GoTo exitRoutine

errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If ysnInitialize Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in Public Function dbLocal()"
Resume exitRoutine
End Select
End Function

[Tony M.]

No one pointed out that my problem seems to be the opposite of what's
being recommended. What should work isn't, and what does work is not
the recommended approach, right? Allen, are you saying that it has
something to do with the transaction I wrapped around the execute
statement? Your link below does not resolve for me.

Thanks,

-Tony M.

Allen Browne wrote:

The reason is that when VBA hits a CurrentDb, it stops what it is doing, and
flushes all the collections of all the objects in the DAO tree. It then
creates a new object, and points it to the default database, which is
typically dbEngine.Workspaces(0).Database(0). Since all collections are
flushed and a new object is created, anything that depended on the existence
of the previous object is no available.

The original question was about the traps when handling transactions. There
is an example and discussion of the pitfalls in this article:
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.

<w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t> wrote in message
news:df*******************@newssvr27.news.prodigy. net...

I don't remember the reason anymore, but I do remember that when you use
CurrentDB you're getting a transient refefence to the DB. The way to cope
is to declare a variable of type dao.database. Then:

set db = currentdb
do stuff
do stuff
do stuff
set db = nothing

Maintaining a reference to the same instance (?) of the db pointer buys
you
some things, such as being able to do a db.execute and followup with a
test
on db.RecordsAffected.

(If your data is in SQL Server: Quite often you may prefer to define an
adodb.connection object and an adodb.command object, get your connection,
construct your command, and execute the command object, which moves the
processing out of Access and up to the server.)

[Allen Browne]

Okay, you want us to evaluate what's happening with your code.

1. The first line of your cmdArchiveRecs is an unqualified BeginTrans.
Are you beginning a transaction on the dbEngine object?
On the default workspace object?
Or do you have ADO as a higher priority reference, so this is actualy an ADO
transaction?

2. Your form_Unload *closes* the local database, which is always open. (You
did not open this; you merely assigned a variable to it.) Access will
automatically open it again, but there can be side effects for this kind of
thing.

3. I am not clear what's going on with your unqualified BeginTrans, and lack
of other info (such as whether you may have Reset the project at some point
since the form opened), but the error message suggests that your dbLocal
variable has been destroyed at some point. The default database is dependent
on dbEngine(0) which in turn is dependent on dbEngine, so the presence of a
transaction on either of those will affect the object that depended on them.

--
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.

"Tony M." <an*************@navy.mil> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

No one pointed out that my problem seems to be the opposite of what's
being recommended. What should work isn't, and what does work is not
the recommended approach, right? Allen, are you saying that it has
something to do with the transaction I wrapped around the execute
statement? Your link below does not resolve for me.

Thanks,

-Tony M.

Allen Browne wrote:

The reason is that when VBA hits a CurrentDb, it stops what it is doing,
and
flushes all the collections of all the objects in the DAO tree. It then
creates a new object, and points it to the default database, which is
typically dbEngine.Workspaces(0).Database(0). Since all collections are
flushed and a new object is created, anything that depended on the
existence
of the previous object is no available.

The original question was about the traps when handling transactions.
There
is an example and discussion of the pitfalls in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

<w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t> wrote in message
news:df*******************@newssvr27.news.prodigy. net...

>I don't remember the reason anymore, but I do remember that when you use
> CurrentDB you're getting a transient refefence to the DB. The way to
> cope
> is to declare a variable of type dao.database. Then:
>
> set db = currentdb
> do stuff
> do stuff
> do stuff
> set db = nothing
>
> Maintaining a reference to the same instance (?) of the db pointer buys
> you
> some things, such as being able to do a db.execute and followup with a
> test
> on db.RecordsAffected.
>
> (If your data is in SQL Server: Quite often you may prefer to define
> an
> adodb.connection object and an adodb.command object, get your
> connection,
> construct your command, and execute the command object, which moves the
> processing out of Access and up to the server.)

[Rick Wannall]

Been a long time since I've done transactions in Access, but the example in
help these days (which matches my memory of how I was doing it), shows use
of BeginTrans et al off the Workspace object.

The general approach is this:

Public Function testws()

Dim ws As Workspace
Set ws = DBEngine.Workspaces(0)

On Error GoTo Generic_Error
'prep work here

ws.BeginTrans
On Error GoTo RollMeBack

'insert
'update
'delete
'whatever

'finished, commit
ws.CommitTrans
Exit_testws:
Set ws = Nothing

Generic_Error:
MsgBox err.Description
Resume Exit_testws

RollMeBack:
MsgBox err.Description, vbCritical, "Rolling Back After Error"
ws.Rollback
Resume Exit_testws

End Function
You can get much more detailed and even nest transactions several layers
deep, though I can't remember why on earth I thought I needed to do that any
more.

Does this help?

[Rick Wannall]

Add "Exit Function" after "Set ws = nothing