Use a batch file to open Access 2010, run a query, and close Access?

Posted on 2014-08-14
Last Modified: 2014-08-18
Hello Experts.

I need to open Access 2010, run a query, and close Access.  I anticipate using Scheduler to run the batch file once a day.  If there is an easier, or better way to do this please feel free to impart you wisdom.

The macro is:
Private Sub autoMasterQry_Click()
DoCmd.TransferText acExportDelim, "", "masterQry", "F:\Folder\Master" & Format(Date, "yymmdd") & ".csv", True, ""
End Sub

Open in new window

The batch file is:
"F:\Folder\dbFolder\myDb.accdb" /X autoMasterQry

Open in new window

The query works as intended.  But when I attempt to use the batch file to execute the query I get the following error message after Access opens:

MyDatabase cannot find the object 'autoMasterQry.'
If 'autoMasterQry' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

Any help would be greatly appreciated.

Question by:ferguson_jerald
    LVL 14

    Accepted Solution

    Create a macro in to execute the query.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    That's not a macro, that's VBA code.

    IMO the best way to do this is to launch your database directly from a scheduled task, and then fire off the code you need. You could create a macro to do this, as Brad Groux suggests, or you could set a Startup Form in Access, and have this form run that code. The downside of the startup form is that the code would ALWAYS run when you start the database, so that may not be the best way to do it.

    You could also move that code to a Standard Module (it's obviously in a Form module now), and then create a macro that would call the new Function or Sub. For example, if you create a new standard module named basImport, and include a function like this:

    Public Sub ImportMasterQry()
      DoCmd.TransferText acExportDelim, "", "masterQry", "F:\Folder\Master" & Format(Date, "yymmdd") & ".csv", True, ""
    End Sub

    Open in new window

    You could then create a macro that calls that Sub - let's say you name the macro "mcrMasterQuery". You could then use Scheduler to run that query:

    "full path to msaccess.exe" "Full path to your database" /X mcrMasterQuery

    This is known as a "command line switch". See this article for more information on that:

    One thing to remember - even if you're specifying a Macro to run via the command line, any code you have in a startup form, or in an AutoExec macro in your database, will run when you fire this off from a scheduled task.
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Check out the following Micro Tutorial:

    MS Access – Different Ways to Start Up a Database

    everything there has already been mentioned, but it might help if you see it in a different way.


    Author Closing Comment

    Thanks for the assistance.  I created a macro to launch the code, and it worked as intended.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now