Access is actually pretty good at importing files, and doesn’t on the surface work much different that import/export from Management Studio. I wrote this up after a friend had asked for assistance, I remembered the import specification part but couldn’t find at the time how to drive it from code. Finally dug through (it’s been a long time since I did this stuff) and got it to work.

For this short example I’ve created a two line comma delimited file, and then started the import:

You can see in the back window that it’s parsed the file and found the comma delimiter. The interesting part is what happens if you click Advanced (in green). That brings up the Import Specification dialog, which I think goes back to at least Access 97. You can tweak the import settings and then save them, maybe something like this:

Then you can save it with a name:

So now if you want to import the same type of file later, you can just select the import specification and you’re set. Very useful, not hard to learn. You get a prompt at the end (after clicking Finish), that lets you save the steps:

After clicking “Save Import”, you get the following task, with the “Run Import” on the ribbon:

Clicking the button doesn’t seem to generate any results for me. CSV is there, database is there, no changes, no error message. Ran the import again from within Access, got a prompt that the table already existed. Dropped the table, back to Outlook, this time worked fine. Not unexpected, just not entirely helpful.

You can re-run the import from within Access, clicking Save Imports bring up the list to pick from:

To automate further, you can create a module, create a function within the module, and then drop in code like this (it has to be a function if you want to call it from a macro):

Function ImportFile() As Boolean

DoCmd.RunSavedImportExport “import-test”

End Function

And you can tie it to a macro – even an autoexec macro – by just plugging in the function name:

In terms of complexity it’s not obvious how to do it all. I wish the import spec dialog had more options besides the Outlook Task – create code and/or create macro options would be nice.  Compared to Integration Services? Of course SSIS is far more robust, but at this level it seems like slightly different approaches with SSIS arguably more intuitive if you use the import/export wizard, probably not so if you build the package manually.

So there you have it, maybe you’ll find it useful one day!