Run VBA in another database via ADO

[Sandy]

I have to fire a module that is located in an unopened database when a
certain event/criteria is met in a front-end database that the user
initiates. I cannot seem to get the following to recognize the module!
Dim appAccess As Access.Application

' Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")

' Open WizCode database in Microsoft Access window.
appAccess.OpenCurrentDatabase "S:\CDT\Sandy\ShutdownTest.mdb", False

' Run Sub procedure.
appAccess.Run "TrackLogins", "LoginTableUpdate"
'appAccess.CloseCurrentDatabase "S:\CDT\Sandy\ShutdownTest.mdb", False
Set appAccess = Nothing

This line: appAccess.Run "TrackLogins", "LoginTableUpdate"
is the name of the procedure (set in the properties of the procedure) and
the LoginTableUpdate is the name of the Sub.

Please advise on the syntax - Also, I do not want to actually have the
database OPEN, I just want to create an ADO connection to it and run the
module from the front end database. It seems my code is still opening the
database.

Thanks for your advise,

 

[Nick 'The database Guy']

Hi Sandy,

Set a reference to this database, then the all you have to enter is the
name of the procedure (LoginTableUpdate).

To set a reference you must goto the VBA window, >>Tools, >>References
push the browse button, then select 'Microsoft Access Databases(*.mdb)'
from the file type and browse to your backend database.

Hope this helps.

Good luck,

Nick

 

[Sandy]

Hi Nick,

I don't want to run the module in the open database. I need the module to
run in the database that it is in. I need to open that database in code, run
the module, and close the connection.

Is this possible through ADO?

Thanks for your help,

 

[Nick 'The database Guy']

Is it the back end database that you want to run this module on?

 

[Sandy]

Hi Nick,

Thanks for helping - yes, kind of...

Here's the scenerio:

I have users in 5 locations. Based on the data in a specific table from each
of those locations, I need to run a procedure in a centralized database (the
tables are linked into the centralized database).

When an end user in any of the 5 centers performs an event that triggers the
appropriate event I need the database to open the Central database in code,
and run the procedure in the Central database, thereby, updating all 5
locations linked tables.

I know this sounds odd - and it is. However, this is the only solution we
can come up with to simplify an already complex project.

Here's what I have now...

Dim Cnxn As ADODB.Connection

strCnxn = "Provider='Microsoft.JET.OLEDB.4.0';Data
Source='\\cria-data1\Shared\SourceFile\ADS.mdb';"

Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

Cnxn. Do Something to run the module in the opened database

Cnxn.Close
Set Cnxn = Nothing

I just need to know how to run the procedure.. I am looking at stored
procedures right now but those are mainly for SQL queries...

Thanks again for your help!

Sandy

 

[Brendan Reynolds]

The answer to your question can you execute VBA code via ADO, Sandy, is no.
ADO is for accessing data, not for executing VBA code.

However, which file your code is located in, and which database the data
upon which your code acts is stored in, do not need to be related. If you
need the code to operate on data in different databases, you can simply pass
an argument to the code to tell it which database to use. For example,
here's some code that I posted in response to a different question, modified
to accept a connection string as an argument in place of the original
hard-coded connection string. This code can now work with any database that
contains a table with the expected name and structure ...

Public Sub TestGetId(ByVal ConnectionString As String)

' The original code used the following constant, now commented out ...
' Const strcConnect As String = "Provider=SQLOLEDB.1;" & _
' "Integrated Security=SSPI;" & _
' "Persist Security Info=False;" & _
' "Initial Catalog=Northwind;" & _
' "Data Source=(local)"

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = ConnectionString '<- using passed in parameter
here
cn.Open
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM Categories", cn, adOpenKeyset, _
adLockPessimistic
rst.AddNew
rst.Fields("CategoryName") = "Test"
rst.Fields("Description") = "A Test Category"
rst.Update
Debug.Print rst.Fields("CategoryID"), _
rst.Fields("CategoryName"), rst.Fields("Description")
rst.Close
cn.Close

End Sub

 

[Sandy]

Thanks Brendan! I don't know why I was thinking I could do this in ADO. What
I ended up doing is running some simple shell script to execute a macro,
which in turn fires off a function that calls the procedure I need to run.

There's always more than one way to slice it with Access

Thanks again!

Sandy