Can I programmatically get at all Import/Export Sp

Question

I am working reverse engineering an MS Access 2010 database in order to recreate it as an SSIS/SSRS solution. There a bunch of import and export specifications that I would like to document without resorting to a bunch of screen shots.

With the assistance of this blog post I was able to get some XML for some of the imports and exports.

Unfortunately, I am only getting the imports and exports (eight of them) found when clicking on External Data > Saved Imports...

I have another 26 import specs that I can get to when I click on the Advanced button from within the Import Text Wizard dialog box and then the Specs button in the Import Specification dialog box.

How can get XML for the specs embedded in the Import Text Wizard like I can for those under Saved Imports? Can I transfer them to Saved Imports somehow? Or is my only option to load each spec within the Import Text Wizard and take a screen shot of it?

Thanks,

Noel

Answer 1

These Import/Export specifications are stored in two system tables: MSysIMEXSpecs and MSysIMEXColumns.

To display them:

• Right-click on the header in the navigation pane and choose Navigation Options...
• In the Navigation Options window check the Show System Objects box and click Ok

(courtesy of https://superuser.com/questions/268077/how-do-i-view-system-tables-for-a-mdb-in-access-2010)

Or create a query like this:

SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.*
FROM MSysIMEXColumns INNER JOIN MSysIMEXSpecs 
     ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID