Multiple BackEnd Files - Keeping relationships

[markmarko]

I'm considering breaking our backend file into multiple files. The only way
I've read to do so will break any relationship between files, which would be
a chore to relink. That technique involved creating multiple be files, then
importing the appropriate tables into each from the un-split original file,
then delete the original tables from the un-split original file.

2 questions:
1) What's your opinions on the benefits of multiple-file backend vs
single-file backend? (I have at least 1 set of tables that need to be
separate for privacy concerns)
b) What technique can be used to avoid having to relink relationships? (I'm
thinking of starting with an already split database, copying it into multiple
backends, deleting the appropriate tables from each, then changing the
Table-Properties-Description for each table. Think that'll work?)

 

[Douglas J. Steele]

Relationships can only be between tables in the same MDB (or ACCDB) file.

You cannot create relationships between tables in different databases.

 

[markmarko]

Are you sure? I just tested linking one table to another file, and was able
to relink the relationship, though I was not able to enforce referential
integrity.

 

[Douglas J. Steele]

What's the point of having a relationship if you can't enforce referential
integrity?

It might save you a second or two when creating new queries (you won't have
to connect the tables, which specifies the ON clause for your joins), but
other than that, relationships between databases don't do anything for you.

 

[markmarko]

Well, I don't know! I wasn't saying I didn't care about ref integrity... I
was saying I couldn't yet enforce it in this instance.

Can you offer advice on how I would use your suggestion? Here's what I have...

A table, tblContractors, with all our contractors' names and relevant data
(approx 100 contractors currently with regular additions) . We also have
another table, tblContractorAcctg, which is one-to-one with tblContractors
which contains data about our contractors that is relelvent to accounting,
and therefore not to be accessible by anyone else in the company. My hope was
to have that private data in a separate file, which we could limit access to
using Windows permissions.

How can I link tblContractorAcctg to tblContractors?

 

[Douglas J. Steele]

If you've got multiple databases, you cannot enforce referential integrity.

Stop and think about it for a second. What stops a user from only working
with database1, or only with database2? If that happens, how would database1
even know about the existence of database2?

If you're using Access 2003 or earlier, you could use Access user-level
security to limit who can see what data. Unfortunately, ULS has been removed
in Access 2007.

 

[markmarko]

Well, as I understood it, I could put the acctg table(s) on the local
accounting computer (we only have 1 accounting person). We could then use
Windows permission to allow only that user to access that table. The rest of
the tables would reside on a network drive, with permissions so that all
users can access them. Therefore, only our accounting person could use both.

I've read that this is possible (Missing Manuals: Access 2007 by O-Reilly
publishing). Are they mistaken?

And yep, we're using 2007

btw- I hope I didn't offend with the "Are you sure" a few posts back. I
don't doubt your expertise!

 

[Douglas J. Steele]

You can do that, and it'll work as desired, but you will not be able to
enforce referential integity between the tables.

 

[markmarko]

ok, thanks for the advice!