linking data

[grahto]

Thanks in advance for anyone who can provide me with some help.

I am completely stumped. I haven't used Access to design anything more than a simple database with separate tables in over a year. I have been sitting here for hours trying to link the data between several tables on 2 separate databases.

I have a database which I use for keeping track of the status of documents from different dates. Every couple of weeks a new load of documents come in and I have to create a new table on my database to keep track of the documents that are being translated.

Now, we have another database with its own table that contains every document that we have ever received. I need to design the database so that I can link the data on all my tables on my database with the data on the other table on the other database. So that whenever I update something on one of my tables, it is automatically updated on the other database. You may think this is useless and that I may as well just keep the tables on the same database, but this is what the boss wants… so I have to deliver.

I will give you an example of what is needed:

Database 1
Table 1
Table 2
Table 3
Table 4
Table 5


Database 2
Table 1

Everything on database 1 needs to automatically update on database 2 whenever something is changed/added/deleted.

To anyone who can help… you are an absolute legend. I have been trying to figure this out for so long.

[grahto]

anyone? I could sure use some help on this =)

[Stewart Ross]

Hi. Short answer is that Access has no facilities which will allow you to keep two separate sets of tables and mirror all updates to another set in real time - and as you have grasped yourself it creates all sorts of difficulties by trying to do so. Even if you were using a true transaction processing database such as Oracle (where update transactions are explicitly confirmed before taking effect, and transaction roll-back is possible) you would be hard-pressed to come up with any reason at all for maintaining two sets of tables in the way you describe.

The normal approach is to have all the tables in one, central, back-end database and one or more (if needed) separate front-end systems linked to the single back-end set of tables.

In trying to maintain two copies of the data in multiple tables you defeat the purpose of a relational database, where the minimisation of redundant data is a design principle.

I think you should reconsider your database design and see if you can do away with locally-held tables altogether. Whatever you do, it should be on one set of data only. That data can be distributed over many back end systems if need be, and by linking your local database to the back-end tables you should be updating the same master table set that everyone else has access to.

-Stewart