automating access macros on shared drives

[jess451]

I'm having a strange problem.

We started out with two databases (Financial and Client Info). The client info database needs to be updated with information from the Financial database weekly. Each database is stored on two separate network shares:

\\Server1\All Staff\Client Info
\\server2\finance team\Financial

To transfer the data, we created two queries. Each of the queries are make-table queries that take data from a table in the Financial database and copy to the Client Info database.

To help with the execution of the process we created a macro (Financial_Transfer) that executes the 2 queries.

When you’re logged in and run the Financial_Transfer macro it works. – All is good so far.

Now to do this automatically.

We created the batch file below:

"C:\program files\Microsoft Office\Office\MSAccess.exe" "\\server2\finance team\Financial.mdb" /x Financial_Transfer

The batch file is located on the same share as the Financial database.

We used the task scheduler to automatically run the batch file. If a user (with proper permissions) is logged on, the task runs as expected.

Great.

Now to Automate not based on a user being logged in. The This is where the process goes bad. It will continually hang in the “running” state. In addition, it locks the database (not exclusively).

Additional notes:
1. The only way the tables in the Client info database are updated is by this method.
2. W2k Workstation
3. Access 2000 SP3

Any ideas? Am I missing something? I thank you in advance.

 

[ptpaton]

maybe this isn't going to help, but couldn't you have just linked the tables from each respective database to the other? Then, build all of your forms/queries/reports on the linked table. That way, data can be updated in each database from either one?

Just a thought...

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[jess451]

I don't think so.

It's a "query" in the financial database that links to a "table" in the Client Info database. It's my understanding that it can't be done. Am I missing something.

The main reason to keep these databases as separate as possible is b/c the finanical one contains client revenue information which shouldn't be accessable by everyone (and everyone has access to the Client info database.

Does anyone else use the Access macro scheduler successfully?

Thank you in advance