Performance Question (Persistent Connection)

[bwion]

I have a quick question about some of the code that I have seen for
establishing a persistent connection to the backend of an access database to
increase performance. Here are the links that I am specifically referirng to:

http://www.granite.ab.ca/access/performanceldblocking.htm

http://support.microsoft.com/kb/303528/EN-US/#15

If I do set it up this way (by declaring a static DAO database object with
the backend) do I have to declare DAO database objects in other portions of
the program. For example, if I have a command button that uses DAO to add
data to a table to I still have to include the:

Dim dbs As DAO.database

Set dbs = CurrentDb()

~~~~Code~~~~~

Set dbs = Nothing

Isn't that reopening a connection with the backend or is that establishing a
connect with the front end? I just want to know if by adding this persistent
connection code and then using the above code in my procedures if I am being
counterproductive (establishing a connection twice and also defeating the
purpose of the persistent connection). Thanks for any tips.

~Ben Wion

 

[BruceM]

If there is a form that is always open (a switchboard form, for instance) I
just bind that to a small table, and include a hidden control to a field
from that table. If there is no always-open form I create a bound hidden
form that opens at startup and closes on exit.

I you have a global variable to declare the DAO.Database I don't see that it
is necessary to declare it again, but there are a couple of issues with
this. One is that, as I understand, a global variable will be cleared if
there is an unhandled error. Another is that if two variables have the same
name the "most local" one will take precedence. If you clear the
DAO.Database variable after updating a table the global variable of the same
name will be cleared too. I'm not quite sure what will happen if the
DAO.Database variables have different names, but I expect in that case that
the recordset connection in the hidden form will continue uninterrupted. If
I was going to do it that way I would probably declare a global Database
variable in the "constant connection" form's code module (rather than in a
standard module), and another local variable as needed for updating
recordsets.

I have to admit I may not have the fullest information on the topic of
global variables, but I have spent quite a bit of time studying it recently,
and I believe my points are valid if not necessarily complete. For myself I
would not use global variables when there is an option available (in this
case the bound form). However, I do not know if there are performance
considerations that may make one choice preferable to another.

 

[Ray C]

Ben, you do not need to declare a variable in order to use CurrentDb.

Simply use it directly, as in:

strSQL = "SELECT * FROM MyTable"
CurrentDb.OpenRecordset(strSQL)

Secondly when you use CurrendDb, you are not creating a connection. It's a
reserved word in Access that points to the current database file.

Ray

 

[bwion]

Hey All,

Thanks for the tips! Now its time for find and replace. Someone needs to
write a book of just access programming tips and tricks. I have a few access
books but most of them don't approach it in a "real-world" problem-solution
way. But I guess that is what forums are for. Thanks again guys.

~Ben Wion

 

[Ray C]

Hi Bruce,

You're absolutely right, global variables should be avoided in Access.
Unhandled errors will clear their values and if the user continues to use the
application after an unhandled error, the database will run in an
unpredictable state".

As for a global database variable, you don't need to create one, it already
exists, it's called CurrendDb, and it doesn't lose it's reference during an
unhandled error.

Database variables should be used only if you need to reference an external
database,

Ray

 

[david]

strSQL = "SELECT * FROM MyTable"

CurrentDb.OpenRecordset(strSQL)

set rs=CurrentDb.OpenRecordset(strSQL)
set fld = rs.fields(1)

And that works because rs maintains a reference to CurrentDb.

Tabledefs and Querydefs don't, so this doesn't work:

set tdf = CurrentDB.Tabledefs(1)
set fld = tdf.fields(1)

set qdf=CurrentDB.Querydefs(1)
set fld = qdf.fields(1).

Instead, you have to use:

set db = CurrentDB

set tdf = db.Tabledefs(1)
set fld = tdf.fields(1)

set qdf=db.Querydefs(1)
set fld = qdf.fields(1).


You can use

CurrentDB.execute(strSQL_1)
CurrentDB.execute(strSQL_2)
CurrentDB.execute(strSQL_3)

but that has two disadvantages: it's slower (because CurrentDB refreshes
the Application.dbEngine.databases collection each time), and you loose
the 'RecordsAffected' property each time. Better to do it like this:

Set db = CurrentDb

db.execute(strSQL_1)
db.execute(strSQL_2)
db.execute(strSQL_3)

Msgbox(db.RecordsAffected & " records updated")

(david)

 

[BruceM]

I see what you mean about there being no need for a database variable in
this case. As for you assertion that database variables should be used only
for referencing an external database, using a variable within a procedure is
a different thing than using a global variable. I do not see a problem with
declaring a database variable in that case, although I acknowledge that my
information on the topic is limitied, and there may be angles of which I am
unaware.

Also, after declaring a DAO.Database variable there are other options for it
than CurrentDB (DBEngine is one I have used, although I have to admit I do
not fully understand it). That is just an observation. I'm not sure how
that fits in with what you said.

 

[BruceM]

OK, I'm trying to learn more about this, and I'm not following you. I have
been thinking that CurrentDB can be used in place of a database variable set
to CurrentDB, but you seem to be saying that is not so. How about something
like this:
set fld = CurrentDB.Tabledefs(1).fields(1)

I'm not saying that is a preferred way or anything like that. Again, I'm
just trying to get a handle on why:
set fld = tdf.fields(1)
works only if there is a database variable in place of CurrentDB in the
preceding expression.

 

[Clif McIrvin]

I see what you mean about there being no need for a database variable
in this case. As for you assertion that database variables should be
used only for referencing an external database, using a variable within
a procedure is a different thing than using a global variable. I do
not see a problem with declaring a database variable in that case,
although I acknowledge that my information on the topic is limitied,
and there may be angles of which I am unaware.

Also, after declaring a DAO.Database variable there are other options
for it than CurrentDB (DBEngine is one I have used, although I have to
admit I do not fully understand it). That is just an observation.
I'm not sure how that fits in with what you said.

Another technique to consider is the with / end with construct instead
of declaring (instantiating) another database variable. It's already
there, you can do anything with it directly you can do with another
instance of it, and there is no need to set the variable to nothing
after you are done with it. With takes care of all that housekeeping for
you intrinsically.

 

[BruceM]

OK, thanks for the information. One more thing to keep in mind.

 

[david]

set fld = CurrentDB.Tabledefs(1).fields(1)

Don't think it will work for anything useful. fld won't be
valid on the next line, because the db object it connected
to is gone (even though the physical db is still open).

I never worked out an exact theory for what is going on,
but I got into the habit of always using
set db = codedb
CodeDB because we used loaded libraries, with no code
in Currentdb. db because we did a variety of complex things,
and even if I started out with a single line
CodeDb.Execute(...)
I often had to change it to a db variable later.

It depends on what you are doing. The reason a recordset
object maintains a reference to it's parent db object is just
so that you can do stuff like
set rs = CurrentDB.OpenRecordset(...)
without having to worry about making a dummy reference
to the db object. If you start doing stuff that is reasonably
complex, or with multiple references to CurrentDB, it makes
sense to adopt a more complex approach.

(david)

(david)

 

[david]

application.dbengine.databases(0)

normally returns CurrentDB. It is faster, because it does not refresh
the databases collection before returning. However, it is not guaranteed
to return CurrentDB (in some obscure cases there may be a different
database at that position in the collection), so it is rarely use now:
computers
are faster than they used to be.

For the same reason, global database variables are less often used now:
I timed it for my application a couple of years ago, and even then it wasn't
worth the effort (I did use other much better optimisations).

Form-level database variables are an even worse idea. In rare cases
form-level variables are required for advanced exception handling,
rollback, or data entry handling, but they are always a source of
possible error and should be avoided. If you have a simple system
you don't need the tiny performance boost, and if you have a complex
system you need to avoid the extra complexity.

I encourage you to use local variables for everything, and database
tables for everything else.

Static local variable are sometimes used for speed, but I go all the way
and cache the data, not the database variable.

(david)

 

[BruceM]

I tend to use variables within procedures, particularly when the variable is
used several times. It sounds like the reasons for doing so may have to do
with more than convenience, although I can't say I quite follow what is
going on. It does suggest an avenue of exploration when something isn't
working as expected.
Thanks for the reply.