How to invoke external access VBA codes?

C

coolfa

Hi everyone, good evening. I am wondering if I might ask some favor
here. I have a question related to invoke another access VBA program in
one access VBA code. The detail is addressed as follows.

Now I already have an access .mdb file (say, AA.mdb) which included a
lot of forms, queries and corresponding VBA codes. This is access file
allows users to do some actions, for example, make some selections and
choose some tables and variables, and accordingly it generates some
results. Now I want to make part of these users' actions automatically,
but I cannot add codes into this access file, so I think I have to make
another access .mdb file (BB.mdb) and invoke this AA.mdb in BB.mdb. (Am
I right?)

The question is I cannot find how to invoke AA.mdb and run its codes in
another access file (BB.mdb). Would anybody nicely help me out here?


Thanks a lot. Have a good evening.

Best
Coolfa
 
R

Rod Plastow

Hi coolfa,

I used to do something similar but I don't think it will completely fulfil
your requirement.

ADO (can't remember about DAO - very rusty) provides for a connection string
object for all IO operations. Thus AA.mdb can open BB.mdb and vice versa as
long as all the privileges and permissions are OK and the paths exist.

However, and it's a big however, Access does not (to the best of my
knowledge) provide you with a collection of procedures inside a module or any
method of executing a foreign procedure. You can step through the code lines
one by one and even insert, delete and modify the code lines. You can open
tables and manipulates the data.

So, if all your execution is within your source mdb and all you want to do
is manipute the data or metadata of your target then you can but I don't
think you can invoke code execution in the target mdb.

No doubt someone will prove me wrong.

Regards,

Rod
 
R

Rod Plastow

On second thoughts I not even sure you can manipulate the metadata of the
target. I can't think how you would declare the objects and collections.

Rod
 
R

Rod Plastow

Third thought: can you instantiate another Application object and use the
OpenCurrentDatabse method to gain access to BB.mdb from AA.mdb?
 
C

coolfa

Hi Rod,

Thank you so much. You are really nice. I guessI have to modify codes
in the original mdb file since the functions on another mdb file cannot
be called in one mdb file.

Btw, would you please tell me, if it is not against any rule, how did
you do in your project?

Thanks again. I appreciate it.

Best
Coolfa
 
R

Rod Plastow

Hi coolfa,

Flattery of course gets you everything! First let me describe what I was
doing.

I run a small business from home and home is often 4,500 miles away. The
business is controlled by an Access application that I wrote but like all
self-use applications I did not bother to construct the myriad of functions
needed to cover every eventuality but relied on the facility to get in there
and alter the data directly. Well, being far away I needeed a way for my wife
to make these data manipulations and so I wrote stand-alone Access
applications that used the Connection object to retrieve and manipulate the
data in my business application. My stand-alone application would, after a
successful execution, modify its own code to prevent my wife from running it
again. I used to email these stand-alone applications to my wife (and there's
another story because Outlook Express et al think you're distributing a
virus!).

However if I understand correctly you need to execute code in your target
application, not just in your source application. I tried a little test with
the following sub procedure in Test1.mdb

Private Sub testaccess()

Dim myApp As Access.Application

Set myApp = CreateObject("Access.Application")
myApp.OpenCurrentDatabase "C:\Test2.mdb"
myApp.Visible = True

End Sub

and (why should I be surprised?) it works. Through the object myApp I now
have access to all the objects in the target application (i.e. Test2) from
Test1. This may be appropriate to your situation.

How proficient do you think you are with VBA (OOP) and the Access object
model? If you want some additional help then we could take it offline and
save boring all these other posters to death.

Regards,

Rod
 
C

coolfa

Thank you so much Rod. Sorry for late response since I was out of town
last week. I really appreciate your detailed information. Unfortunately
I am just a newbie on Access, so I am afraid I might be of no use to
you. Anyway, the best wishes to your business.

Have a great week.

Coolfa
 
Top