Referential Integrity On Linked Tables

[ribena1980]

Hi,



I have accounts Databases for each year dating back to 2007.

I am trying to uniform the data and had the idea that each of the database years would import a table of valid account sections and subsections from a master database.

These 2 tables of valid account sections and subsections would be on 1 database, where any new, amended or deleted item would cascade onto the database years.

For example, adding the section Car and Subsections Insurance, Tax, Petrol, Parking onto the Master Database meaning that 2007.mdb, 2008.mdb, 2009.mdb etc would be able to have items Car and its sub items selected.

The problem I have is enforcing the Referential Integrity of this. From doing some research it seems like this is not possible with Linked tables as it cannot guarantee that tables in the master database will always be available.

Although I understand the theory behind this , how do we get around this issue to enforce a situation like the one I have described above.

Thanks,
Mark

[ranman256]

you wouldnt have separated databases for each year.
ALL data would be in 1 database, for EVERY year. You wouldnt break it up.

[ssanfu]

The problem I have is enforcing the Referential Integrity of this. From doing some research it seems like this is not possible with Linked tables as it cannot guarantee that tables in the master database will always be available.

Although I understand the theory behind this , how do we get around this issue to enforce a situation like the one I have described above.

True, you won't be able to use the built in RI, but YOU could write (all of the) code to enforce RI between the different dBs. Lots of work!

I would rather have all of the data in one dB, if possible.

[ribena1980]

Hi Guys,

Thanks for the replies. Although having everything in the one database probably is the best practical solution, I really dont want to go down that route if I can avoid it.

Is there a mechanism where I can have the sections and descriptions on a main database and import the contents of those tables into each year.mdb file and enfor referential integrity from that point?

Thanks,
Mark

[ssanfu]

where I can have the sections and descriptions on a main database

Do you have tables named section and description? Section and Description are reserved words and shouldn't be used for object names.

Is there a mechanism where I can have the sections and descriptions on a main database and import the contents of those tables into each year.mdb file and enfor referential integrity from that point?

If sections and descriptions are tables, then yes, I think you should be able to import the tables into each year.mdb file and enforce RI.