How to redesign a backend without loosing data?

[scs]

How would one go about redesigning a backend full of data. I know how the
tables should have been built but I don't know how to go about redesigning
them now that their full of data. For example:

The database has a tblLease table full of data. Most of the fields in this
table should have been put into a separate tblVisit instead. The tblVisit
table has not been created yet. Some of the information needs to remain in
the tblLease. The tblLease has tables related to it, where it is the one
side of a many to one relationship. The tblLease is and the new tblVisit
needs to be on the many side of a one to many relationship with the
tblPerson.

I may even decide to structure it like this tblPerson related to tblVisit
related to tblLease in a one to many relationship.

tblPerson
----------
PersonID
FirstName

tblVisit
----------
VisitID
PersonID
StartDate
EndDate

tblLease
----------
LeaseID
VisitID
StartDate
EndDate

If I could get the PersonID, StartDate, EndDate, and a buch of other data
into a new table called tblVisits I'd be most of the way where I need to be.

I'm sure there are some shortcuts here. I'm not too worried about the
frontend. I can redo it before hand. I just want to get the backend in
better shape. Export tables, copy them, make table queries? What's the
best way?

TIA

 

[Allan Murphy]

1. Create your new table tblVisit.
2. Use an append query to append the relevant fields from tblLease into
tblVisit
3. You may also might look at deleting the fields from tblLease after you
have appended to tblVisit

 

[Larry Linson]

1. Create your new table tblVisit.
2. Use an append query to append the relevant
fields from tblLease into tblVisit
3. You may also might look at deleting the
fields from tblLease after you have appended
to tblVisit

You will, of course, have to Open the database for Exclusive Use to make
design changes. Access is very "forgiving" and lets you make very drastic
changes without losing data. Just be certain that you have used your Queries
to populate the new tables, and made any other changes, before you delete
the original information.

Larry Linson
Microsoft Access MVP

 

[scs]

Thanks very muc!. I'll have to do this over a weekend when the data isn't
being used.

 

[scs]

Thank you! I think I'll play with a copy first. I'll keep track of how to
do it, make changes to a copy of the frontend, and then work on the real
backend over a weekend when it's not in use. Thanks again.

 

[(PeteCresswell)]

Per scs:

Export tables, copy them, make table queries? What's the
best way?

Dunno from "best", but what I'd do is create a new DB from scratch and go
through the following:
------------------------------------------------------------------------
Once I had the structure worked out, I'd clone it, and zip it up as
NewDB.Empty.001.zip.

Then I'd create a link in the new DB to each of the tables in the old DB.

Using the links, I'd create queries in the new DB that populate tables in the
new DB.

Before I ran the first query, I'd zip the thing up as NewDB.Empty.002.zip.

From there on, I'd be doing an iterative process:

- Run a query
- See how well it worked
- Make any needed changes
- Back up that version
- Run another query.....


Once I got all the queries running correctly, I'd create a macro (don't usually
use macros... but this would be an exception) that ran all the queries
successively.

With the macro in place, I'd purge all the tables, compact/repair the DB, and
zip it into yet another NewDb.Empty.nnn.zip.

Then I'd run the macro in the empty DB.

After that, I'd look long and hard at the results and start trying to convert
the application.

I'd consider it almost inevitable that some additional changes to the new DB's
structure would be required once I got into converting the app.

Each time the need for one of those changes became apparent, I'd unzip the last
NewDb.Empty.nnn.., make the changes, zip it up again into a new version, run the
macro to populate the empty changed version, and continue on my way.
------------------------------------------------------------------------



Oh yeah... I'd also have a table in both the back end DB and the new version of
the application called "------------ Program Changes ------------------"

Each time I made a new version of either, I'd add a record to that table
describing the change and rename the DB to MyApp.nnn.mdb where nnn=PK of the
record in the program changes table.

Call me obsessive, but this kind of thing works for me. I can't remember what
I had for breakfast... but when I come back to an app after three months of
working on something else I can get oriented pretty quickly just by looking at
the "changes" tables.