Copy Access DB field-by-field.

[JDMils]

I need to upgrade an Access DB. I need to copy all the tables and their fields from the existing DB to a new one. Some of the fields need to be removed and I need to add some in their place.

So my idea is to cycle thru all tables in the existing DB, and copy each field across to the new DB, one at a time, deleting and inserting fields as required.

I have never programmed VB.Net for Access, so I would appreciate as much help as possible.

Thanks

 

[DavidT_macktool]

Sounds easy, right? Unfortunately it really isn't. There may be several ways to accomplish your task.
Here are some pointers:
First create the new database and all the tables - Now is the time to get all the fields you want and get all the relationships correct. This will take quite a bit of time so slow down and do it right.
Second, you will work on the transfer.
Things to consider - continuity between old and new datatables. You can't keep updating the old table after the transfer has been made. Once the transfer is made all access has to point to the new table. Sounds like a weekend job - to keep everyone out until it is done. Some large tables take quite a while to transfer.
When I did the transfer/update I created a transfer project. Each table had a form to do the transfer/update. I added 2 dataadapters and 2 datasets to the form (one for the old table one for the new table). I bound each table to a datagrid and added a button to start the transfer. I used a FOR/NEXT loop to go through the old table and add each record to the new table. I mapped each old table's field to the new table's field and added new field info when needed. By adding a dataset for the old table it allowed me to select only the info I wanted to transfer (ie data with a date greater than 1/1/2001). That helped clean up the tables. I also performed some queries to get rid of unused information - why transfer part history of a part if the part number has been removed? Query the Part table and if the part number exists transfer the record. Cleaned up the tables in that manner also. Each form turned out to be a little different but almost all of the underlying code was very simular - just field names changing - logic stayed the same. I also added the save button to commit the changes after I had a good look at the data that had been transfered.

Hope you don't have a lot of tables to transfer, and I hope this helps.

 

[Administrator]

One thing I do to copy data from an older Access DB to a newer is first create an APPEND query in Access. You may need to create a dummy DB first so you have a target. Then you can copy/review the SQL code and use it. It's not as painful a process as it may seem.