OPen and control of another mdb...

[hzgt9b]

I'm trying to have the VBA code module(s) in one MS Access mdb open and
execute some code in another mdb file. Assuming that I can run the remote
Sub, wll I be able to set some variables so that when that code runs it picks
them up?

Any help or pointers would be appreciated...

 

['69 Camaro]

wll I be able to set some variables so that when that code runs it picks

them up?

That depends upon a number of things. Are you an expert coder? If not,
have the procedure save the values in a table where you can easily pick them
up from the other database.

Generally, what you have in mind?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Ofer]

You can create a reference from MDB1 To MDB2, that way MDB1 will recognize
all the functions that are written in MDB2 modules only.

So, if you want to open a form in MDB2 using MDB1, create a function in MDB2
that open this form, and then run this function from MDB1, after you add a
reference to MDB1.

To add a reference, open the code anywhere in MDB1, select tools > refernce
in the menu bar, and add MDB2 using the browse

 

[hzgt9b]

I have a couple of existing mdbs with existing code modules (subs and
functions) and data. I am developing code in another mdb that needs to
programatically make a copy of one of the other mdbs (call it mdb2) based on
some condition, then call some Subs and Functions in mdb2. Before calling
mdb2's Subs and/or Functions I will need to set and/or some assign some
variable values in the mdb2 so that the called code can execute properly. I'm
trying to limit my impact to the existing mdbs - so moving the variables into
a table is not desirable if possible (I could take that path as a last ditch
effort)...

Here's a mix of code and pseudo code that I need to implement in my new mdb:

Public Sub SomeSub()
On Error GoTo Error_Handler
Dim mdb2 As Database
Set mdb2 = OpenDatabase(FullyQualifiedMdbName)
'<!----------------------------------------------------------------
'1. Set mdb2 variables
'2. Call mdb2 Subs or Functions
'----------------------------------------------------------------->
mdb2.Close
Set mdb2 = Nothing

Exit_Routine:
Exit Sub
Error_Handler:
'TODO handle exception here...
Resume Exit_Routine
End Sub

 

[hzgt9b]

Excure the double post, I posted my other message too early.

What I don't know how todo is make the sub or funcation call in mdb2... Is
there an example you can point me to on how todo this?

 

[hzgt9b]

I don't want to open any forms in MDB2, I just want to execute some of the
Subs and Functions. What's the VBA syntax for accomplishing this?

Is there a way to call MDB2's module code w/o adding the reference. If not,
I'll need to programatically add the reference... any pointers on how to do
that also?

 

[Ofer]

When you create a reference to MDB2 any function in it, you can call it the
same way you call a function on the current mdb.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

['69 Camaro]

The easiest way is to set a library reference and use the public procedures,
but if you have an aversion to that, then you could open the other DB and run
a macro that runs a public function that reads your variables you've stored
in a table in the other DB. If storing the data in a table is not possible,
then you could try something like this:

Public Sub execFnInOtherDB()

On Error GoTo ErrHandler

Dim accApp As New Access.Application
Dim frm As Form

Set accApp = CreateObject("Access.Application")

accApp.OpenCurrentDatabase ("C:\MyDB.mdb")
accApp.Visible = True ' For test purposes
only.
accApp.DoCmd.OpenForm "frmSetVars"
Set frm = accApp.Forms!frmSetVars
frm.txtBestCar.Value = "Camaro"
accApp.DoCmd.RunMacro "mcrRunMyFn" ' Macro runs public function
' that reads
text box on form
' for further
processing.
Set frm = Nothing
accApp.Quit

CleanUp:

Set frm = Nothing
Set accApp = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in execFnInOtherDB( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

The macro, mcrRunMyFn, runs a public function that reads the value in the
text box on the open form. You would need to define the public function,
name and all. For testing purposes, set the accApp.Visible = True, but leave
this out during normal operations, because the user doesn't need to see
what's going on.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

I just now had time to test a better idea. One could call the form's public
procedure (once the form is open in the other database) to run other public
procedures. The syntax would be easy. Try:

Public Sub execFnInOtherDB()

On Error GoTo ErrHandler

Dim accApp As New Access.Application
Dim frm As Form

Set accApp = CreateObject("Access.Application")

accApp.OpenCurrentDatabase ("C:\MyDB.mdb")
accApp.Visible = True ' For test purposes only.
accApp.DoCmd.OpenForm "frmSetVars"
Set frm = accApp.Forms!frmSetVars
frm.txtBestCar.Value = "Camaro"
frm.MyPublicSub ' Form's public Sub runs other public
' procedures that read text box on
' form for further processing.
Set frm = Nothing
accApp.Quit

CleanUp:

Set frm = Nothing
Set accApp = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in execFnInOtherDB( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

.. . . where MyPublicSub is a public subroutine in the frmSetVars form's
module in the other database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.