Two dbs using one linked table

[JohnB]

Hi. Im using Access 2002 on XP, in a multi user networked environment.

I have a db which Im being asked to change for a slightly different use but
by the same group of people.

As I see it, I have two options. Either I could modify the existing db by
adding new forms reports etc to make it dual purpose, or I could export the
current db structure, minus data, to a new db and then modify the second db
accordingly. The problem with this second option is there would then be data
which is common to both dbs, held in just one table. A solution to this is
to remove the common table from the second db and then set up a link to the
original dbs table.

Are there any pitfalls in having one db sharing a table with another db?
Would the original db always have to be opened before the second one? Each
db would have a general database password but this may change at a later
date to a more extensive type of security - any problems likely in that
area? I really dont want to go down the Linking route only to find out later
that there are lots of problems with it.

Thanks for any advice. JohnB

 

[Tim Ferguson]

As I see it, I have two options. Either I could modify the existing db
by adding new forms reports etc to make it dual purpose, or I could
export the current db structure, minus data, to a new db and then
modify the second db accordingly. The problem with this second option
is there would then be data which is common to both dbs, held in just
one table. A solution to this is to remove the common table from the
second db and then set up a link to the original dbs table.

For me, this would be the clincher: if there is common data, then it's a
common database. It's not possible to enforce relational integrity across
two different mdb files, so you will in time find the MailingList users
happily deleting Customer records that still have outstanding Orders or
Invoices, etc.

Remember that there is no rule that you have to have only one front end.
It is very common to have different front end mdbs for the MailingList
people, the Finances, the Bookings and Management and so on, with
different sets of reports and forms in each.

one? Each db would have a general database password but this may
change at a later date to a more extensive type of security - any
problems likely in that area?

File-level passwords are probably not even worth the mental effort it
takes to think of them in the first place. Either take the trouble to set
up full Access user-level security, or just educate your users. MDB
passwords are really a false safeguard.

Hope that helps


Tim F

 

[JohnB]

Thanks for this Tim.

Looks like one big mdb is the way to go. Thats not too bad really. I use a
big Tab Control form as my main switchboard, so I think Ill just add one
more Tab which, when clicked, opens another TabControl form which will front
the new stuff. That way it will allow me to have good visual separation
between the two sets of forms and reports.

Thanks again, JohnB

 

[Tim Ferguson]

Looks like one big mdb is the way to go. Thats not too bad really.

Just to check: you _are_ separating the front end (client: forms and
reports only) from the back end (server: tables only) aren't you?

If you are just sharing a single mdb file between several users, then you
are going to run into file corruption and loss of data. Not to mention that
you have to take the whole thing off line in order to do any coding or
updating of the forms. Not to mention that it'll run like frozen custard
over a network.

Best wishes


Tim F