Transferring a Microsoft Access Table definition between Databases

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.

Advertisements

One response to “Transferring a Microsoft Access Table definition between Databases

  1. >For those having to deal with Access on a day to day basis, Jebus pray for us.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s