Execute Access Macro from Excel?

  • Thread starter Thread starter Larry Adams
  • Start date Start date
L

Larry Adams

From Excel, I would like to execute a predefined Access macro to run a
series of 20 or so queries. But I am not finding a good reference.

My current Excel VBA code is illustrated below. It works, but, the problem
is, anytime I enhance the query set on the Access side, I need to also
change and mimic in Excel. I'd rather just have Excel run the predefined
macro in Access. Any thoughts?

Thanks in advance. Excel and Access are both 2002 SP2. Larry

================================================
Sub Update_Access()
Dim db As Database, rs As Recordset
Dim path As String
'
Worksheets("MODEL").Select
path = Worksheets("MODEL").Range("PATH") & "MODEL.mdb"
'
Set db = OpenDatabase(path)
db.Execute "PROD A100 - CLEAR TABLE"
db.Execute "PROD A110 - APPEND RECS"
db.Execute "PROD A120 - UPDATE 1"
db.Execute "PROD A130 - UPDATE 2"
...
...
db.Close
'
End Sub
 
1) I like it better the way it is. Better to keep your code just on one
side.

2) To run macro's, you need an Access Application reference instead
of the DAO reference.

(david)
 
Thanks, David. I considered using an Access Application reference, but was
not sure I could without knowing what version of Access is on machine before
I open -- as I need to distribute this app to a number of different users on
different machines. But maybe that's not a problem.

Would be interested in what this code might look like -- to open the Access
App, and then execute a designated macro.

Again, any/all help is much appreciated. Thanks again. Larry.
 
set app = getobject("my.mdb")
app.docmd.runmacro "mymacro"

It gets trickier, a lot trickier, if you have user level security.

Also, the Access application is bigger, slower, and flakier.
And it requires that a version of Access is installed.

GetObject depends on .mdb being registered, it returns
an Access application object.

If you wanted to try to use a specific version of access,
or multiple mdb's, you would need to use CreateObject,
but you would drown in your own regrets later.

(david)
 
Okay, I guess I'll leave this as an experiment for later -- as my current
code is working pretty well. Thanks for the insights. Larry.
 
Back
Top