Solved

Access Database Design

Posted on 2012-04-12
4
209 Views
Last Modified: 2012-04-12
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.

Any suggestions?

- Methos
0
Comment
Question by:Methos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 37838863
Provided all the user input is through your forms - so that there is no way they can enter data directly into tables or via queries - then you should not have a problem.  I assume you have suitable validation and other control mechanisms in your forms that prevent invalid data being entered.

I would say that the solution you are proposing is very sensible.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 37838871
For "Lookups" referential integrity may not always be needed.
In some cases the users are/were allowed to type in "Free Text"
(Set the "LimitToList" property of the combobox to: No)

This means that the existing data may not be in your "revised" list, thus RI cannot be enforced.
Again, this is sometimes to be expected.
There is nothing inherently "wrong" with this (Unwieldy, yes, ...but not wrong)

You can however get a unique list from the existing data though, and verify this list against your "official" list.
For example this will get a unique list of Departments from a "Orders" table
SELECT DeptID
FROM Orders
GROUP BY DeptID;

HTH

;-)

JeffCoachman
0
 

Author Closing Comment

by:Methos
ID: 37838976
Thanks to both of you.  I do use input forms, but haven't really stopped anyone from getting at the tables - which I'll set in motion now.   I also left the combo box property left at not limiting to the list, which I'll change.

Both answers helped me equally...  I split the points but can't pick both as "best solution".

Thanks -- I can move forward.  I appreciate you quick answers back!
0
 
LVL 21
ID: 37839050
<<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.>>

It is true the data engine can't enforce referential integrity across databases. That does not mean that you can't write code that does. Before RDBMS's could enforce RI, I have to write code to do it.  As JeffCoachman poited out there are times when you do not what the RDMS/database engine to enforce RI.  I have apps where I enforce the RI with code not the RDMS/database engine.

When trying to keep remove sits in sync, you have to be very careful about deleting anything from your master tables. It is easy to add data to your master tables.

Back in the mod 90's I have a client with offices in many states.  The remote office were set up to create an export databases that was then FTP'ed to the main office.  The app also looked for updates on the FTP site. It would download the updater database and open it. It would auto run and update the master list tables.  This allowed me to use a single back end oat each location.   I also never delete records. Only mark then as inactive.  To delete records from master list, you will first need to update all the existing data so it is not linked to the record that will be deleted.     They still use this method toady.

My recommendation is to never delete records. Only mark then as inactive. You can easily filter out these records.

FWIW: I was fortunate to have learned never to delete records from the very start from some DBA masters. I have been doing it this with every application for 30+ years and it has served me well.


EDIT: Well ...  I took to long to type my reply.  Hope it helps anyway.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question