Call subroutine that's in another database

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

I have a form in 1 database that needs to run a couple subroutines that are
in another database. Is this possible? The other database (someone else
created) is quite complex so it's not even an option to copy all the
subroutines & modules, etc into my current database. Right now my users
has to click a button in the current database for it to do certain things,
then has to open the other database & click a few buttons in it. I would
like to automate this so my user only clicks 1 button in the current & it
runs everything behind the scenes.

BTW I'm using Access XP

TIA
Connie
 
Connie said:
I have a form in 1 database that needs to run a couple subroutines that are
in another database. Is this possible? The other database (someone else
created) is quite complex so it's not even an option to copy all the
subroutines & modules, etc into my current database. Right now my users
has to click a button in the current database for it to do certain things,
then has to open the other database & click a few buttons in it. I would
like to automate this so my user only clicks 1 button in the current & it
runs everything behind the scenes.
Click to expand...


You can get to the public procedures in another database the
same as any library by adding the other file to your
References. Even though maintaining a reference may be more
trouble than it's worth, that has always been adequate for
my needs.

If you don't want to use a reference, then look into using
Automation to run the other file.
 
Hi Marsh, I had tried that a couple of days ago & for some reason didn't
work - got an err msg so I figured that couldn't be done, however I did try
it again just now & it's working.
Thanks!!

Just a couple more questions ....
1) what do you mean by "Even though maintaining a reference may be more
trouble than it's worth" ... what kind of trouble could I be setting myself
up for

2) I was having a problem with a piece of code in the other db that is
opening a recordset ... the table is not linked - it actual resides in that
other database. This code works find if I run it from that database, so I'm
sure it's because of the connection (see code below) because when I check
the Data Source portion of it, it's referring to the 1st database &
therefore couldn't locate the table. My question is, how do I set the Data
Source portion of the connection to the other db while that code is running.
In the meantime, I moved those tables to my current db & it seems to work.
I would just like to know how to set a connection other than
"CurrentProject".

Thanks again for your help & suggestions!
Connie

*******
Public cnn as New ADODB.Connection (in 1 module)
Private rs as New ADODB.Recordset (Declaration section of the module that's
running the code)

Sub UpdateInfo()
Dim strSQL As String

set cnn = Nothing
set rs = Nothing
set cnn=CurrentProject.Connection
strSQL = "SELECT * FROM tblStores"
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
** rest of code **
end Sub
********
 
Connie said:
Hi Marsh, I had tried that a couple of days ago & for some reason didn't
work - got an err msg so I figured that couldn't be done, however I did try
it again just now & it's working.
Thanks!!

Just a couple more questions ....
1) what do you mean by "Even though maintaining a reference may be more
trouble than it's worth" ... what kind of trouble could I be setting myself
up for

2) I was having a problem with a piece of code in the other db that is
opening a recordset ... the table is not linked - it actual resides in that
other database. This code works find if I run it from that database, so I'm
sure it's because of the connection (see code below) because when I check
the Data Source portion of it, it's referring to the 1st database &
therefore couldn't locate the table. My question is, how do I set the Data
Source portion of the connection to the other db while that code is running.
In the meantime, I moved those tables to my current db & it seems to work.
I would just like to know how to set a connection other than
"CurrentProject".

Thanks again for your help & suggestions!
Connie

*******
Public cnn as New ADODB.Connection (in 1 module)
Private rs as New ADODB.Recordset (Declaration section of the module that's
running the code)

Sub UpdateInfo()
Dim strSQL As String

set cnn = Nothing
set rs = Nothing
set cnn=CurrentProject.Connection
strSQL = "SELECT * FROM tblStores"
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
** rest of code **
end Sub
********
Click to expand...


I don't do ADO, but I suspect the problem is that you are
using CurrentProject. The way I read Help, that refers to
the database that is running, not the library. There seems
to be a CodeProject object that would refer to the database
that the library code is contained in.
 
Thanks for the fast reply. I would think there is a way to identify the
correct connection, just not sure how to go about doing it, but I'll
definitely check out the CodeProject object.

Thanks again Marsh for all your help!!
 
Back
Top