asked on

Using vbs to open a database using the Access Runtime

Background: Nick67 provided some vbs scripts that can be used to deploy and update access front ends. It works great and I am able to use the same script for both Office 2000 and 2007 installations. Unfortunately I am learning that we will now also have Office 2010 standard editions with no MSAccess installed. So I have been trying to get the vbs script to work with the Access run time.

The section of the code that opens the database is shown in the code window. In his last post Nick67 said the following regarding altering it for use with the runtime:

Have a look for the header Automating Access Runtime a quarter of the way down here
http://support.microsoft.com/kb/317113
http://support.microsoft.com/kb/295179/EN-US
<The Access Runtime must be started with a database. Because of this requirement, if you want to automate the Access Runtime, you must start the Msaccess.exe and specify a database to open. After you use GetObject to retrieve the Application object, you can proceed with automating the Access Runtime. If you try to automate the Access Runtime with the New keyword or with CreateObject, you will receive an error message >

So this will have to change
Set acApp=CreateObject("Access.Application")

However, since it is just firing the db
dim PathToMDE
PathToMDE = "U:\db\Timesheet\time_capture_fe.mdb"

Set acApp=CreateObject("Access.Application")
acApp.usercontrol=true
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
acApp.DoCmd.Hourglass True
'acApp.AutomationSecurity=1 ' low
acApp.OpenCurrentDatabase PathToMDE
acApp.DoCmd.Hourglass false
Set acApp=Nothing

It could conceivably be preceeded with a shell command
Shell "WhateverThePathToTheRuntimeIs\MsAccess.exe" & chr(34) & PathToMDE & Chr(34)
and changed to
Set acApp=GetObject("Access.Application")

...and this is where I continue to struggle....

I changed line 6 & 7 to:

Shell "C:\Program Files\Microsoft Office\Office12\MsAccess.exe" & chr(34) & PathToMDE & Chr(34)
Set acApp=GetObject("Access.Application")

but now I am getting an error in the script telling me "Type mismatch: 'Shell' so I must not have the syntax correct? How should I alter the code below to incorporate this shell command to the runtime? Does the line: acApp.OpenCurrentDatabase PathToMDE need to be removed as well?

sub FireDB

dim PathToMDE

PathToMDE = "U:\dbApps\vTime\vTime_fe.mdb"

Set acApp=CreateObject("Access.Application")
acApp.Visible = False

acApp.DoCmd.Hourglass True
'acApp.AutomationSecurity=1  ' low
acApp.OpenCurrentDatabase PathToMDE
acApp.DoCmd.Hourglass false
'acApp.usercontrol=true

acApp.Visible = True
acApp.DoCmd.RunCommand (10) 'accmdappmaximize

Set acApp=Nothing
Set fs = nothing



end sub

Open in new window

Nick67

Quick and dirty, here's a sub in vbscript that gets it open.
Add the rest in.

I may polish in a bit, but I know you're up against it, so here's something to start with

'---------------------------------------
Private sub FireRunTime()

set WshShell = WScript.CreateObject("WScript.Shell")

dim PathToMDE
PathToMDE = "U:\db\Timesheet\time_capture_fe.mdb"

set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE""" & PathToMDE

end sub
'-----------------------------------------

snyperj

ASKER

OK, so I tried that with the following values, using a test database and the path on this machine where the runtime is located.

But when I click on it, nothing happens.... no errors or anything....

Private sub FireRunTime()

set WshShell = WScript.CreateObject("WScript.Shell")

dim PathToMDE
PathToMDE = "c:\dbApps\vTime\database.mdb"

set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run """C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE""" & PathToMDE

End Sub

Open in new window

Nick67

You need to paste it into your script and call it instead of FireDB. Post your scipt if it doesn't play nice

snyperj

ASKER

ok, I will try in the office tomorrow... can't get on the network from here.

Thanks-

Nick67

OK,

More tweaking.
Here's a script that opens a db.
This actually dead simpler!

'declare an object to be a filesystem object
Public fs
'declare an object to be a MS Access application
Public acApp
 'declare a wscript shell
Public WshShell

Main()

'-------------------------------------
Sub Main()
FireRunTime()
end sub

'---------------------------------------
Private sub FireRunTime()

set WshShell = WScript.CreateObject("WScript.Shell")

dim PathToMDE
'PathToMDE = "U:\db\Timesheet\time_capture_fe.mdb"

PathToMDE = "C:\Users\admin\Desktop\EE\snyperj.accdb"
Set acApp=GetObject(PathToMDE)
acapp.usercontrol = true
'set WshShell = WScript.CreateObject("WScript.Shell")
'WshShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE""" & PathToMDE

acApp.AutomationSecurity=1  ' low
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
Set acApp=Nothing

end sub
'-----------------------------------------

Open in new window

deploy.vbs

snyperj

ASKER

I changed the PathToMDE to my actual, but this is what I am now getting

'declare an object to be a filesystem object
Public fs
'declare an object to be a MS Access application
Public acApp
 'declare a wscript shell
Public WshShell

Main()

'-------------------------------------
Sub Main()
FireRunTime()
end sub

'---------------------------------------
Private sub FireRunTime()

set WshShell = WScript.CreateObject("WScript.Shell")

dim PathToMDE
'PathToMDE = "U:\db\Timesheet\time_capture_fe.mdb"

PathToMDE = "c:\dbApps\vTime\database.mdb"
Set acApp=GetObject(PathToMDE)
acapp.usercontrol = true
'set WshShell = WScript.CreateObject("WScript.Shell")
'WshShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE""" & PathToMDE

acApp.AutomationSecurity=1  ' low
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
Set acApp=Nothing

end sub
'-----------------------------------------

Open in new window

vbsError.bmp

snyperj

ASKER

I did get this to work(based on your first post from last night), but I am getting the Macro security warning and not sure how to disable it in the runtime.

Public WshShell

FireRunTime()
'-------------------------
Private sub FireRunTime()

set WshShell = WScript.CreateObject("WScript.Shell")

dim PathToMDE
PathToMDE = "c:\dbApps\vTime\database.mdb"

WshShell.Run """C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE""" & PathToMDE

End Sub

Open in new window

Nick67

That's what this line is supposed to be for!

acApp.AutomationSecurity=1 ' low

Nick67

But with the code in your very last post, there is no Access object to automate, so you can't use that line.
That's why I researched and tested the other syntax, because that is the necessary syntax for Access automation

Nick67

Now,
I DON'T have the runtime installed, anywhere.

I tested that last posted script against machines running the full version.
Test the script against a machine using the full version.
See if it works.
If it does, then you may have Trust Center or other run-time related issues.
If it doesn't, then you may have file path issues.
The script works for me at home and at work as written against full version A2003 and A2010

snyperj

ASKER

Nick- first of all THANK YOU for your continued efforts. I am very appreciative of your expertise.

Q: What does the following line suppose to do in an Office 2007 installation?

acApp.AutomationSecurity=1

Is that suppose to prohibit the security warning that fires when a db starts up, even if the db location is NOT trusted?

I ask this because I tried the below script on my full version Office2007 and it DOES open the database, but I still get the security error near the menubar when the db opens(see pic). I used a folder (C:\db) that was not already set as trusted whereas previously I was using U:\db which was already trusted- so I didn't notice this.

I am thinking maybe acApp.AutomationSecurity=1

doesn't actually do anything (for Access 2007 and higher) and everything relies on trusted locations...which means I am going to have a problem setting those other than manually.

'declare an object to be a filesystem object
Public fs
'declare an object to be a MS Access application
Public acApp
 'declare a wscript shell
Public WshShell

Main()

'-------------------------------------
Sub Main()
FireRunTime()
end sub

'---------------------------------------
Private sub FireRunTime()

set WshShell = WScript.CreateObject("WScript.Shell")

dim PathToMDE
'PathToMDE = "U:\db\Timesheet\time_capture_fe.mdb"

PathToMDE = "c:\dbApps\vTime\database.mdb"
Set acApp=GetObject(PathToMDE)
acapp.usercontrol = true
'set WshShell = WScript.CreateObject("WScript.Shell")
'WshShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE""" & PathToMDE

acApp.AutomationSecurity=1  ' low
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
Set acApp=Nothing

end sub
'-----------------------------------------

Open in new window

error2.JPG

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

snyperj

ASKER

Thanks again for all the help.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Hey Nick ... how would you set that on the fly in a start up routine (assuming permissions) ... say by first checking to see if it exists, if not write that key.

mx

Nick67

@mx
Is that a 500 point question?
<grin>

I do stuff with RegSvr32 and copying stuff to system32 in vbscript files that get called by the login script.
(which takes admin or system permissions)
So, very likely, a vbscript could be created that checks for those key and creates them.
http://www.visualbasicscript.com/Vbscript-to-Edit-Registry-m57985.aspx
Then you'd call that vbs from the login batch file
wscript %0\..\SomeScriptThatIsInNetlogonFolder.vbs

But it isn't something I do, at the moment, so I have no working code to post

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

ok thx ... just curious. Right now, I'm setting (or will be shortly) peeps system for a TL for a couple of users that IT 'accidentally' installed A2010 on. Soon, I will have to deal with 100+ peeps. Easy to do manually, but ... would be cool if my start up routine checked, then created if necessary - although kind of a waste once it's been done.

501 pts btw.

mx

Nick67

Code never dies.
You archive it and remember that you've done it before.
And then you trot it back out again. :)

snyperj

ASKER

Followup regarding the above VBS posted here:
https://www.experts-exchange.com/questions/27412300/Need-help-modifying-this-VBS-code.html

Please assist if you are available. Thank you.