[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1282
  • Last Modified:

How to open an access database in runtime mode using vbscript

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
chtullu135
Asked:
chtullu135
  • 3
  • 2
  • 2
2 Solutions
 
Nick67Commented:
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
 
chtullu135Author Commented:
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
 
Nick67Commented:
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
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!

 
PatHartmanCommented:
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
 
chtullu135Author Commented:
Hello Nick,
I believe some of the users have runtime installed while others have the fully blown version of access installed.
0
 
chtullu135Author Commented:
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
 
PatHartmanCommented:
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

Independent Software Vendors: 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!

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