Hello everyone! I've been working with a problematic legacy database
for a while. While I am still fairly new to Access, the more I work
with the database, the more problems I've uncovered. Unfortunately,
most of these problems lie in the ways of architecture. A lot of the
tables are designed with no primary keys, and a number of them using
the text names in place of the PK-FK relationships.
For instances, I have one table called [Job Transfers] that refers to
another table called [Job Postings]. The relationship is done via the
_name_ of the posting, rather than by an FK. What's worse, the same
text field (let's call it "[Transfer Reason]") that is used as a FK
also doubles as a reason for the transfer. Let's say there was a
posting named "Winter Sign-up 2003". There would be job transfer
records that list "Winter Sign-up 2003" in under [Transfer Reason],
but some transfer records would have the entry "Union Agreement" under
the same field -- when there are no postings named "Union Agreement"!
*Just to be clear, these are the _actual_ values that are stored. I'm
not looking at a FK-hiding combobox here.
There are other problems with the database too, such as multiple
(undocumented) queries that seem to serve the same purpose. And I
have already untangled some queries that were suppose to be joins but
returned a cartesian product of the involved tables instead.
I have tried to fix what I could with the database, but I am beginning
to consider importing all the data into a fresh database, with a
redesigned table structure, and more efficient queries.
Unfortunately, there is no "Untangle Mess..." option in the Access
menus. Could anyone recommend an approach I should take to tackle
this?
The main concern I have is how to generate new PK's and FK's and
making sure the relationships are preserved between databases (i.e.
the transfer record that refers to "Winter Sign-up 2003" still refers
to the correct record, but using an FK rather than text).
What's more, I need to write a VBA module to do this somewhat
automatically. Or, rather, I need an efficiently reproducible way of
converting the data from the old format to the new database format.
Since I am working with copies of the production database, I'll be
converting databases a good number of times.
Sorry for the long post, but can anyone give me some suggestions for
this? Any help would be greatly appreciated.
Alan