Separate database for tables?

In Access 2002 Bible, page 86, second paragraph, it says:

"It's also a good idea to create a separate datatbase for
just your tables. By separating your design objects
(queries, forms, reports, etc.) and the tables into two
different databases, it's easier to maintain your
application."

Why is this? Do you agree?

If the tables are in a separate database from the forms,
queries and reports can they still interact (input data
from forms into tables and pull info from tables into
query results and reports)? How would that work; how do I
do that? I'm using Access 2002.
Thanks.

Absolutely. We've been doing this since version 1 of Access, and would not
develop any other way.

Reasons in this article:
http://allenbrowne.com/ser-01.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.

"Holly Clifton" wrote in message
...
In Access 2002 Bible, page 86, second paragraph, it says:

"It's also a good idea to create a separate datatbase for
just your tables. By separating your design objects
(queries, forms, reports, etc.) and the tables into two
different databases, it's easier to maintain your
application."

Why is this? Do you agree?

If the tables are in a separate database from the forms,
queries and reports can they still interact (input data
from forms into tables and pull info from tables into
query results and reports)? How would that work; how do I
do that? I'm using Access 2002.
Thanks.

Holly,

If you run a single, monolithic database application (one where the tables
are in the same database as forms,queries, reports, etc.):

- You will need to have all your users exit/close the database anytime you
want to make a design change to a form, report, query or macro. Users will
not like this, nor will you. If you split the application and keep all of
your queries, forms, reports, macros and modules in their own MDB file, you
can make changes to your heart's content in your own copy of the front-end
MDB and deploy changes to users by simply having them copy the new MDB file
over their current copy.

- More important, if you are using an unsplit database where all users are
opening/running the same copy of the database, you are asking for and are
practically guaranteed to have database corruption problems!

Generally, the steps to set up a multi-user, split application are as
follows:

1. If you have not already done so, split the application into a Front-End
and a Back-End. The Database Splitter utility found at Tools|Database
Utilities|Database Splitter is one way to do this. The Front-End (FE) will
contain all Queries, Forms, Reports, Modules, Macros and local-use tables.
The Back-End (BE) will contain all tables to be shared among users.

2. The BE will reside in a folder on your server, where full permissions
for each user will be granted (Read, Write, Create, Delete, Execute).

3. In the FE, Link to the tables in the BE. To avoid problems with
changing drive mappings, link to the BE tables using the full UNC, starting
with My Network Places and drilling down through folders until the actual
database name of the BE is found.

4. An individual copy of each FE will reside on each user's computer. For
ease of distributing updates to FEs, see:
http://www.granite.ab.ca/access/autofe.htm which describes an AutoUpdater
for front-ends, developed by Access MVP Tony Toews.

5. Additionally, a copy of the most current FE in use by users should be
kept on the server. You will do your maintenance/update programming on your
own copy of the FE on your own computer. When updates are ready to be
distributed, you'll copy your modified FE to replace the one on the server.

Those are pretty much the high points.

--

Cheryl Fischer, MVP Microsoft Access



"Holly Clifton" wrote in message
...
In Access 2002 Bible, page 86, second paragraph, it says:

"It's also a good idea to create a separate datatbase for
just your tables. By separating your design objects
(queries, forms, reports, etc.) and the tables into two
different databases, it's easier to maintain your
application."

Why is this? Do you agree?

If the tables are in a separate database from the forms,
queries and reports can they still interact (input data
from forms into tables and pull info from tables into
query results and reports)? How would that work; how do I
do that? I'm using Access 2002.
Thanks.

Thanks so much. Your reply was certainly helpful.

-----Original Message-----
Holly,

If you run a single, monolithic database application (one
where the tables
are in the same database as forms,queries, reports, etc.):

- You will need to have all your users exit/close the
database anytime you
want to make a design change to a form, report, query or
macro. Users will
not like this, nor will you. If you split the
application and keep all of
your queries, forms, reports, macros and modules in their
own MDB file, you
can make changes to your heart's content in your own copy
of the front-end
MDB and deploy changes to users by simply having them
copy the new MDB file
over their current copy.

- More important, if you are using an unsplit database
where all users are
opening/running the same copy of the database, you are
asking for and are
practically guaranteed to have database corruption
problems!

Generally, the steps to set up a multi-user, split
application are as
follows:

1. If you have not already done so, split the
application into a Front-End
and a Back-End. The Database Splitter utility found at
Tools|Database
Utilities|Database Splitter is one way to do this. The
Front-End (FE) will
contain all Queries, Forms, Reports, Modules, Macros and
local-use tables.
The Back-End (BE) will contain all tables to be shared
among users.

2. The BE will reside in a folder on your server, where
full permissions
for each user will be granted (Read, Write, Create,
Delete, Execute).

3. In the FE, Link to the tables in the BE. To avoid
problems with
changing drive mappings, link to the BE tables using the
full UNC, starting
with My Network Places and drilling down through folders
until the actual
database name of the BE is found.

4. An individual copy of each FE will reside on each
user's computer. For
ease of distributing updates to FEs, see:
http://www.granite.ab.ca/access/autofe.htm which
describes an AutoUpdater
for front-ends, developed by Access MVP Tony Toews.

5. Additionally, a copy of the most current FE in use by
users should be
kept on the server. You will do your maintenance/update
programming on your
own copy of the FE on your own computer. When updates
are ready to be
distributed, you'll copy your modified FE to replace the
one on the server.

Those are pretty much the high points.

--

Cheryl Fischer, MVP Microsoft Access



"Holly Clifton"
wrote in message
...
In Access 2002 Bible, page 86, second paragraph, it
says:

"It's also a good idea to create a separate datatbase
for
just your tables. By separating your design objects
(queries, forms, reports, etc.) and the tables into two
different databases, it's easier to maintain your
application."

Why is this? Do you agree?

If the tables are in a separate database from the forms,
queries and reports can they still interact (input data
from forms into tables and pull info from tables into
query results and reports)? How would that work; how
do I
do that? I'm using Access 2002.
Thanks.


.