[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Using vbs to open a database using the Access Runtime

Posted on 2011-09-25
18
Medium Priority
?
2,477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 
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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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