Script to open Access database, run macro, and persist Access instance

Question

I would like to have a script to:

Open an Access .accdb file
Run a macro within the database
Leave this open

I can very easily do the first two with the following VB script:

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = true
accessApp.OpenCurrentDataBase("C:\path.accdb")
accessApp.Run "myLinker"

But it immediately closes the Access database when the VBS execution finishes. I would like the instance to remain open independent of the script.

I am not forced to use VBScript for this but it definitely seems the easiest to actually invoke the macro to run.

Bryan Weaver · Accepted Answer · 2013-12-05 15:26:14Z

23

If you want to leave the application open after the script completes you need to set the UserControl property to true.

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = true

accessApp.UserControl = true

accessApp.OpenCurrentDataBase("C:\path.accdb")
accessApp.Run "myLinker"

The Visible property is technically unnecessary when the UserControl property is true. It will automatically be set.

More information here: http://msdn.microsoft.com/en-us/library/office/ff836033.aspx

edited Dec 5, 2013 at 15:26

answered Dec 5, 2013 at 15:15

Bryan Weaver

4,4731 gold badge31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

enderland Over a year ago

Great, thanks. The description on the Application methods page is terribly vague and not useful with respect to its implication -

You can use the UserControl property to determine whether the current Microsoft Access application was started by the user or by another application with Automation, formerly called OLE Automation. Read/write Boolean

Guess that shouldn't surprise me from MSDN at this point...

engineersmnky · Accepted Answer · 2013-12-12 21:58:20Z

2

You could also just use a .bat or .cmd file and put this because MSACCESS has a command line switch for running a macro and unless that macro closes the database it will remain open for user control.

START "" /MAX "PATH\TO\MSACCESS.EXE" "C:\path.accdb" /x myLinker

answered Dec 12, 2013 at 21:58

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Collectives™ on Stack Overflow

Script to open Access database, run macro, and persist Access instance

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related