I am redesigning an Access database system and could use some advice.
Currently I have multiple self-contained databases being filled out by users at various locations. Monthly I import records from each database into a Master database. [I'm not able to setup and connect everyone to a shared MDB or SQL database.] This is working out fine.
However, each database has lookup tables that I wish to now update frequently and distribute to everyone. I was going to put all reference tables into a separate database, link these reference tables to the "input" database - so I could easily distribute a new reference database without having to go into each person's input database and change things.
My problem is that I can't set referential integrity to linked tables and I want to control what is entered into fields using these lookup tables.