run macro in another database

[BorisGomel]

Hello everybody,

I hope somebody helps me in my problem, and if this is a case, it would be much easy for me to do my job. I work with one very large database access 2003 called checks Clients. I do uploading data’s from excel files from big banks cap one, city and so on and process them accordingly their requirements. The problem I need constantly reconcile the data and in order to that I need to keep opening another large database called Files to look up that really slows my computer and takes time to jungle between large mdbs . I always run one select query there called Inquire to obtain information. I read about call shell function that allows running a macro in another database without opening it up. So I create macro called lookUp in Files mdb to start run Inquire query and placed a command button on form frmLooking in Checks mdb and put in click event following code:

Call Shell ("B:\checks Clients. EXE" & "B:\ Files.mdb"" /x Inquire ", 1)



When I run this code the Access shows error 53 no path was found.

I ensure that paths are correct.

May be somebody point me out what I am doing wrong.

Really appreciated,

BorisGomel

[June7]

Why do you need to run a SELECT query? Does the query have dynamic parameters? SELECT queries don't need to be run unless there are dynamic criteria. How does a SELECT query in Files help you get data needed in Clients?

[BorisGomel]

Yes , it has date and id of the bank that I need to look up. For examples, when query is running first it asks an user to type date in the format dd/mm/yyyy and then it prompts the user to type an id bank that can be from 1 to 19( we are working with 19 bank-cap one is 1,city is 2,hsbc is 3 an dso on) and then the query supplies data relaited to this bank and date whcih I need for recs .
Thnak you,
GomelBoris

[June7]

Still don't really understand how the query in Files provides data to Clients. But whatever works for you!

This is how I call procedure in another database:

Dim a As New Access.Application
Set a = CreateObject("Access.Application")
a.Visible = False
a.OpenCurrentDatabase "\\dotatufs02\Crm\PMS\airports\Report Production\GIS\Airports.mdb", False
a.Run "GetPaverData"
Set a = Nothing

GetPaverData is a public Sub procedure in a general module.

[BorisGomel]

Thank you June 7. I know this confusing for me too , but initially it was designed that all banks files have been appended in files mdb, and clients mdb is used just only to upload bank's files to print checks. Both Access mdb are very big and it is hard to change something here only just superficially.It works for the firm where I am working. I managed to open file mdb from client mdb and it is good. But I don't understand second part as
a.Run "GetPaverData"
Set a = Nothing

GetPaverData is a public Sub procedure in a general module.


When file mdb is opend I go to object query and run query "Inquire" .
It would be nice that once file mdb is opend , query "Inquire" is being strarted up.
But, It helps me a lot as it is .
Thnak you in advance,
GomelBoris

[June7]

That was example code for you to model as needed for your requirements. GetPaverData is a procedure in my database in a general code module. You would reference whatever procedure you want to run.

Set a = Nothing is just cleanup code, it releases the created object.

That last line was just a comment in the post.



Is query 'Inquire' a SELECT query? SELECT queries normally don't need to be 'run', as pointed out in post 2.

[BorisGomel]

Ok I got it .Thank you very miuch June 7. Learn a lot.
GomelBoris