Execute macro via command line (Access 2002)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Just a thought. Is the Setwarnings set to no?

Reply | Quote

Just a thought. Is the Setwarnings set to no?

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

wouldn’t you offer the /user and /pwd switches in the same line as the macro switch?

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

wouldn’t you offer the /user and /pwd switches in the same line as the macro switch?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I stand corrected – I should have checked the Help file before I replied. Mea culpa!

Reply | Quote

I stand corrected – I should have checked the Help file before I replied. Mea culpa!

Reply | Quote

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.

Reply | Quote