Restoring Primary key - and more

[Ugiliz]

Hi I wrote a simple membership/training database in 2004 (Access 2002). The
idea was to have a Master Table with members details and sub tables for
training courses and membership payments. The training front end was working
well but the membership database was in development as my client couldn't
decide what to do.
I've been working abroad since then and, in any event was an amateur at
Access. My client has asked for more advice. Someone else has tampered with
the database, They've removed my autonumber primary key and replaced it with
a simple join using membership number for queries/reports. Without
referential integrity the data is becoming messy. I can't make a one-many
relationship on membership number. I get an error message about referential
integrity. This is a text field and although master and sub table have same
format the actual data is of varying lengths and symbols.
In addition, instead of building on one database and adding front ends for
training, membership fees, etc there are now 2 separate databases containing
(duplicate) information.
For the training part I have added an autonumber ID to the master table. I
can't find an easy way to copy this to the relevant training subtables (many
per member). Any advice on this? I'm doing it by hand but it is tedious!
The next challenge will be to marry the details from training database with
the membership database so that there is just one database. Is there an easy
way to do this - or indeed anyway? Some members will not have not been on
training and will need an ID for the primary key, then I will need to match
and add ID (foreign key) to several subtables on the current membership
database.
Oh dear, what a mess! Hope someone can help me (and I'm doing this for a
charity for free!)
Thanks

 

[GeoffG]

1.

Without referential integrity the data is
becoming messy. I can't make a one-many
relationship on membership number.
I get an error message about referential
integrity.

You may have records in the Training Courses table without
corresponding records in the Members (Details) table.

2.

This is a text field and although master and
sub table have same format the actual data
is of varying lengths and symbols.

Give an example please.

3.

For the training part I have added an
autonumber ID to the master table. I
can't find an easy way to copy this to the
relevant training subtables (many
per member). Any advice on this?

Create a new long-integer, foreign-key field in the Training
Courses table named fkID.

Assuming the Members and Training Courses tables are linked
by the text field you talk of, you should be able to run an
update query to copy the ID field in the Members table to
the fkID field in the Training Courses table. Here's a
step-by-step procedure:

1. Backup the database before proceeding.

2. Create a new query.

3. Add the Members and Training Courses tables to the query
in design view.

4. Open the Query menu and select Update Query.

5. Drag the fkID field down from the Training Courses table
to the field row of the design grid.

6. In the fkID column of the design grid, in the "Update
To" row, enter:
[Members]!ID
Notice the exclamation point between [Members] and ID.

7. Save the query.

8. Run the query.

9. That should copy the new primary key field, ID, in the
Members table to the new foreign key field, fkID, in the
Training Courses table.

10. Ensure all records in the Training Courses table have a
value in the fkID field before changing the relationship
between the two tables.

11. Enforce referential integrity on the new relationship.


Geoff

 

[Larry Daugherty]

Posts here should be limited in scope to one technical issue per
thread. More issues, more posts. There is some helpful informatin at

www.mvps.org/access

look for "Netiquette"

Your issues are in the political/relationship arena even more than in
the technical arena. You have to decide how you'll address those
issues before it makes any sense to address the technical issues.

Some suggestions:

On the political and business side, "Free" is definitely not the right
price to charge. Your client values your work by what was paid for
it. You can have an earnest discussion with the client about
respecting your work (simply point out what it would cost per hour
[$25 - $100+ per hour] to hire the work done by a professional. No
matter how compelling the discussion, she/he/they/it will probably
promise to do better but actually behave in the future as they have in
the past.

You might also point out that the project was in stasis because s/he
wouldn't get off the dime and work with you to complete it. Also, get
them to give you the design that they asked the other 'developer" to
implement.

Technical issues:

Starting with the last backup of your design build in the new design
elements. What? You have no backup of your design? If that's the
case then you know that you have need of a backup plan that works. No
backup plan that depends on a user to understand and execut it is
likely to succeed. There are all kinds of strategies and utilities in
play. Search the Access related newsgroups for them. Possibly easier
for you would be Groups.Google.com. Post back if you still have
backup issues after your research..

Before you start working on the design, make a backup of everything
that's there. Copy that backup into your own personal development
area and do all of your work on that backup copy.

One way or another, get back to the design *you* had. Give your new
application a name that is slightly different from the working
application If new information makes you aware that you have new
entities in play then add them to your schema. Impose Referential
Integrity as needed. Complete your user interface design. It should
be pretty close but because the other person probably changed some
elements you'll hae to analyze them carefully. Make sure the user
interface correctly handles the changes in the schema.

Compile your work frequently. Make frequent interim backups.
Sometimes changes in the user interface can cause loss of the current
application. If you have backups it's a simple matter to get back to
the last one that didn't crash.

Once your design is pretty well complete, purge all of the tables with
delete queries. Get the most current data of just the main tables and
just those child tables for which RI was maintained from the current
working application. Eventually, someone will have to resolve or
re-enter all related data. (*Don't you do it, let someone else do
it!*).

If your application has never been split then start the process by
making a backup copy of your current work. Then grit your teeth and
follow the directions you find in Help for "Split ...". Give the
BackEnd a name that is the name of your FrontEnd with the letters
"DAT" appended (no quotes).

Place the back end of your newly split application into the folder
where it will reside during its working life. If you are working on a
different system then create an identical folder structure for that
path on your development system. If that isn't practical then there
are other ways to get things done. Post back if necessary. With the
back end in that folder structure, open the Front end (you may get
errors because the data isn't found). Tools|Database
Utilities|Linked Table Manager ... Navigate to *DAT.mdb and select
all. Click OK Your FrontEnd now links to tables in *DAT. Everything
is working as it should.

Compile the front end one last time to be sure all errors are gone.
Compact and Repair both the FrontEnd and the BackEnd.

Close the application but leave Access open. Again in Tools|Database
Utilities|Make MDE. Make an MDE of the FrontEnd.

Make backup copies of the MDBs of both parts of your application.

Give a copy of the FrontEnd.MDE to every user of the application who
is on the same LAN.

Move all copies of the MDBs that you have been working on into a
secure location that is available to you along. So long as you have
the only copies of the MDBs no one can make changes to your front end
design. Malicious people could mess with the back end.

Keep the code secure and, if ever again asked to support the
application you will have the code to start with. If your schema
design was good then most changes will affect the FrontEnd only. Let
your client know about your keeping the source code secure from
tampering before you start doing any of the things above.. No matter
what their promises, if they insist that they must have the source
code I would tell them "You already have it", make no changes and walk
away. Alternatively, now might be the time to require the payment of
hourly work before you even begin.. Then, if they insist on having
the source code (after they've paid your bill, of course), let them
have it without a quibble. Let then know that once the source code is
in their hands you have no feelings of responsibility for the
application's design.

One grouchy old guy's opinion.

HTH