How do you change the format of a database without causing issues with old data that is used?

I hava a database that has over 230 questions. So now I am in the process of making the database the correct way. The problem is Im not sure how to deal with the old data.
The users are constantly transfering the data back and forth. Adding and editing data they receive.

Any suggestions on how I should approach this experts will be apreciated


Example:

Old DB
tblInductionData
Fields Question1
Fields Question 2
Fields "              "
etc


New DB
tblQuestions
Fields QID
Fields Questions


tblGeneralInfomation
ID
Name
Unit

tblDropdown
Field YES/No
Field Inspectors
Field DateInspection

tbleResponse
ID
ResponseId
Response
gigifarrowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You have to write a conversion process as a one-time deal (or do the work manually).

Jim.
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, there is no easy way to change the basic table design while the users are currently inputting data into the old design.

There are a lot of unknowns here, but in the most basic sense you could:

Create a separate database with the correct design (with fake data)
Then test this design *Fully*
Then create a system to migrate the old data to the new system (again, create a system to do this with Fake data, and test it *fully*)

Make sure you have a backup of the original database!
Then pick a day and deny all access to all database files until the conversion is complete.
Migrate the data using your system.
Test the converted database *fully*, then redistribute the db...

Again, There are a lot of unknowns here, so do not take this as gospel...

JeffCoachman
0
hnasrCommented:
Like both previous comments.
Adding,
1- You need parallel run.
     New database
     Process to transfer old to new
     Enter data in old, and in parallel in new
2- check data in both old and new and make sure all is going fine
3- During a vacation day, stop old database, and publish new for online usage.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gigifarrowAuthor Commented:
Thank you for all your suggestions. I was trying to attach a example but It would not updload for some reasons.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.