Run macros in different database

[Kinger43]

I am working with three different databases, all of which work together in certain areas, normally through linked tables. One database gets updated via csv files and the other databases get backed up primarily via this database. I am trying to automate the update process. I run the primary database update first and then I need to call macros from the other two databases to run and update the other databases. My thought was to call all of the macros from one master macro (or code) that would run daily and update the databases. In short, I am having trouble calling the macros in the secondary databases to run from the primary database.
I managed to accomplish this using the RunApp macro action but because it doesn't pause I run into a bunch of errors. Is there a better way to do this?

 

[KenHigg]

Is there a better way to do this?

Loose the macros and do it with code - ?

 

[Kinger43]

I'm not going to change the macros in the secondary databases to code; I didn't write them and I'm not the only one who has to use them.

 

[KenHigg]

I'm not going to change the macros in the secondary databases to code; I didn't write them and I'm not the only one who has to use them.

I suppose that like choosing the lesser of two evils then -

 

[Kinger43]

I suppose, but it still doesn't help me with my problem. I have experimented with making shortcuts to the macros in a folder and calling them. application.followhyperlink won't run them.

 

[KenHigg]

Just curious, how many macros are you attempting to run and how complex are they?

 

[Kinger43]

There will be a total of 6 macros that will run, 1 is in the primary database and it is fairly complex, but it is not a problem to run. Two of the other five are decently complex, they update tables and call batch files to move information around to and from an oracle database. The other three just run queries. I'm considering making one of them an AutoExec and nesting the other macros.

 

[Kinger43]

I have gotten application.follow hyperlink to run the macro shortcut I have created. The problem now is that Microsoft office is throwing up warnings about viruses in files like this. Is there some way I can turn these off?

 

[KenHigg]

Have you tried the SetWarnings False thing?

 

[Kinger43]

Yes, I tried to SetWarnings False. It's not an Access warning, it is an office warning, it goes deeper than the code that I am running.

 

[Kinger43]

I tried writing a module in one of the secondary databases and referencing the database in the primary database and calling the module from there. It then gave me an error on the call function that said it was expecting a variable or procedure, not a module. What does this mean and how do I make it procedure?

 

[KenHigg]

Hum - Don't know. Can you post the code you wrote?

 

[Kinger43]

Option Compare Database

Public Function Runmacros()
    DoCmd.RunMacro "Daily AS400 TNS Update Macro"
End Function

This is the secondary database code, it just calls the macro

Function Update()
    Call Runmacros
End Function

This is the primary database code that calls the other function.

 

[KenHigg]

If you can allow me to digress a step or two; From one .mdb you can open and do stuff directly in the second .mdb. Are you doing this?

 

[Kinger43]

Yes, I can open the secondary db from the primary, its the doing stuff that I'm having trouble with.

I figured out this most recent problem. I had the module named the same as the function. I'm not much of a programmer, that is poor practice I guess.

 

[KenHigg]

What is the code you are using to open the second database?

 

[Kinger43]

I'm not, I have just referenced the secondary db and called the function. If I run the function in the secondary database by itself it runs fine; if I call it from the primary database it seems to get things out of order and hangs up. I'm going to try opening the secondary database with followhyperlink first, then procede to call the function.

 

[Kinger43]

Ya, that made things worse.

 

[Kinger43]

I found the solution, but haven't been able to perfect it. I am calling the macros from a batch file and setting up the task scheduler to run the batch file. If I run the batch file manually while I am sitting there it runs fine. It ran fine from the task scheduler for a couple of days, but lately it has been giving me this error:

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user,m or you need permission to view its data.

It gives me this error in the middle of a macro, I don't understand why the file string is empty, and I know the database isn't being used by any other user.