In my previous post launch-open-an-ms-access-database-using-a-vbscript I went over the basic concept of using a VBScript to launch a database.
In this post I would like to demonstrate the true power of VBScript for this type of application.
Say, you are a developer within a structured company network environment and you want to standardize the front-end setup on your users computers without having to sit down at each of their computers, one by one. Well, VBScript to the rescue!
The script below may seem scary, but really isn’t. It start by determining the user’s My Documents location. This is where I have chosen to place the Front-End application of the database. You could always alter this aspect, but it has served me very well in numerous routines. Once it has determine the location it determines the location of the VBScript itself (which will be installed with the Master copy of the Front-end). As such, it copies the Master copy to the user’s My Documents. Then it determines the location of the msaccess.exe required to launch the database and then finally launches it!
Once again, I do hope this helps someone out there as I found it hard to come across this information several years ago when I needed it.
'*******************************************************************************
'Date: 2008-05-27
'Author: Daniel Pineault / CARDA Consultants Inc.
'Purpose: This script should be located on a network share in the same
' directory as the Front-End which it will automatically copy
' to each user's MyDoc\Subfolder\ and then launch
' Give your users a link to this script and it will do the rest
'Copyright: You are free to use the following code as you please so long as
' this header remains unaltered.
'Revision: 2008-05-27 Initial Release
'*******************************************************************************
Const MY_DOCUMENTS = &H5&
Const PROGRAM_FILES = &H26&
Dim objShell
Dim objFolder
Dim objFolderItem
Dim objNetwork
Dim objFSO
Dim objShellDb
Dim DelFoldr
Dim sMyDocPath
Dim sProgPath
Dim sVBSPath
Dim sAccPath
Dim sFrontEnd
Dim sFolder
Dim sSec
Dim sUser
Dim sPath
Dim sComTxt
'Specify the Fullpath and filename of the database to launch
sFrontEnd = "test.mdb" 'Database name to open
sFolder = "Databases" 'MyDoc subfolder where the
'front-end will be copied to
'If your database is secured by an mdw file specify it below, otherwise
'leave its value blank
sSec = "Security.mdw"
'Determine the location/path of the user's MyDocuments folder
'*******************************************************************************
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self
sMyDocPath = objFolderItem.Path 'My Documents path
sPath = sMyDocPath & "\" & sFolder & "\"'Path to front-end
Set objFolder = objShell.Namespace(PROGRAM_FILES)
Set objFolderItem = objFolder.Self
sProgPath = objFolderItem.Path 'Program Files Path
'Determine path of this VBScript
'*******************************************************************************
sVBSPath = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) _
- (Len(WScript.ScriptName) + 1)))
'Ensure lastest version of front-end is installed
Set objNetwork = CreateObject("Wscript.Network")
sUser = objNetwork.UserName 'User's network username
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Copy a network version of the Front-end to the MyDocs/SubFolder
'*******************************************************************************
'Create application folder if it does not already exist
If objFSO.FolderExists(sPath) = False then
Set objFolderCreate = objFSO.CreateFolder(sPath)
End If
'Delete the existing copy to ensure we have a fresh copy
If objFSO.FileExists(sPath & sFrontEnd) then
'Delete the file to copy a new fresh copy
Set DelFile = objFSO.GetFile(sPath & sFrontEnd)
DelFile.Delete
Do While objFSO.FileExists(sPath & sFrontEnd) = True
WScript.Sleep 100
Loop
End if
'Copy a fresh copy of the FE
objFSO.CopyFile sVBSPath & "\" & sFrontEnd, sPath & _
sFrontEnd, OverWriteExisting
Do While objFSO.FileExists(sPath & sFrontEnd) = False
WScript.Sleep 100
Loop
'Determine the location of the MS Access executable
'*******************************************************************************
' Set objShellDb = CreateObject("WScript.Shell")
' 'Determine in which folder the Access executable is located
' If objFSO.FileExists(sProgPath &_
' "\Microsoft Office\OFFICE11\msaccess.exe") Then
' sAccPath = sProgPath & "\Microsoft Office\OFFICE11"
' Elseif objFSO.FileExists(sProgPath &_
' "\Microsoft Office\OFFICE10\msaccess.exe") Then
' sAccPath = sProgPath & "\Microsoft Office\OFFICE10"
' Elseif objFSO.FileExists(sProgPath &_
' "\Microsoft Office\OFFICE\msaccess.exe") Then
' sAccPath = sProgPath & "\Microsoft Office\OFFICE"
' End if
sAppEXE = "MSACCESS.EXE"
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\" & sAppEXE & "\Path"
sAccPath = objShellDb .RegRead(sRegKey)
'Launch database
'*******************************************************************************
'Build the command to launch the database
sComTxt = chr(34) & sAccPath & "msaccess.exe" & chr(34) &_
" " & chr(34) & sPath & sFrontEnd & chr(34)
if isNull(sSec)=False AND sSec<>"" Then
sComTxt = sComTxt & " /wrkgrp " & chr(34) & sVBSPath &_
"\" & sSec & chr(34)
if isNull(sUser)=False AND sUser<>"" Then
sComTxt = sComTxt & " /user " & sUser
End if
End if
objShellDb.Run sComTxt 'Launch the database
