Creating and Deploying Access Solutions with the Microsoft Data Engine

Scott Smith
Microsoft Corporation

January 1999

For the most current information, see www.microsoft.com/officedev/default.htm.

The Microsoft® Data Engine (MSDE) is a Microsoft SQL Server™ 7.0 compatible data storage server that ships with both Office 2000 Premium and Office 2000 Developer, each slightly different. When installed, the Office Premium version acts as a local server and supports creation of a new project type, Access Data Projects (ADP). This version of MSDE contains a number of files that enable visual ADP project development, and it is not redistributable. The version of MSDE that ships on the Office 2000 Developer CD is a self-extracting executable run-time engine with no design-time UI components. This version of it is redistributable.

This paper addresses packaging a project where the data and solution exist together on the same machine—similar to current Jet-based solutions. It outlines the steps required to create, package, and distribute an Access Runtime solution with MSDE using the Visual Basic® for Applications (VBA) Package and Deployment Wizard included with Office 2000 Developer. A sample ADP project and MDF file are found in on the Office 2000 Developer CD in the \ODETools\v9\Samples\MSDE folder. The example code in the sample is similar to the code in this paper, but is more generic for re-use purposes.

Note ADP solutions that use data in an existing server do not require the distribution of MSDE, and can be easily deployed within a corporate environment, where workstations have access to SQL 7.0 Server.

Installing Office MSDE

To install MSDE for development purposes, run the setup program SETUPSQL.EXE on the Office Premium CD1 found in the \SQL\x86\Setup folder. The process is fairly straightforward, and all the default settings can be used. See Access 2000 online help for further details.

Creating a New ADP Solution

For the solution described in this paper, begin by creating a new database. From the File menu, choose New, and select the Project (New Database) icon. For detailed information, Access 2000 online Help contains extensive documentation on ADP solutions.

Coding the ADP Solution for Distribution

Distributing MSDE requires you as the developer to complete several steps. You must programmatically accomplish the following:

Install MSDE on the user's machine.
Start MSDE.
Load the database into MSDE.

Installing MSDE on the user's machine

The VBA Package and Deployment Wizard, described later in this paper, has an option to run an executable or a batch file on completion of Setup. This example uses that feature to launch a batch file that contains a command line that will extract and install MSDE on the user's machine.

The batch file contains:

start /wait msdex86.exe –s –a –f1 "sql70ins.iss"

This uses a default .iss file contained within msdex86.exe along with all of the product defaults.

You can also issue a command with a custom .iss file:

start /wait msdex86.exe -s -a -f1 "c:\Progra~1\Adp1\MSDETemp\MSDEInst.iss"

where you would provide the full path to your custom .iss file after copying it to the user's machine.

This statement silently expands and installs MSDE to the user's hard drive. It uses the file MSDEInst.iss as instructions for installation. In addition, the "start /wait" parameter causes the installation to complete before returning control of the system to the batch file.

You need to change the folder named "Adp1" to the appropriate name of your project. You may want to augment this batch file with a program that requests the user to choose where to install MSDE on the system (the default is the C:\ drive), then modifies the .iss file appropriately. You may also want to remove the MSDETemp folder after completion. Detailed information on the contents of the .iss file can be found in the Platform SDK.

Starting MSDE on the user's machine

The two options for launching MSDE are: via command line on Microsoft Windows NT® at the end of the installation process, or through SQL DMO code.

The command line statement for Windows NT is:

net start mssqlserver

The SQL DMO code will do the same thing, as follows, and will work for either Windows® 95/Windows 98 or Windows NT.

Note Using SQL DMO requires a reference to the Microsoft SQLDMO Object Library:

Sub TurnOnMSDE()
Dim oSvr As SQLDMO.SQLServer

    Set oSvr = CreateObject("SQLDMO.SQLServer")
    On Error GoTo StartError
    oSvr.LoginTimeout = 60 ' This needs to be high to avoid time-out errors.
    oSvr.Start True, "(local)", "sa", ""

ExitSub:
    Exit Sub

StartError:
    If Err.Number = -2147023840 Then 'This error is thrown when the server is already running, and Server.Start is executed on NT.
        oSvr.Connect "(local)", "sa", ""
        Resume Next
    End If
    
End Sub

Loading the database into MSDE on the user's machine

The following code only needs to run once, and it assumes a few things. Therefore, you will need to modify it appropriately.

First, it assumes the Setup program copied the MDF file to the same folder as the ADP file and that the current directory contains the MDF file.
It also assumes that ultimately the file should go in the MSSQL7\Data folder.
Note It is not necessary to place the MDF file in the MSSQL7\Data folder, but that is the standard location. This subroutine also requires a reference to the Microsoft Scripting Runtime to handle the copy file routine.

Sub ConnectData()
Dim strMsg As String
Dim strCurDir as String
Dim FSO As Scripting.FileSystemObject
Dim oSvr As SQLDMO.SQLServer

     Set FSO = CreateObject("Scripting.FileSystemObject")
     Set oSvr = CreateObject("SQLDMO.SQLServer")

'Log onto database
     oSvr.Connect "(local)", "sa", ""

'Copy File to data folder
     strCurDir = CurDir & "adp1sql.mdf"
     FSO.CopyFile strCurDir, "c:\mssql7\data\adp1sql.mdf", True

'Attach to database
     strmessage = oSvr.AttachDBWithSingleFile("DemoDatabase", "c:\mssql7\data\adp1SQL.mdf")

'Display the success or failure message
     MsgBox strmessage

     oSvr.Disconnect
     Set oSvr = Nothing

End Sub

For debugging purposes, you may want to programmatically detach the database so this code can be re-run. Use the SQLDMO method DetachDB.

Setting up MSDE for Packaging

The files in the MSDE folder on the Office 2000 Developer CD make up a template for creating custom MSDE-based solutions—the file MSDEInst.bat contains the command line information described above in the "Installing Office MSDE" section, and should be modified for your specific use.

The MSDEx86.dep file lists dependencies for MSDEx86.exe as follows:

; Dependency file for MSDEx86.exe

; Default Dependencies -----------

[MSDEx86.exe]
Dest=$(AppPath)\MSDETemp
Uses1=MSDEInst.iss
Uses2=MSDEInst.bat

[MSDEInst.bat]
Dest=$(AppPath)\MSDETemp
Uses1=

[MSDEInst.iss]
Dest=$(AppPath)\MSDETemp
Uses1=

This file is designed to tell the VBA Package and Deployment Wizard which files to pick up and where to install the files on the user's machine. The current settings place all the files in an \MSDETemp folder in the application path, making it easy for you to delete later.

The MSDEInst.iss file should also be modified for your requirements, and at run time should be changed to point to the correct drive install location.

To use these template files, copy all of them (MSDEx86.exe, MSDEx86.dep, MSDEInst.bat, and MSDEInst.iss) to your machine in a separate folder, modified as necessary for each project.

Packaging the ADP Solution using the VBA Package and Deployment Wizard

The VBA Package and Deployment Wizard is found on the Add-In menu in the Visual Basic for Applications 6.0 Environment. Specific instructions for using the wizard can be found in Office 2000 Developer online help.

To use the VBA Package and Deployment Wizard to package an MSDE-based solution:

Launch the Package and Deployment Wizard from the Add-In menu in the VBA environment.
On the Included Files screen, select Add File and browse to the MDF data file associated with your solution, and add it to your project.
Select Add File again, and browse to the folder containing MSDEx86.exe and its dependencies. Add this file to your solution. Notice that the other dependent files are also added, based on the MSDEx86.dep file settings.
Uncheck any dependencies found that will already be on the end-user's machine, such as SQLDMO.rll.
On the Installation Options screen, select the Run this command when installation is finished option and type the following:
```
MSDETemp\MSDEInst.bat.
```
This will perform a silent install of MSDE to C:\MSSQL7. If you wish to give the user the option of which drive to install to, create an executable program and place it on this line instead. The executable should request the user for an installation location, then modify the file MSDEInst.iss, to change szDir and szDataDir to point to the new installation location.

After this modification, have the executable launch MSDEInst.bat.

Continue with the packaging process to create the Setup program.

Important Development, Testing and Distribution Issues

Only the version MSDE on the Office 2000 Developer CD is redistributable. The version on the Office Premium CD is not.
MSDE is fully compatible with SQL Server 7.0. A solution created with MSDE can be migrated to SQL Server using the Import and Export Data tool, which comes as part of the Office MSDE, or using the SQL DMO method AttachDBWithSingleFile. When using AttachDBWithSingleFile, a log file is built using the system defaults. If you wish to ship your own log file, use the AttachDB method.
Only one MSDE can be installed and running on a machine at a time. Testing redistributable MSDE-based solutions should be done on a separate machine from the development machine, if the development machine is using Office MSDE or SQL Server 7.0. Additionally, MSDE and SQL Server 7.0 only support one sort order and code page—regardless of the number of databases installed on that machine. Use SQL DMO to verify the code page and sort order.
When packaging MSDE, the Package and Deployment Wizard may find references that will already be on the developer's machine. Remove these from the package list, or mark them as shared, or they will be removed when the solution is uninstalled.