Core database uses external data?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not sure this the section to post my question.

I have a database that is used for 3 different groups. The database is
exactly the same for all 3 -- same tables, forms, queries, reports. The
problem is when I make improvements, I have to do it to all 3 databases.
Each database has about 6 tables.

Is it possible to use a "core" database that contains no data but is linked
to tables in an external database, and when the "core" database is opened the
use has the option of selecting the group he wants to use.

Any assistance in pointing me in the right direction would be appreciated.

Kelvin
 
To do this correctly, will take some work. Based on your post I assume your
database is not split, that each group has a database where all the tables
and other objects reside. If you do some research, you will find that this
model is not recommended, particularly if you have multiple people using the
data database.

The first step is to split the databases. (Tools->Database
Utilities->Database Splitter). Now you will have a total of six mdb files,
three with the original name and three with the original name appended with
_be. Those with the original name will contain everything execpt your
tables. These are the Front End (fe). The three with the _be added contain
your data and are the Back End (be).

Next, put the be on a shared drive all the users have rights to. Then, put
a copy of the fe on each user's computer. You will have to use the Linked
Table manager to link to the tables in the be. Now, when you have to make
changes, you will only need to make the changes one time, deliver it to the
users, and relink to their be.

There is only furhter step you should seriously consider. If all three be
files are indentical in structure, but contain different data depending on
the group, then it would be advisable to add a field to each table that would
identify the group. Then it would be necessary to make changes to your
queries, forms, and reports to filter the data be group so that the see only
their data. The advantage here, is that now you only have to make changes
once for the be should they be required to modify, add, or delete table
structures, indexes, relationships, etc.

The last step required a little work, so you may want to phase it in later.
You could start with doing everything except combining the back ends.
 
Roger,

I got the link table database. Pretty neat. My problem is the database has
10 tables. Is that possible? My database has a core, or key table, called
borrower, all the other tables are what I call sub-tables or sub-sub-tables.
That means the data in the "sub-table" relates to the borrower table, and you
cannot have data in the sub-table with a record in the borrower table. The
goal is use one database and be able to let the user select the group of
tables to use. That way, I only have to maintain one database. Is that
possible?
 
Sorry for the delay. I hope it's still pertinent.

Sure it's possible. The LinkTables.mdb sample just shows one table as an
example. There's another sample called "RelinkOnOpen.mdb" which shows how
to re-link all of the tables in a database. This sample doesn't let you
select the database, but if you combine this with the LinkTable sample, you
should have something to go on.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Klatuu,

I did the database splitter thing. Works pretty good. Thanks. I did not
have to setup links though, perhaps because the FE and BE are in the same
folder?

As for my initial question about "selecting" what data tables to work with,
if I split the other 3 databases, give them the same BE filename (I'd have to
store them in seperate folders), then could I simply copy the BE file I want
to use into the folder where the FE file is looking for the BE file?

I Roger Carlson's 2 databases and will take a look at that also.

Kelvin
 
Back
Top