Updating data between database versions

[grahamvb]

I have been asked to add features (and fields) to an existing database containing private medical data. The idea is to add features to the empty database, test it with fake data, purge the fake data and deliver it to the user in another state with a simple update button. The Access 2010 database is in one piece (No front/back end).

I am looking for the best/shortest VBA method to move a lot of preexisting field data from the old database to the new version. I think I have it mostly figured out; I have code for counting, reading and writing the table names, field names and field data.

However, because the data types vary within the tables and there will be new fields in the new version’s tables, my only idea, so far, is to code through the data table-by-table and field-by-field, a lengthy process with a large footprint. Moving entire tables or complete records may not be effective. Can anyone think of a smaller, faster way to transfer all these tables’ field data?

Thank you for considering solutions to this issue.

 

[pbaldy]

I suspect the reason nobody has answered is that they're afraid of the mess this will be. Is there a reason the db isn't split into a front/back end (or why it isn't the first thing on your agenda)? I would never have a production db that wasn't split. When I make changes, I just send a new front end. If table changes were required, I typically send a little db with a button to run code to make the changes in their copy of the back end.

 

[grahamvb]

Thank you for considering my dilemma. Splitting the db is a solution that will make my job much easier. The end user is in another state and is a single chair that has limited ability when it comes to file manipulation within the Windows file system. My goal is to provide that user with an update that is as simple as possible.

I may have conceptually solved the issue by reversing it. Sending an Update.accdb file that can be placed on the users desktop and used to move all non-table objects, Modules, queries, forms, reports, etc. to the existing database. As you suggested the table fields can be appended as needed. This method keeps the db intact and the update process very simple, for the end user. (Not necessarily me.

Thank you again for thinking and writing about this.

 

[pbaldy]

Happy to help. Glad you found a solution that works for you.