Results 1 to 3 of 3

Thread: Automate saved imports by VBA code - setup to run every night
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Automate saved imports by VBA code - setup to run every night


    Is there a way to have a saved import run each night - a code that this do thise without opening the database?


  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Post Thanks / Like
    57 Post(s)
    11 Thread(s)

    Default Re: Automate saved imports by VBA code - setup to run every night

    I usually make a separate Access database that links to this other database (it really just is another version of the "Front End Database"), runs upon opening, executes code, and then automatically close.
    You can do that with an AutoExec macro (any macro named "AutoExec" automatically runs upon opening it), or by putting the VBA code in the Load event of a Form, and set that Form to be the Startup Form that the database opens to).
    Then you can use any Scheduling program to open the database at the time you want it to run.

    So it will open it at your designated time, run the code, and automatically close itself.
    I have used this method with great success many times.
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Ontario, Canada
    Post Thanks / Like
    2 Post(s)
    0 Thread(s)

    Default Re: Automate saved imports by VBA code - setup to run every night

    Isn't that like asking if Word or Excel can open a file without the application itself being opened? I'd bet my dog's life that it can't be done without opening Access in one way or another. You could use Task Scheduler to open the db at night, but your coding skill would need to be somewhat good and the pc running TS has to be left on. TS has an option to run a task whether or not you are logged in, but if IT/IS reboots the machine during the night, it would be a problem if a network login is lost, I think. When doing this, I used TS to activate a shortcut that contains a command line switch so that the db knows TS is opening the db. My startup code ran one way for TS and another way for a regular user.

    Are you sure the import needs to be done on a regular basis instead of linking tables to something?

    P.S. I don't own a dog
    Last edited by Micron; Apr 23rd, 2018 at 09:09 PM.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts