locked
Saving imports in Access RRS feed

  • 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

    Tuesday, August 6, 2019 1:44 AM

All replies

  • 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



    Tuesday, August 6, 2019 4:46 AM
  • 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 

    Tuesday, August 6, 2019 12:20 PM
  • code in: docmd.DeleteObject

    before running the import

    to prior delete the existing tables

    Tuesday, August 6, 2019 8:48 PM
  • Where do you exactly insert the code in: docmd.DeleteObject?
    Tuesday, August 6, 2019 9:42 PM
  • 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....

    Tuesday, August 6, 2019 9:47 PM
  • I am doing it manually. So deleting the tables manually appears to be my only option.

    Thanks

    Tuesday, August 6, 2019 9:52 PM
  • 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

    Saturday, August 10, 2019 5:10 AM