I have a database I have been working on a copy of. Now that I am finished, (or almost finished) how do I transfer my data over to the new version? The problem is that the old database has been in use while I have been updating a copy, so there are new records. In addition, there are several tables in the database that are all interconnected by relationships between ID fields that are autonumber fields. I worry about the autonumber fields renumbering themselves when I transfer the data and not matching up correctly with the other tables. What to do? How do I maintain the integrity of my data?
Transfer data to database in use...
- Thread starter Kila
- Start date
dan-cat
Registered User.
- Local time
- Today, 21:39
- Joined
- Jun 2, 2002
- Messages
- 3,415
Phew - this depends on whether you have made any structural changes to the tables.
First of all you will need to make sure no-one is inputting data. Then take a fresh copy of the old db.
Strip out any forms/queries/macros/reports.
Import all the forms/queries/macros/reports from the new db into the old db.
Tables is a little more complicated.
You are going to need to remember any changes you have made to the table structure.
If you have created any new tables then import them from the new db
Amend the old db table structure so it matches the new db structure (add fields,check relationships etc)
Compact and repair.
Don't delete any tables from the old db because they will contain data. Just amend their structure so they match your new db table structure. Be careful that any amendments you do doesn't alter data - for instance amending field sizes.
Don't delete the original old db until you are confident all is well.
In the future it would be better to have a front/backend arrangement to your db.
All forms/queries/modules/macros/reports to be stored in the front end db
All tables (containing shared data) in the back-end.
The front-end is linked to the back-end via linked tables.
This means all you have to do is amend the front-end and give a fresh copy of this to each user on their local machine.
The back-end is stored on a network share. If you need to amend table structure then you can still do so but tables can be locked from design view by the front end. This usually means getting all your users to quit their front ends in the middle of the day or (the more likely scenario) you wait until everyone has gone home after sending an email reminding everyone to quit the db before leaving. There is always one who doesn't
First of all you will need to make sure no-one is inputting data. Then take a fresh copy of the old db.
Strip out any forms/queries/macros/reports.
Import all the forms/queries/macros/reports from the new db into the old db.
Tables is a little more complicated.
You are going to need to remember any changes you have made to the table structure.
If you have created any new tables then import them from the new db
Amend the old db table structure so it matches the new db structure (add fields,check relationships etc)
Compact and repair.
Don't delete any tables from the old db because they will contain data. Just amend their structure so they match your new db table structure. Be careful that any amendments you do doesn't alter data - for instance amending field sizes.
Don't delete the original old db until you are confident all is well.
In the future it would be better to have a front/backend arrangement to your db.
All forms/queries/modules/macros/reports to be stored in the front end db
All tables (containing shared data) in the back-end.
The front-end is linked to the back-end via linked tables.
This means all you have to do is amend the front-end and give a fresh copy of this to each user on their local machine.
The back-end is stored on a network share. If you need to amend table structure then you can still do so but tables can be locked from design view by the front end. This usually means getting all your users to quit their front ends in the middle of the day or (the more likely scenario) you wait until everyone has gone home after sending an email reminding everyone to quit the db before leaving. There is always one who doesn't
Good idea!
This is a good idea. How do I get TO this front end/back end stuff to set it up that way? This is new to me. Since you suggested this for in the future, I imagine that it is too difficult to retrofit. Just as an FYI, my database is currently on a server, with shortcuts on peoples' desktops.
This is a good idea. How do I get TO this front end/back end stuff to set it up that way? This is new to me. Since you suggested this for in the future, I imagine that it is too difficult to retrofit. Just as an FYI, my database is currently on a server, with shortcuts on peoples' desktops.
