Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Execute macro via command line (Access 2002)

    Would anyone know anything about this: I have created a macro whose sole purpose is to call a function, which in turn updates some data in the database. If I run the macro from the interface, the Function performs correctly. If I call the macro from a command line (I'm using the job scheduler), the scheduler reports that the job is running, but it just hangs. It will eventually time out according to the setting for that task. I know that the mdb file has been accessed because the ldb file has been created. I even used the build button to capture the name of the function just to make sure that I spelled it right.

    The function takes about 4 minutes to complete normally.

    Does anyone have an idea about this?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Execute macro via command line (Access 2002)

    What does the command line "command" look like? As far as I know, there isn't a parameter you can specify when starting an Access database that says run this particular macro - and I assume this is really a macro and not VBA. The only way to run a macro automatically when a database is opened is to name it AutoExec. On the other hand you can specify a start-up form that runs VBA code. Give us some more details and we'll try to clarify things for you.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    Thanks for the response, Wendell.

    According to the help file, a command line switch of /x macro starts and runs the specific macro. Have I misunderstood this?

    My command line: "C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" ddfile01dataddrMarketingMORPT_data.mdb /x mUpdateData

    mUpdateData is actually a macro, whose only statement is to call a function.

    My next step will be to add statements to my code to write to a log file in order to see where everything hangs up, and if it even gets to the function at all.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute macro via command line (Access 2002)

    No, you haven't misunderstood, but how is the macro calling the function? Are there any required parameters for the function that may not get passed when called from the command line? Remember that a called macro executes when the database opens. If there should be globals populated or any other preconditions that don't occur under those circumstances, the macro can fail because necessary things haven't happened yet.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    Just a thought. Is the Setwarnings set to no?
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Execute macro via command line (Access 2002)

    I stand corrected - I should have checked the Help file before I replied. Mea culpa! <img src=/S/woops.gif border=0 alt=woops width=58 height=36>
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    Hi Charolotte!

    Below I've included a screen of the macro--very simple. There are no parameters required for the function. And the function only calls a procedure in the module. It is this procedure that actually does any work.

    I do have quite a lengthy declarations section for this module. Would this be the "preconditions" you refered to?
    Attached Images Attached Images

  8. #8
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    Ok, I figured out that my problem was in accessing an external database referenced in my code. The reason it just hung was because it was waiting for a password. This was never a issue during development since my computer is automatically authenicated to this database. I moved the project over to a server to run it as a scheduled job overnight. The server does not automatically authenticate to this external data source (its an AS/400).

    So now I need to figure out a way to code in this connection. I will submit another thread if I can't figure it out. Thanks to everyone who submitted answers to this.

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Execute macro via command line (Access 2002)

    wouldn't you offer the /user and /pwd switches in the same line as the macro switch?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    Hey Catherine. Glad you could join the discussion.

    Actually, the /user and /pwd switches apply only to the mdb itself. I am going against an external ODBC datasource (an AS/400).

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Execute macro via command line (Access 2002)

    And the external datasource won't accept a username and password presented in your code? Not that I've done this of course, just wondering.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    I haven't figured it out yet. I'm sure it can be done, I'm just not sure how. I think that there is a way to save userid and password information into a DSN file.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Execute macro via command line (Access 2002)

    What is the external datasource? Are the tables linked to the datasource?

    If you are linking to a datasource via ODBC there is a provision to save the password via a checkbox on the dialog box where you choose which tables you wish to link. You only have to do this once then copy it to the server or wherever and it will not wait for the password to be entered.

  14. #14
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute macro via command line (Access 2002)

    Hey Pat. Thanks for joining the conversation.

    Yes, you are right. Clicking the Save password checkbox will overcome having to type in your password each time--ON MOST ODBC CONNECTORS. This is an AS/400 connector--and it is a little flakey. The Save password checkbox doesn't, and creating a file-based connection (*.dsn) is highly unstable. You have to use a machine-based connection, which really sucks if your app needs to be available on multiple desktops.

    I appreciate everyone's responses. Hopefully, someone will come up with a snippet of coding that will do the trick. I tried creating a ConnectionString, complete with UID and PWD, but didn't know how to tie it to an already linked table, OR delete the link with the table and create a ConnectionString that will connect to an existing AS/400 file.

Posting Permissions

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