Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

Automating Imports from Access

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:

image

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:

image

Then you can save it with a name:

image

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:

image

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

image

image

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:

image

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:

image

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!

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.