Pretty simple...but I must be missing something (1 Viewer)

[jfgambit]

I have the following code that is supposed to open up a database on the server and run a macro to update the information in the tables. I keep getting a "Cannot find the macro 'mcrDailyShipmentInformationUpdate' error.
I know the macro exists...I am looking at it (and it is looking back at me with a childish smerk)

Any ideas??

Function UpdateDB()
Dim db As DAO.Database
Dim ws As DAO.Workspace

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("J:\Transportation\On Time Reports\testop.mdb")

DoCmd.RunMacro ("mcrDailyShipmentInformationUpdate")

End Function

 

[hqengint]

I believe the problem lies in where VB is looking for your macro. Most likely, the program is looking for the macro in what ever database the code is written in, rather than the one that is being opened. You may want to try and include a path in the macro name (not sure this is possible), or placing the macro in the current database (this may require some code alteration). I hope this helps.

 

[jfgambit]

I had already tried changing the Do.cmd line to:

do.cmd.runmacro (db.mcr...)

but that didn't work either.

Additionally, I tried adding the following:

With DB
DoCmd.RunMacro ("mcrDailyShipmentInformationUpdate")
End with

But it didn't work as well.

Here is what in essence I am trying to accomplish:
I need to update the records in a database every morning at approximately 1:00 AM. I can just call my macro AutoExec (in the DB on the J:\ drive) and set up a Scheduled Task to open the database everyday, but my problem is the fact that there are a number of users and I just know someone is going to open it without by passing the AutoExec (SHIFT key) and wipe out thousands of records.

I felt the easiest thing to do was create a seperate DB on my computer with an AutoExec Macro that ran the above code, opened the database on the J:\ drive and ran the necessary updates. Then I could add a Scheduled Task on my personal computer to open the DB.

If anyone has any better options I am open to suggestions.

 

[Drevlin]

Ok this is just off the top of my head... but...
Couldn't you create a seperate database for the users that only has linked tables to the original database? Then you could run your macro on the original without worrying that the users will mess it up...

Drevlin