Scope of Database Definition

[cmdex_VABCH via AccessMonster.com]

I have an older database I am now responsible for. It appears that the
original programmer set a global database definition at startup and wanted to
use it through out the program when ever he needed it. Well the database has
been worked and reworked so many times that the single database definition
just isn't used anymore except on occassion in the original code.

Example:
~At startup in called Module M:
dim GblDb as Database
~module M, function F:
set GblDb = CurrentDb()
~form X:
set y = GblDb.OpenRecordset(.....
~module Z:
set b = GblDb.OpenRecorset(......
set GblDb = Nothing
~form A:
set c = GblDb.OpenRecorset(....

My question is, does a single definition used through out the program have
any significant advantages, other than not having to define a db definition
for each function? When does the global definition lose scope?

I should mention that the database is a front end, with an Oracle back end,
and resides on a server with as many as 50 concurrent users.

Some insight would be helpful. Thank you.

 

[Allen Browne]

In the early days of Access, some developers used globals because:
a) It saved them having the declare the variable repeatedly in many
functions.

b) They perceived a performance advantage if the variable is already set.

c) It used less resources to keep reusing the same database variable.

IMHO, these advantages are outweighed by other considerations, such as:
a) The variable gets destroyed when you Reset during debugging.
So you must test it and reinitialize when needed.

b) The maintenance nightmare of a global, where any piece of code could
point it to the wrong thing (especially as the database grows, or with
multiple developers.)

c) Scope confusion, since VBA also permits a local variable to have the same
name.

d) The 'performance' and 'resource' issues are bogus, since:
- Machines are faster and have more RAM.
- You can use dbEngine(0)(0) to point to an existing object if you care.
- You can pass a db object from one routine to another where it matters.

Hopefully each of your users has their own distinct copy of the front end
(not all 50 people using the same MDB file.)

 

[cmdex_VABCH via AccessMonster.com]

Mr. Browne,
Thank you for the insight. I have been to your site many times and
appreciate all the help you have proveded through the years.

And no, I have seen as many as 15 concurrent users in the same database (MDE)
on the server at the same time. It's the way this company solved the
distribution to each user issue. Trust me, I am working to correct that as
soon as possible.

Thank you again!