I’m currently in the process of taking a long-running experiment with code and sharpening it up into something that we can include in the code-base proper. An intermediate step involves converting data from one format to another, which is stored in an Access database. When I was experimenting, I took a copy of the entire old database and hacked with wild abandon until I had something that looked like we’d get the gold with.
The database definitions for the relevant tables have changed enough that I don’t care to manually redo the changes in the old database when they were just sitting there in the new, ready to go. So, now I have a dilemma where I need both old and new table definitions in the same database for a time as I do the data conversion.
I initially thought it might just be as easy as “copy” table in one access database, and a “paste” into another. No dice (shame, Access has surprised me in the past in it’s ease of use with data dictionary goofery).
I then thought I could export a few tables to a new intermediate Access database, and import them back into the old. No dice (the intermediate needs to pre-exist first it seems).
I THEN thought I might be able to export the tables to Excel, and then import them back into the old. That kinda worked, from a raw data point of view. All but the field names of the data dictionary (like whether they were keys, their data type, etc) got clobbered. *pant pant pant*
Finally, I tried an export/import cycle via XML. All the table’s definition came through quite nicely thanks. *phew*
A limitation I need to remember for the future is that the XML export option is limited to a table at a time (circa Access 2000). I had to recreate my relationships on the import end, but for the task at hand, there was only a single relationship and something I was happy to wear.