Solved

How to open an access database in runtime mode using vbscript

Posted on 2014-12-17
7
637 Views
Last Modified: 2014-12-18
Hello,

I am using the script below to open up an access database.  It works when the database is accdb, but when I try to open the same database in runtime mode, i get an error message prompting me to use the runtime switch.  I thought this wasn't necessary if I changed the extension to accdr

'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
CONST LOCAL_VERSION_TEXT_PATH = "C:\Databases\ProcurementOperationsLocalVersion\LocalVersion.txt"
CONST DEPLOYMENT_VERSION_TEXT_PATH = "\\pkdw0037L\matmgmt\SCM Forms\ProcurementOperations\FrontEnd\DeploymentVersion.txt"
CONST LOCAL_VERSION_FRONTEND_PATH = "C:\Databases\ProcurementOperationsLocalVersion\ProcurementOperations.accdr"
CONST DEPLOYMENT_VERSION_FRONTEND_PATH = "\\pkdw0037L\matmgmt\SCM Forms\ProcurementOperations\FrontEnd\ProcurementOperations.accdr"
CONST USER_PATH = "\\pkdw0037L\matmgmt\SCM Forms\ProcurementOperations\FrontEnd"
CONST BUILT_PATH = "C:\Databases\ProcurementOperationsLocalVersion"
CONST TEMP_PATH = "c:\prod\temp"
Main()

'-------------------------------------------------------------------------------------------------------------------------------------------
Sub Main()
dim MyConn
dim MyConn1
Dim LocalSplit
Dim ServerSplit
Dim x 

OpenCmdWindow()


Set fs = CreateObject("Scripting.FileSystemObject")

CheckDeployment()

If fs.FileExists(DEPLOYMENT_VERSION_TEXT_PATH) then
    Set MyConn= fs.OpenTextFile(DEPLOYMENT_VERSION_TEXT_PATH,1, true)
    ServerSplit = Split(MyConn.readall, ".")
    MyConn.Close
end if

If fs.FileExists(LOCAL_VERSION_TEXT_PATH) then
    Set MyConn1= fs.OpenTextFile(LOCAL_VERSION_TEXT_PATH,1, true)
    LocalSplit = Split(MyConn1.readall, ".")
    MyConn1.Close
else
    LocalSplit =Split("0.0.0.0", ".")
end if

set myconn = nothing
set myconn1 = nothing

If UBound(LocalSplit) <> UBound(ServerSplit) Then
        Call CopyUpdate()
        FireDB()
        Exit Sub
End If

For x = 0 To UBound(LocalSplit)
    If cint(ServerSplit(x)) > cint(LocalSplit(x)) Then
        Call CopyUpdate()
        FireDB()
        Exit Sub
    End If
Next

FireDB()

end sub

'------------------------------------------------------------------------------------
private Sub CopyUpdate()
Dim BuiltPath
Dim userpath

userpath= USER_PATH
BuiltPath = BUILT_PATH
TempPath = TEMP_PATH

If fs.FolderExists(BuiltPath) = False Then
    fs.CreateFolder BuiltPath
End If

'fs.copyfile userpath, BuiltPath, -1
fs.copyfile DEPLOYMENT_VERSION_TEXT_PATH, LOCAL_VERSION_TEXT_PATH, -1
fs.copyfile DEPLOYMENT_VERSION_FRONTEND_PATH, LOCAL_VERSION_FRONTEND_PATH



End Sub
'-----------------------------------------------------------------------------------------

Function CheckDeployment() 
Dim BuiltPath
BuiltPath = LOCAL_VERSION_FRONTEND_PATH

If fs.FileExists(BuiltPath) = False Then
    Call CopyUpdate()
End If



End Function
'---------------------------------------------------------------------------------------
sub FireDB

CloseCmdWindow()
dim PathToMDE
PathToMDE = LOCAL_VERSION_FRONTEND_PATH



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
Set fs = nothing

end sub

'-------------------------------------------------------------
sub OpenCmdWindow()
set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd.exe"
WScript.Sleep 10
WshShell.AppActivate "c:\windows\system32\cmd.exe"
WScript.Sleep 100
WshShell.SendKeys "Starting....wait patiently...."


end sub

 '------------------------------------------------------------
sub CloseCmdWindow()
dim oshell
dim oWmg
dim strWndprs
dim objQResult

WScript.Sleep 100

Set oShell = CreateObject("WScript.Shell") 
Set oWmg = GetObject("winmgmts:") 

strWndprs = "select * from Win32_Process where name='cmd.exe'" 
Set objQResult = oWmg.Execquery(strWndprs) 

For Each objProcess In objQResult 
	intReturn = objProcess.Terminate(1) 
Next
End sub

Open in new window

0
Comment
Question by:chtullu135
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40505413
The accdr file does specify that it should pretend to be a run as if only the run-time is installed.
Clearly that works entirely well -- runtime files are fired up ONLY on the command line by providing the path to the app file.

Set acApp=CreateObject("Access.Application") won't work on a machine that strictly has the run-time installed and apparently fails when Access emulates the run-time too.

So it looks like you'll need to get everything else FireDB does into the .accdr itself and fire it with
WshShell.Run "c:\whateverthefullpathtoMSAccess.exeIsonyourmachines " & chr(34) & PathToMDE
 & CHR(34)
0
 

Author Comment

by:chtullu135
ID: 40505462
I'm not sure I understand.  I've seldom used runtime.  When runtime is installed,  is it installed as a special runtime MSAccess.exe file on each machine the machine
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40505499
Microsoft has a free run-time version of Access available for download and installation.
ALL it does is run pre-created database apps.
No object creation (forms, tables ect)
No code creation (no VB editor)
No code editing
No support for opening Access from other programs to be automated
No error catching.
You may wish to install it on machines that don't have Access on them and for which you don't want to spend the money for full-blown Access.

You can force full-blown Access to ACT like the run-time with command line switches in a run... or shell... call.
You can do it in the AutoExec macro code when Access first opens
Or you can do it by changing the name of the file to mdr, or accdr
When Access is made to run this way, ALL the limitations of the run-time (those listed above) and others which may be desired (No Nav Pain, No BackStage etc.) come into play.

Run-time Access doesn't have File | Open available.
So it immediately understands that trying to open a file is meant to fail (acApp.OpenCurrentDatabase PathToMDE)
Hence your problem.
So, you need to instead open your accdr as if you were doing so from the Windows Run... dialog, or from a double-click.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40505518
Here is a very simple .bat file that works because it opens the database from the command file.  You could add version checking but I don't bother.  Each time the user opens the app, the batch file downloads a new copy.  The .bat file doesn't throw an error if the

md c:\ClientMgt
del c:\ClientMgt\AOAClientManagement.accdr
copy \\newfiscal\Afox\AFox\AccessApplications\ClientMgt\AOAClientManagement.accdr c:\ClientMgt
c:\ClientMgt\AOAClientManagement.accdr

Open in new window

0
 

Author Comment

by:chtullu135
ID: 40506223
Hello Nick,
I believe some of the users have runtime installed while others have the fully blown version of access installed.
0
 

Author Closing Comment

by:chtullu135
ID: 40507405
I've decided to go the batch file route to deploy new runtime front-ends since  I won't be able to use the vbs script to deploy and open up the accdr files.  It's not a perfect solution but it's good enough.  I've split the points. Nick provided an excellent explanation of what the problem was while Pat provided a work around.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40508304
Looks like I stopped mid sentence so for the sake of future readers, I'll finish my sentence:

The .bat file doesn't throw an error if the -- directory already exists or if the file being deleted doesn't exist so you don't have to worry about either.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 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

17 Experts available now in Live!

Get 1:1 Help Now