Solved

How to open an access database in runtime mode using vbscript

Posted on 2014-12-17
7
720 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

22 Experts available now in Live!

Get 1:1 Help Now