473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Recreating Problematic Database

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
7 1944
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: lkrubner | last post by:
I have a webserver through Rackspace. I create a domain. I create an FTP user. I upload some files. I create a database called testOfSetupScript and then I create a database user named setup. I...
3
by: José Luis Ramírez | last post by:
Hi, I'm trying to recreate an array from a var_export()'ed array stored in a database, using eval(). Basically I have something this in my database: array ( 0 => 1, 1 => 2,
1
by: Xif | last post by:
Hello Everyone! Here's a problem with relative imports: Suppose I have a package called some_package (in a separate directory included in the PYTHONPATH, with an __init__.py file etc.) This...
0
by: Cherrish Vaidiyan | last post by:
hello, Thanx for the suggestions on my Listener query. Now i am performing a simple work.. STANDBY DATABASE creation. I have followed the instraction from Oracle 9i Release 1 documentation...
0
by: Cherrish Vaidiyan | last post by:
sir, The following are the steps that i followed in setting up standby database on Red hat Linux 9. i am using Oracle 9i. i have followed the steps in this site : ...
4
by: Jenni | last post by:
Hopefully someone out there can help. I am currently trying to write some code to allow me to delete a table, then recreate it and re-establish the relationships. I seem to have hit a snag in the...
23
by: Steve Jorgensen | last post by:
Hi all, I'm working on a project through a consulting company, and I'm writing some database code for use in another programmer's project in Excel/VBA. The other programmer is working through...
0
by: Duray AKAR | last post by:
Most probably, you are loading the data to the label before you submit the update. I dealt with that type of an isssue before, you question is not descriptive enough to make certain decisions,...
6
by: opt_inf_env | last post by:
Hi, I would like to insert a data (row) into database (MySQL) and than to generate file whose name contains the ID of the inserted row (ID is declared as AUTO_INCREMENT). I wanted to extract ID...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.