Trying to upgrade customers' database

jeffcflam · June 14th, 2005, 08:45 AM

Hi all,

I am using Visual Basic 6 stand alone application with Access (2k to 2003) database. Now I have changed a lot of stuffs, for example, adding, renaming and removing tables and fields, and adding queries, in the database. Then I have to upgrading my customers' database, too. However, they are not willing to give me their copy of databases because of their private data. So I need to write a VB program to automatically upgrade their databases on their side and keep all their existing data.

Sounds complicated, doesn't it? Any recommandations about the best ways to do it? Besides, how can I verify if a data field already exists in a table?

Appreciate for replying soon.

gbianchi · June 14th, 2005, 08:53 AM

hi there..

you can do all that kind of things using sql.. you can use

Code:

 CREATE

to make a new table, add or drop to add or remove rows...

and to verify if a field exists you can do a query to retrieve that field ;) and if you have an error that field no exists...

HTH

Gonzalo

ejan · June 15th, 2005, 01:49 PM

To do that you can take one of two appoaches depending on whether you can copy data from an old table, say my_old_table, to the corresponding new table, say my_new_table, using a simple SELECT statement.

If you can do so, your best bet is to write CREATE TABLE statements and execute them against your customer's database so that it contains the old tables + data in them + new tables. Now simply execute SELECT statements to bulk-copy data from the old tables to the new ones.

If you cannot do so, however, the method I just described is not the way to go. In this case, you should create a separate database with the new structure. Then write a VB program that processes each table sequentially and dumps data from the old database to the new one. If you do take this approach, be sure to take care of any constraints. For example, you'll have to copy data to a parent table before you can copy data to a child table, a table whose records reference records in the master table. After data is copied to the new database, delete the old database.

In either case, be sure to have your database backed up, just to be on the safe side.

I hope this helps.

Regards,

ejan