Run Access Macro from VB Script

R

rconverse

Well-known Member

Joined

Nov 29, 2007

Messages

1,187

• Mar 11, 2008

• #2

I use this, which appears almost identical to what you have except I do not run a macro. I call a sub procedure.

Code:

dim accessApp
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase("F:\Your File Path\Your DB Name.mdb")

accessApp.Run "SubName"
accessApp.Quit
set accessApp = nothing

I would try to rename the macro. Maybe because your DB and macro are named the same thing access is getting confused? (Obviously that is just a guess.)

The other thing you may try is look into the syntax of this statment:

Code:

objaccess.run "AO"

Based on this error: Error:...can't find procedure AO

I am thinking that maybe to call a macro, you may need:

Code:

objaccess.run "macro.AO"  

'or 

objaccess.runmacro "AO"

Just some ideas.

HTH
Roger

 

Upvote 0

A

abesimpson

Active Member

Joined

May 3, 2003

Messages

435

• Mar 11, 2008

• #3

I converted the macro to a vbs function which I saved in module 2 as "run()". Then I used your code.

Trying to run the code I get an error at Line: 5 Char: 1, ... Access can't find the proceedure 'run'.

Any suggestions?

Thanks in advance for your help.


a

 

Upvote 0

R

rconverse

Well-known Member

Joined

Nov 29, 2007

Messages

1,187

• Mar 11, 2008

• #4

Could you please post the exact code?

Thanks,
Roger

Edit: You could also try: Docmd.run "module2.run"

 

Last edited: Mar 11, 2008

Upvote 0

A

abesimpson

Active Member

Joined

May 3, 2003

Messages

435

• Mar 11, 2008

• #5

Code:

dim accessApp
set accessApp = CreateObject("Access.Application")

' accessApp.visible = True

accessApp.OpenCurrentDataBase("Z:\My Virtual Machines data\VM find stock signals\office\portfolio lists\Current Portfolios.mdb")
accessApp.Run "run"
accessApp.Quit
set accessApp = nothing

You will notice the extra line of code "accessApp.visible = True" I added (now commented out). This line confirms that the database "Current Portfolios.mdb" is in fact being addressed.

Thanks for your help.

abe

 

Upvote 0

R

rconverse

Well-known Member

Joined

Nov 29, 2007

Messages

1,187

• Mar 11, 2008

• #6

I think you need to change your module2 from a function to a sub.

Sub Run()

 

Upvote 0

A

abesimpson

Active Member

Joined

May 3, 2003

Messages

435

• Mar 11, 2008

• #7

That was that (function to sub); the devil as they say is in the details.

Many thanks for the touch of magic.

a

 

Upvote 0

R

rconverse

Well-known Member

Joined

Nov 29, 2007

Messages

1,187

• Mar 11, 2008

• #8

Great! You're welcome.

 

Upvote 0

A

abesimpson

Active Member

Joined

May 3, 2003

Messages

435

• Mar 11, 2008

• #9

Is there a similar compact and repair VBS script?

Regards

a

 

Upvote 0

R

rconverse

Well-known Member

Joined

Nov 29, 2007

Messages

1,187

• Mar 12, 2008

• #10

There is, but under tools | options click the general tab and check the compact on close. That will do the same thing when the DB closes and you don't need VBS. That might suit your needs.

Thanks,
Roger

 

Upvote 0