Moving All Data from one Database to Another

scotthutchings

Registered User.
Local time
Today, 15:34
Joined
Mar 26, 2010
Messages
96
I need to replace the backend of my database with one that adds several fields to many different tables. There are currently 122 tables so the manual method is not very appealing (especially because I anticipate further additions from the client which would require me to do it again). The database is currently live so I need to transfer all the data from the existing database into the tables of the new database. I am not on site so I will be sending the client an update so I need to do it via code. How do I cylce through all of my tables and Insert all the data from my tables in one database to another? One more element is having the user that will actually perform the update point to the existing database location through a browse window?

Thanks for your help!
Scott
 
While 122 table could be quite valid it does beg the question whether the database is normalised, particularly if you are having to consider changing the structure of a significant number of them.

Anyway, my approach to updating remote schemas is to write a series of SQL statements to add the extra fields. These can be run in sequence using either macros or VBA. Personally I prefer VBA because I can handled any errors.

My reasons for this approach (even with 122 tables) is that you can easily review the script and fully test prior to implementation. Furthermore, it avoid the big headache of trying to transfer data from a deeply relational schema (the problem you are facing).

Of course you should always take a backup of the users database(s) prior to implementation.

Chris
 
How do you write the code to add a field to a table?
 
How do you write the code to add a field to a table?

If you don't want to write all of the code, you can use my free Backend Auto Updater tool which you can just add entries to a table as to which fields you want added to where and the datatype and it will do the job for you. Also download the documentation for it.

You can find it here.
 

Users who are viewing this thread

Back
Top Bottom