Saving imports in Access

Question

Hello, we have setup importing from an SQL database to MS Access and this works successfully. It provides the option to Save the Import for that session. The MS access database will need to be updated periodically to get the latest tables. Is there a way to access the saved import when opening a new database?

Thank you

Answer 1

Hi Gladiator

Yes there is. Please look at "Run a saved import specification"

https://support.office.com/en-us/article/run-a-saved-import-or-export-specification-6969e099-4573-40f0-a1e0-f3042bddb640

In a module, run at start up:

docmd.RunSavedImportExport "name of saved import/export"

---

Cheers // Peter Forss Stockholm

Answer 2

Thanks Peter

I have tried this but the problem with this is that I need it to overwrite the current tables. It will only import another set of updated tables and it adds a 1 at the end of each table name leaving me with 2 sets. if I run the saved import again, I will get a third set of tables with the 2 at the end of each table file name.

I need to start fresh with a new set imported tables.

Thank you 

Answer 3

code in: docmd.DeleteObject

before running the import

to prior delete the existing tables

Answer 4

Where do you exactly insert the code in: docmd.DeleteObject?

Answer 5

well sequentially it needs to occur before the import so you don't get that '1' appended to the table name...

not sure how you are doing the import; if it is via an event with code - then it would be at the top of that code stack....so it fires before the import...

if you are doing the import via manual use of the features in the ribbon - - then you don't need code - - you would go to the navigation pane and manually delete the existing table objects....

Answer 6

I am doing it manually. So deleting the tables manually appears to be my only option.

Thanks

Answer 7

Hi Gladiator

Try this:

Make a push button on a Form
Create a macro (or VBA code) conneted to that pushbutton (Event On Click)

VBA code:

    DoCmd.DeleteObject acTable, "TheTableNameIWantToDelete"
    DoCmd.RunSavedImportExport "MySavedImpSpecification"

Example of the macro, please see pic below.

---

Cheers // Peter Forss Stockholm