• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2564
  • Last Modified:

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

0
snyperj
Asked:
snyperj
  • 9
  • 7
  • 2
1 Solution
 
Nick67Commented:
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
'-----------------------------------------
0
 
snyperjAuthor Commented:
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

0
 
Nick67Commented:
You need to paste it into your script and call it instead of FireDB.  Post your scipt if it doesn't play nice
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Thanks-
0
 
Nick67Commented:
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
0
 
snyperjAuthor Commented:
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
0
 
snyperjAuthor Commented:
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

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

acApp.AutomationSecurity=1  ' low
0
 
Nick67Commented:
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
0
 
Nick67Commented:
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
0
 
snyperjAuthor Commented:

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
0
 
Nick67Commented:
It may be that
acApp.AutomationSecurity=1  ' low
is deprecated for A2007+

There is reghacking for the trust center settings available.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27309981.html?sfQueryTermInfo=1+10+30+center+nick67+trust

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\TrustedLocations\SomeName]
"Path"="C:\\Path To Your Folder"
"AllowSubfolders"=dword:00000001
"Description"="TrustedLocation1"

You could get your IT guys to possibly deploy this via login scripts or GPO
0
 
snyperjAuthor Commented:
Thanks again for all the help.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
Nick67Commented:
@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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
Nick67Commented:
Code never dies.
You archive it and remember that you've done it before.
And then you trot it back out again. :)
0
 
snyperjAuthor Commented:
Followup regarding the above VBS posted here:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27412300.html

Please assist if you are available.  Thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now