This module is pretty simple, but I must be missing something

[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&quot

DoCmd.RunMacro ("mcrDailyShipmentInformationUpdate&quot

End Function

 

[JPeters]

Problem is it's looking at your current database for the macro, not the target DB.

Try this:

With DB
DoCmd.RunMacro ("mcrDailyShipmentInformationUpdate&quot
End with

Or something like

DoCmd.RunMacro (Db.mcr....etc) .. Im not sure if this works. But Now you know your problem. You'll get it. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[scking]

This issue is somewhat confusing and not very well documented. Josh is absolutely on target but I'm not sure the "With Db" will work. What I generally do is to use VB code rather than macros because, since you can't do any real error checking in a macro it could fail and your client application may not even be aware of it. Also, VB code has a mechanism for handling these types of situations. When you set the database within the procedure you would use the CodeDb object like this:

Dim db As Database
Set db = CodeDb

This forces the code to look within the server database for any database or object type references using the db object.

If you open the remote database there are quite a few methods you can use directly from the client database. Create a database object and look at all the methods using IntelliSense or use the browser on the database object. Also, remember you can build the code on the server database that uses CodeDb and will then reference the objects on the server.


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[jfgambit]

scking
Can you show me what the full code would look like with the Set db = Codedb added in. I'm afraid this addition confused me. You were correct that the With statement would not work...

Thanks.

 

[JPeters]

Eh, I figurd it was a long shot. But atleast we pinpointed the problem. [laugh] ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[scking]

It involves creating a library or add-in which is NO SIMPLE THING. I've done a few and it involves quite a bit of effort. If you have any alternatives I would recommend you try them. If not you will get a tutorial on libaries, library calls, and creating an Add-in. What you must have before pursuing this solution is 1) developmental control over both databases 2) local or LAN access in directories where you have permissions. What you would need to do in the library is build a USysRegInfo table with the appropriate entries, rename it with a .mda extension. Well, more if you are interested.
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------