Solved

Using vbs to open a database using the Access Runtime

Posted on 2011-09-25
18
2,116 Views
Last Modified: 2012-05-12
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
Comment
Question by:snyperj
  • 9
  • 7
  • 2
18 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36596331
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
 

Author Comment

by:snyperj
ID: 36596502
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36596549
You need to paste it into your script and call it instead of FireDB.  Post your scipt if it doesn't play nice
0
 

Author Comment

by:snyperj
ID: 36596563
ok, I will try in the office tomorrow... can't get on the network from here.

Thanks-
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36596733
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
 

Author Comment

by:snyperj
ID: 36599195
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
 

Author Comment

by:snyperj
ID: 36599230
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36599624
That's what this line is supposed to be for!

acApp.AutomationSecurity=1  ' low
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36599646
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:Nick67
ID: 36599703
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
 

Author Comment

by:snyperj
ID: 36599967

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
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36600039
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
 

Author Closing Comment

by:snyperj
ID: 36600560
Thanks again for all the help.
0
 
LVL 75
ID: 36600602
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36600710
@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
 
LVL 75
ID: 36600748
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
 
LVL 26

Expert Comment

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

Author Comment

by:snyperj
ID: 37018783
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now