How do I automate a saved import in a macro in Access 2007?

Question

I have created a macro to run a saved import (a file imported from Excel into Access 2007). When I run the macro, the saved import runs and the following happens:

1. A "Manage Data Tasks" window pops up.
2. I have to choose from the following options: Run, Create Outlook Task..., Delete, and Close.
3. If I select Run, the next pop-up asks, "Overwrite existing table or query?"
4. I have to choose: Yes or No.
5. Another window pops up and says, "All objects were imported successfully."
6. I have to click the OK button.
7. I have to click the Close button on the "Manage Data Tasks" Window.

Is there any way that I can automate all these button clicks if they are always going to be the same? (The bold faced buttons are the ones that I'll always be clicking.)

Solution

If you use the DoCmd.RunSavedImportExport Method you won't have to interact with the Manage Data Tasks dialog at all.

   DoCmd.RunSavedImportExport "YourSavedImportName"

You will however have to deal with the possible errors that get raised. For example if your source file is unavailable you will get an error like

Run-time error '3011':

The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure the object exists and the you spell its name and the path correctly.

Comments

[Arun Raja]

When I do this i get a new table it does not get appended to the same table.

[Conrad Frix]

You'll have to recreate the Saved Import and choose Append a copy of the records to the table instead of Import the source data into a new table in the current database

[Arun Raja]

I am getting only two options to download the whole table or link the table for odbc database. I nned to get the values from mysql database.