Running VBA Code sitting in another DB from current DB 1

[airon]

I posted a topic about a week ago and thought I had my sollution and in part I did, but not total.

I need to run code or call some code in another DB while sitting in my exsiting DB. I was advised to do this by refrencing the other DB. This I have done and it works, except it runs the VBA code sitting in the other DB in my current exsisting DB.

What I need to do is call a procedure in the other DB and just have it run the code in it's own DB.
Is this possible?
If not does anyone know any back doors to make this possible?

Thanks in advance.
airo

 

[christheprogrammer]

I would say that you'd have to replace all instances of 'CurrentDB()' in the VBA code to the database name you'd like the code to run on. A little ugly, but I think it may work.
Cheers Chris

 

[MichaelRed]

I do not understand what you mean. Particularly by the part " ... run the code in it's own DB ... ". What is the code supposed to "do" in the "other" db? what objects are supposed to be afected? Where do they 'reside'? Can you provide an example expliniation?
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[airon]

futher explanation:
I have Reporting DB that sits on a network drive. In order to run this DB in efficient time conists of the following:

In VBA Code:
* compacting DB to local drive
* once I have it on local drive I simply need to start the
DB running.
* The Db creates and deletes 250 queries in process of
running, then sends them to excel for formating and
completion.
* Reason I am using a 2nd DB is because I have to compact
the the original DB to the local drive for efficent
running. (this I have to do while the DB is closed).
-- 2nd DB I am only using because I don't know of any other
way to achieve this process.

* What happends when I reference the Reporting DB in the
2nd DB is:
The 2nd DB runs all code in the Reporting DB, but it does
it in its own DB, causing errors etc...

I hope this clarification has clarified, please respond again.

 

[MichaelRed]

Well, I have never tried this, but you SHOULD be able to cobble together the RunCommand instruction (acCmdOpenDatabase)?. This is NOT what I would recommend, however.

In the general sense, using data on a server should be accomplished with a "Split" database. Place the data segment of the app on the server and the remainder on the local drive.

Unless there is a LOT of data or activity, there should not be a need to compact the db each time reports are run (although it is a useful periodic maintenance activity).

You would normally link the data tables (on the server) to the reporting application (on the local system). If the server is set up for the nattering nabobs of e-mail and chat, AND the datasets are quite large, this process will often not be pratical. In these instances, you can either convince the net admin to change the network settings to be more favorable to the db uses - or copy the data to a local drive - and reference it from there.

Even in the latter approach, it is seldom appropiate to copy the entire MDB file from the server to the local drive. It will, of course, vary with the details of the application but the more common process would simply update the local tables from the server tables via queries.

In any of the above, You would still have a "Split" database with two copies of the data segment (one on the server and one on the local machine) and one reporting segment on the local system. the local system would have links to the local copy of the data segment and include the code which 'updates' the local copy of the data segment from the server copy.

There are many different approaches to 'getting' the data from the server for the use of the reporting process. In the above, I have briefly mentioned a few, so there are others which may be suggested. My suggestions (above) are not intended or expected to be restrictive or even the 'best' soloution, but only to highlight the issue of not needing to copy the entire database just to run repots.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[rgbanse]

airon,
This may or may not be your solution.

Create a table with all of the code you need for "Common" calls procedures, functions etc. Save it in a meaningful or common directory with a unique name identifying it as a "Library" database.

Open your working database, open a new or existing module in design view. Next, on the menubar, click Tools,References. This will open a dialog window. Click browse, set type as MS Acess Database, and select your "Library" database. Click Ok then close and reopen your working database. After reopening, all calls made to "Library" Public Functions will run as if they were resident in your working database.

The biggest advantage to this type of setup is that all changes made to the "Library" automatically updates any working databases referencing it.

Hope this helps.
RGB

 

[NormDuffy]

* Reason I am using a 2nd DB is because I have to compact
the the original DB to the local drive for efficent
running. (this I have to do while the DB is closed).
-- 2nd DB I am only using because I don't know of any other
way to achieve this process.
If all you need to do is compact the database you can do this with a simple command line paramater from a batch file or modify a shortcut. All you need is path\filename / comapct. If you want you can call this batch file as a scheduled task or run it manually or call it with code.

 

[rgbanse]

Airon,
Excuse the Faux pas above. You should create Modules, not tables with your common Functions and procedures and save them in a "Library" database.
RGB