Solved

How to open an access database in runtime mode using vbscript

Posted on 2014-12-17
7
986 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
[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
  • 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
Technology Partners: 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!

 
LVL 37

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 37

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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