Central Database location for tables

[jw45]

I have a situation and don’t know the best approach. I have come into an environment where there are many databases (20-30) using many of the same tables. Example is that db 1 may use tables from 2,4, & 5 and db 2 may use table from 4. Hope that makes sense.

One db in particular has information created and updated by the scheduling group but is needed by many other areas (databases). I want to share this table but do not want the other users link directly to the ‘master’ table. If I understand ‘links’ correctly it is a link and will update the data in the table. Because of data corruption and other concerns I do want to link the tables.

What I want to do is create one database that is a tables ‘warehouse’ and have the tables in this db be copies of the master. In this db the tables will be a ‘copy’ of the table from the source or master db. The problem I have is how to keep the copies up-to-date.

Can someone give me an approach or direction to take?

Also, really want to stay away from MS security if possible.

 

[Remou]

This:
Read Only Links
thread700-1296823
May give you some ideas.

 

[jw45]

some very useful stuff written by Duane (thanks Duane if you read this) and have already incorporated in a couple of my databases. However, that is not exactly what I was looking for.

Users have many databases (some sorta complex) that link directly to the tables that I am trying to protect. They already have established queries and code using these reference so I don’t want to change the basics. By linking directly to the db that I refer as creating/maintaining the source data, they cause me at least two concerns. One, is that the can accidentally change/delete the data. Two, depending on what they are doing with their db they put the source db in a locked state ‘i.e. .. you do not have exclusive access….” Then leave their db open for long periods of time forcing me search around to find who has the db locked.

What I want to is create a new database and place a copy the table there and then change the link in their db to point to the new location. This
should require minimal programming.

By the way, I tried the import on open option but some of the tables are quite large and it takes a while to get the db open.

What I am stumped on is the best method and frequency to keep the ‘source’ table synchronized with the copy. Anyone have any suggestions?

 

[lameid]

Why not use Access Security to keep them from updating data (remove all but read data and read design permissions in the backend table from Admin) and secondly remove open exclusive permissions from the database object. I am hoping you are at least a little familar with Access security as it is a broad subject.

 

[dhookom]

I have you consider creating snapshot queries in your user mdbs with SQL like:

SELECT MtgLocations.*
FROM MtgLocations IN 'C:\CDM Meeting Manager.mdb';

If you set the query Recordset Type to Snapshot, the users should not be able to edit. Name the query the same as the table name from the remote MDB.

Remind me to send you a bill for the "very useful stuff"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]