MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

[MS Access 2003] Scheduled task for a VBA function

with one comment

Following my previous post, I found myself in need to schedule a MS Access VBA function to run once a day. On the internet we find plenty of articles recommending to launch the Access with the destination DB as parameter and the /X “nameofthefunction” switch.

In my case this did not work for a “cannot find macro” error.
Then what I did is create a macro that would call the function (use the “Run Code” method).
I came up with a couple of generic guidelines dictated by the various problems I bumped into:

1. In the Run Code method specify the function you are calling adding () at the end even if no parameters are expected (e.g.: myfunction())
2. Make sure your function does not have the same name as the module (in my case this caused confusion for the function call)
3. You MUST use a function not a SUB or it won’t work
4. Any warning at the start up of MS Access will prevent the scheduled task from running. For macros you can simply lower the security for Macros.
5. Make sure the references are correct in VBA. In our case the database is copied at regular intervals from another pc. Some DLLs were missing and some other were misplaced.
More on this: I had a weird problem: although I had referenced and installed the same DLL on the server and the client, when the db was copied over from the client, I would still get a “MISSING” DLL error (and the name and location were the same). It turns out that the “references” take into account not only the file name and path but even the file version (although not visible). After copying the referenced DLL over in the same location, everything worked.



Written by zantoro

November 18, 2013 at 2:19 pm

Posted in MS Access

One Response

Subscribe to comments with RSS.

  1. SutoCom

    November 21, 2013 at 1:04 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: