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