database management

[Guest]

Hello

My database will be comprised of modules, each independant of each other. I
wish to have a Master Switchboard that facilitates the selection of a certain
database, then opens only that module, then closes itself.
When the module is quitted, it then re-opens the Master swithcboard and
closes itself.

Is using the opencurrentdatabase function the way to go? I have concerns as
the help file indicated only examples of opening from other applications such
as Excel or VB. And if opencurrentdatabase is the correct option, how do you
use closecurrentdatabase to close the Master Switchboard?

Thank-you in advance.

 

[Larry Linson]

According to the Access 2002 Help, you can execute this from Microsoft
Access -- the text with the Example for OpenCurrentDatabase reads:

"The following example opens a Microsoft Access database from another
application through Automation and then opens a form in that database.
You can enter this code in a Visual Basic module in any application that can
act as a COM component. For example, you might run the following code from
Microsoft Excel, Microsoft Visual Basic, or Microsoft Access.

When the variable pointing to the Application object goes out of scope, the
instance of Microsoft Access that it represents closes as well. Therefore,
you should declare this variable at the module level."

My question would be "What do you expect to accomplish with this database
structure?" Microsoft Access databases can be rather large without affecting
stability or performance.

"Module" by the way, has a specific meaning in Microsoft Access -- "Modules
contain VBA declarations and procedures". I believe you are using it in a
more generic sense, but suggest that you avoid using Access reserved words
in their generic sense, to eliminate confusion on the part of those reading
your posts.

Larry Linson
Microsoft Access MVP

 

[Guest]

Ok, I have a Master Switchboard that has a combo box that selects a certain
equipment type. I wish to have a seperate database for each equipment type
(containing information unique to that type)

I have written the first database which contains approx. 6 forms, 1 subform,
6 queries and 5 or so tables. I have over 20 equipment types.
So if I don't split the "whole" thing into equipment type based databases,
the one database may become very large and difficult to maintain.

I also will be uploading each equipment database each time one is finished,
and I wish to split each so that it has a front and back end. If I don't do
it in individual databases, I will have to continuously split everytime a new
database is ready.

The overall "whole" database isn't big enough to warrant an Access Project
or SQL server, so I just wanted one Master database to have a switchboard
that simply 'navigates' between each individual equipment type database.

 

[Larry Linson]

Ok, I have a Master Switchboard that
has a combo box that selects a certain
equipment type. I wish to have a seperate
database for each equipment type
(containing information unique to that type)

I have written the first database which
contains approx. 6 forms, 1 subform,
6 queries and 5 or so tables. I have over
20 equipment types.

5 tables x 20 equipment types = 100 tables
6 queries x 20 equipment types = 120 queries
7 forms x 20 equipment types = 140 forms

So if I don't split the "whole" thing into
equipment type based databases,
the one database may become very large
and difficult to maintain.

This would give a total of 360 Access objects (even if none are common, but
I'd wager some are common across equipment types). I've worked on Access
clients that contained around a thousand objects, and they were easily
maintainable; others have reported using even larger numbers of objects.

I also will be uploading each equipment
database each time one is finished,
and I wish to split each so that it has a
front and back end. If I don't do
it in individual databases, I will have
to continuously split everytime a new
database is ready.

First, I don't understand what you mean by "uploading each equipment
database each time one is finished". I've not worked on any databases with a
concept of them being "finished" -- once developed, they were for ongoing
use.

Except for some small examples, every database I create is split between
front and back end. That does not dictate further splitting by equipment
type.

The overall "whole" database isn't big
enough to warrant an Access Project
or SQL server, so I just wanted one
Master database to have a switchboard
that simply 'navigates' between each
individual equipment type database.

In fact, just the thought of the added complexity of multiple databases by
equipment type and the maintainability thereof was what caused me to raise
the question. But, if that is the design you use, also take a look at
former-MVP Michael Kaplan's site for his TSISOON (Trigeminal Systems Inc.
Shut One, Open Next)... I can't remember if it is version-specific... the
site is http://www.trigeminal.com.

Larry Linson
Microsoft Access MVP