By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Recreating Problematic Database

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Create your new database and structure, then create a conversion database in
Access linking to the old AND new database tables. Create a series of
queries to import the data from the old to the new. Store the query names in
a local table. Write a simple VB function to open the table and run the
queries in order. You can then test the new database without endangering the
existing data. Repeat emptying and importing into the new database until you
have a working application.

"Andante.in.Blue" <05********@sneakemail.com> wrote in message
news:bc**************************@posting.google.c om...
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

Nov 12 '05 #2

P: n/a
05********@sneakemail.com (Andante.in.Blue) wrote in message news:<bc**************************@posting.google. com>...
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

Alan,
If I were you, I'd start with documenting what you have. You might
want something like SpeedFerret to find where all the various bits and
pieces are used in the DB so that you don't go out of your mind trying
to figure it out. There may be code that does this in the NG, I'm not
sure. But at least if you can work out something of a blueprint from
what you have now, you can move forward in an intelligent manner. if
you get this part right, you should be able to fairly quickly and
easily recreate the functionality of the existing DB. (so make sure
you get all that right!)
HTH a little.
Nov 12 '05 #3

P: n/a
Thanks for the reply paii. Your idea of using a conversion database
is interesting. I sounds like a good approach to a reproducible
conversion method. I think I'll give it a shot.

I'm still worried about the autonumbering within the database though.
Some tables have autonumber PK's, and they won't remain the same if I
import it from one table to another; so somehow, I'm going to have to
change the FK's in the database as well.

Other tables will have to get new PK's in the new table. How should I
make sure all the records still refer correctly to each other? Is
there a standard method of doing that?

"paii" <pa**@packairinc.com> wrote in message news:<vo************@corp.supernews.com>...
Create your new database and structure, then create a conversion database in
Access linking to the old AND new database tables. Create a series of
queries to import the data from the old to the new. Store the query names in
a local table. Write a simple VB function to open the table and run the
queries in order. You can then test the new database without endangering the
existing data. Repeat emptying and importing into the new database until you
have a working application.

Nov 12 '05 #4

P: n/a
Thanks pieter! I have a good idea of how the database works. I guess
I should have picked up (or ask my employer to pick up) SpeedFerret a
while ago, but as it is, I've worked with the db for a while now, and
I think I know where everything is. I'm just looking for procedures
or approaches to follow to help introduce new PK's to records, and to
maintain the correct references between tables. If you could give me
tips for that, that would be awesome!

Alan

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
Alan,
If I were you, I'd start with documenting what you have. You might
want something like SpeedFerret to find where all the various bits and
pieces are used in the DB so that you don't go out of your mind trying
to figure it out. There may be code that does this in the NG, I'm not
sure. But at least if you can work out something of a blueprint from
what you have now, you can move forward in an intelligent manner. if
you get this part right, you should be able to fairly quickly and
easily recreate the functionality of the existing DB. (so make sure
you get all that right!)
HTH a little.

Nov 12 '05 #5

P: n/a
I really don't like autonumber fields because of the problems you have.
"Andante.in.Blue" <05********@sneakemail.com> wrote in message
news:bc**************************@posting.google.c om...
Thanks for the reply paii. Your idea of using a conversion database
is interesting. I sounds like a good approach to a reproducible
conversion method. I think I'll give it a shot.

I'm still worried about the autonumbering within the database though.
Some tables have autonumber PK's, and they won't remain the same if I
import it from one table to another; so somehow, I'm going to have to
change the FK's in the database as well.

Other tables will have to get new PK's in the new table. How should I
make sure all the records still refer correctly to each other? Is
there a standard method of doing that?

"paii" <pa**@packairinc.com> wrote in message

news:<vo************@corp.supernews.com>...
Create your new database and structure, then create a conversion database in Access linking to the old AND new database tables. Create a series of
queries to import the data from the old to the new. Store the query names in a local table. Write a simple VB function to open the table and run the
queries in order. You can then test the new database without endangering the existing data. Repeat emptying and importing into the new database until you have a working application.

Nov 12 '05 #6

P: n/a
Import the autonumber fields into a standard integer field. Your new table
will have it's own PK. You can then use the imported field as a cross
reference for assigning the new PK to the detail tables FK.

"Andante.in.Blue" <05********@sneakemail.com> wrote in message
news:bc**************************@posting.google.c om...
Thanks for the reply paii. Your idea of using a conversion database
is interesting. I sounds like a good approach to a reproducible
conversion method. I think I'll give it a shot.

I'm still worried about the autonumbering within the database though.
Some tables have autonumber PK's, and they won't remain the same if I
import it from one table to another; so somehow, I'm going to have to
change the FK's in the database as well.

Other tables will have to get new PK's in the new table. How should I
make sure all the records still refer correctly to each other? Is
there a standard method of doing that?

"paii" <pa**@packairinc.com> wrote in message

news:<vo************@corp.supernews.com>...
Create your new database and structure, then create a conversion database in Access linking to the old AND new database tables. Create a series of
queries to import the data from the old to the new. Store the query names in a local table. Write a simple VB function to open the table and run the
queries in order. You can then test the new database without endangering the existing data. Repeat emptying and importing into the new database until you have a working application.

Nov 12 '05 #7

P: n/a
Actually, I've discovered that autonumber fields weren't as
problematic as we thought... at least not in Access 97.

Here's what I've done so far (might be useful for anyone else who has
a similar problem). I've create a series of queries in a "bridge"
database is linked to both (a copy of) the old database back end, and
a blank front end (with all the tables set up, but having no records).
Then, I've recreated, at paii's suggestion, a bunch of append queries
that take info from the old db's tables and pop them into the new
ones. The "base level" tables (ones without a FK) were a trivial
matter and were imported first. Then, any tables that require newly
generated FK's can "look up" the appropriate PK using the append query
to join the "source table" from the old DB, with the newly created
tables with the newly created PK's. I've added in a few select
queries to help me weed out any problematic records (like duplicated
records, or orphaned children) and deal with them accordingly. This
works lightning fast.

All this was done without enforcing relationships, btw. This way, I
won't get error msg's preventing me from adding records.

Now the funny thing is that, although I've set some of the new PK
fields as autonumbers, I can still import "old" numbers into them via
append query. For instance, I have a set of job transfer records that
use an autonumber field for a PK. When I import this set of records
to the new table, Access lets me keep the same numbers as before!
This is great, since now I don't have to run through the database
correcting FK's as I had feared before.

I am running into another problem though. I'm getting an error when
I'm running this import query. Access complains that "it didn't add
10 records to the table because of key violations". But when I count
the number of records in the old and new tables... they match exactly!
I know I've clear the table before importing the data, does anyone
know what could be causing this? Unfortunately, Access doesn't give
me any clues as to which records were "omitted".

Alan
"paii" <pa**@packairinc.com> wrote in message news:<vo************@corp.supernews.com>...
Import the autonumber fields into a standard integer field. Your new table
will have it's own PK. You can then use the imported field as a cross
reference for assigning the new PK to the detail tables FK.

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.