How to deploy an Access 2000 project that includes the Microsoft Data Engine

Deploying the Microsoft Data Engine (MSDE) with your Microsoft Access project (ADP) involves the following three steps:

1. First, you create code in the ADP to start the MSDE server, copy the data file (*.mdf) to the server, register the MDF, and connect the ADP to the server.
2. Second, you define the MSDE Setup parameters, including the code page and sort order that the MSDE server should use.
3. Third, you package the components of your solution for deployment.

This article provides you with further information about this process and contains sample code to assist you in getting started.

To deploy a Microsoft Access project (*.adp) with the Microsoft Data Engine (MSDE), you need to complete the following three sets of tasks. Note that even though the code in this article is designed to be used in an ADP, with some modification these procedures could be used in any other VBA project.

Step 1: Preparing the ADP

1. Open a module in the ADP that you want to deploy and add the following code. On the target computer, the code will start the MSDE, copy the MDF to the server directory, and connect the ADP to the server:
   

   Public Sub sStartMSDE(sSvrName As String, sUID As String, sPWD As String)
   '********************************************************************
   'This procedure will turn on MSDE. If the server has already been started
   'the error trap will exit the procedure and leave the server running.
   
   'Note that it will not place the SQL Service Manager on the Task Bar.
   '
   'Input:
   '   sSvrName    The server to be started
   '   sUID        The user ID with which to start the server
   '   sPWD        The user password
   '
   'Output:
   '   Resolution of start
   '
   'References:
   '   Microsoft SQLDMO Object Library
   '********************************************************************
   
       Dim osvr As SQLDMO.SQLServer
   
       'Create the SQLDMO Server Object.
       Set osvr = CreateObject("SQLDMO.SQLServer") 
           
       On Error GoTo StartError
       
       osvr.LoginTimeout = 60
       'Start Server.
       osvr.start True, sSvrName, sUID, sPWD
       
   ExitSub:
       Exit Sub
   
   StartError:
       If Err.Number = -2147023840 Then 
          'This error occurs when the server is already running, 
          'and Server.Start is executed on NT.
           osvr.Connect sSvrName, sUID, sPWD 'Connect to server.
       Else 'Unknown error
           MsgBox Err.Description
       End If
       
       Resume ExitSub
       
   End Sub

   Public Sub sCopyMDF(sSvrName As String, sUID As String, sPWD As String, _
                       sMDFName As String, sDBName As String)
   
   '********************************************************************
   'This procedure will check for the database on the MSDE Server. If the 
   'database exists it will then copy sMDFName from the same location as the 
   'ADP to the MSDE Data directory. Then sMDFName is attached to the ADP.
   '
   'Input:
   '   sSvrName    The server to start
   '   sUID        The user ID with which to start the server
   '   sPWD        The user password
   '   sMDFName    The name of the MSDE Database to be copied
   '   sDBName     The name of the database
   '
   'Output:
   '   Resolution of copy
   '
   'References:
   '   Microsoft SQLDMO Object Library
   '   Microsoft Scripting Runtime
   '********************************************************************
   Dim FSO As Scripting.FileSystemObject
   Dim osvr As SQLDMO.SQLServer
   Dim strMessage As String
   Dim db As Variant
   Dim fDataBaseFlag As Boolean
   
   On Error GoTo sCopyMDFTrap
   
       'The drive names used in FSO.Copyfile and oSvr.AttachDBWithSingleFile
       'need to match the locations for Program Files and MSDE on the
       'user's machine.
   
       'Initialize value.
       fDataBaseFlag = False
       
       Set FSO = CreateObject("Scripting.FileSystemObject")
       Set osvr = CreateObject("SQLDMO.SQLServer")
       
       'Log onto database.
       osvr.Connect sSvrName, sUID, sPWD
       
       'Check for database on local MSDE Server
       'by looping through all database names on the local MSDE Server.
       For Each db In osvr.Databases
           If db.Name = sDBName Then 'The database exists.
               fDataBaseFlag = True
               Exit For 'Get out of loop.
           End If
       Next
       
       If Not fDataBaseFlag Then 'There is no database name match.
           'Copy file to data folder.
           FSO.CopyFile Application.CurrentProject.Path & "\" & sMDFName, _ 
            osvr.Databases("master").PrimaryFilePath & sMDFName, True
           'Attach to database.
           strMessage = osvr.AttachDBWithSingleFile(sDBName, _
            osvr.Databases("master").PrimaryFilePath & sMDFName)
       End If
        
   ExitCopyMDF:
       osvr.Disconnect
       Set osvr = Nothing
   Exit Sub
       
   sCopyMDFTrap:
       MsgBox Err.Description
       Resume ExitCopyMDF
   Exit Sub
       
   End Sub

   Public Sub sCreateConnection(sSvrName As String, sUID As String, _ 
                                sPWD As String, sDatabase As String)
   '********************************************************************
   'This Function will check for a connection in the ADP. If there is
   'none it will create one using the input parameters.
   '
   'Input:
   '   sSvrName    The server to start
   '   sUID        The user ID with which to start the server
   '   sPWD        The user password
   '   sDatabase   The name of the MSDE database
   '
   '
   '********************************************************************
   
       Dim sConnectionString As String
       
       On Error GoTo sCreateConnectionTrap:
       
       If Application.CurrentProject.BaseConnectionString = "" Then 
           'This adp is connectionless.
           sConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=" & sPWD & _ 
            ";PERSIST SECURITY INFO=TRUE;USER ID=" & sUID & _ 
            ";INITIAL CATALOG=" & sDatabase & ";DATA SOURCE=" & sSvrName
           Application.CurrentProject.OpenConnection sConnectionString
       End If
       
       
   sCreateConnectionExit:
   Exit Sub
   
   sCreateConnectionTrap:
       MsgBox Err.Description
       Resume sCreateConnectionExit
   
   End Sub

   Because these procedures use SQL DMO code, you must add a reference in the ADP to the Microsoft SQLDMO Object Library. To support the CopyFile function, you must also reference the Microsoft Scripting Runtime.
2. Create a form named Startup. Specify the name of the form in the StartupForm property of your ADP. In the Startup form, set the TimerInterval property to 20 milliseconds. In the OnTimer event of the Startup form, add the following code:NOTE: The followingcode uses generic names: "TestDeploySQL.mdf" for the file name and "TestDeploySQL" for the database name. Remember to change these names to your file name and database name. Also, the form "See Data" can actually be any form in your database that is bound to data. Just make sure the name of the form matches the name specified in the DoCmd.OpenForm command.

   Private Sub Form_Timer()
      Dim sUID As String 'The user ID
      Dim sPWD As String 'The user password
      Dim sServerName As String 'The name of the MSDE or SQL Server
      Dim sDatabaseFileName As String 'The name of the mdf
      Dim sDatabaseName As String 'The name of the database
          
      sUID = "SA" 'User ID
      sPWD = ""    'No password
      sServerName = "(Local)" 'Local MSDE or SQL Server
      sDatabaseFileName = "TestDeploySQL.mdf" 'Name of the mdf
      sDatabaseName = "TestDeploySQL" 'Name of the database   
      
      'Start local MSDE with User = "SA" and Password = " "
      sStartMSDE sServerName, sUID, sPWD
      
      'Copy the mdf to local PC.
      sCopyMDF sServerName, sUID, sPWD, sDatabaseFileName, sDatabaseName
      
      'Connect this adp to new database.
      sCreateConnection sServerName, sUID, sPWD, sDatabaseName 
          
      DoCmd.OpenForm "See Data"  'Launch some form to show data and verify
                                 'that the Access project is working.
   
      DoCmd.Close acForm, Me.Name 'Close current form.
   End Sub

3. Create a form named "See Data" to display the data. This requirement is just for demonstration purposes and you can change the form name in the code to a form of your own. This can be any form in your Access project that is bound to data in the project.
4. After you test this code, use the following procedure to remove the server connections from the Access project:
   

   Sub MakeADPConnectionless()
   '********************************************************************
   'This subroutine will remove the connection from the ADP, rendering
   'it connectionless.
   '********************************************************************
       'Close the connection.
       Application.CurrentProject.CloseConnection 
       'Set the connection to nothing.
       Application.CurrentProject.OpenConnection 
   End Sub

   Run MakeADPConnectionless in the Immediate window.
   
   NOTE: Because of locking issues that this code cannot resolve, you may receive an error when you run MakeADPConnectionless. If the procedure is not successful, quit and restart Microsoft Access and the Access project. Then run MakeADPConnectionless again.
5. Stop the server and copy the MDF to the same directory as the ADP.
6. To make sure your application is working properly, restart the server and run the following procedure in your Access project to delete the MDF on the server:

   Sub DeleteMDF(sSvrName As String, sUID As String, sPWD As String, _ 
                 sDatabase As String)
   '********************************************************************
   'This subroutine uses SQLDMO to drop the currently connected database
   'from the MSDE Server.
   '
   'Note: There is a loop within the error trap to work around
   'dropping a connection.
   '
   'References:
   '   Microsoft SQLDMO Object Library
   '********************************************************************
       Dim osvr As SQLDMO.SQLServer
       Dim i As Integer
       Dim strMessage as String
    
       On Error GoTo DeleteMDFTrap
       
       Application.CurrentProject.CloseConnection 'Close the connection.
   
       Set osvr = CreateObject("SQLDMO.SQLServer") 'Create SQLDMO Object.
       osvr.Connect sSvrName, sUID, sPWD 'Connect to MSDE Server.
       
       strMessage = osvr.DetachDB(sDatabase, True) 'Detach the database.
   
   DeleteMDFExit: 'Clean up.
       osvr.Close
       Set osvr = Nothing
   Exit Sub
       
   DeleteMDFTrap:
       Select Case Err.Number
       
       Case 6008 'Work around issue with closing connection.
           If i < 99 Then 'Continue trying to close connection.
               DoEvents
               Resume
           Else 
     'The connection will not close. Stop trying and exit the procedure.
   
               MsgBox Err.Description
               Resume DeleteMDFExit
           End If
               
       Case Else
           MsgBox Err.Description
           Resume DeleteMDFExit
       End Select
   Exit Sub
   
   End Sub

7. When you are finished testing, run MakeADPConnectionless again. After the Access project is connectionless, it is ready to deploy.

Step 2: Defining the MSDE Setup

MSDE uses the instructions in an ISS file to define its Setup parameters. You can use the default ISS file or create a custom ISS file to distribute with your application. For the purposes of this article, accept the default settings in Sql70ins.iss, which is contained in MSDEX86.exe. This will happen automatically when your users run Setup.

Note that there can be only one MSDE server on a computer. The code page and sort order are selected at the time the MSDE server is installed. After installation, these settings cannot be changed. The code page and sort order of all MDF files supported by a server must match the server.

For more information, see the MSDEDeploy.doc white paper located in the Wpapers folder on the Microsoft Office 2000 Developer CD.

Step 3: Packaging Your Application for Deployment

The last step is packaging the ADP, MDF and MSDEX86.exe for deployment. In this final step, you include all required files, remove duplicate files, and specify MSDEinst.bat to be run at the end.

1. While holding down the SHIFT key to prevent the Startup code from reattaching the MSDE tables, open the ADP.
   
   NOTE: If the reattach code accidentally does execute, you must re-run the MakeADPConnectionless() function before running the Package and Deploy Wizard.
2. Press ALT+F11 to switch to the Visual Basic Editor.
3. On the Add-ins menu, if the "Package and Deployment" Wizard is listed, skip to step 7.
4. On the Add-ins menu, click Add-in Manager.
5. In the Available Add-ins list, click VBA Package and Deployment Wizard.
6. Under Load Behavior, click Loaded/Unloaded, and then click OK.
7. On the Add-ins menu, click Package and Deployment Wizard.
8. Click the Package button, and select the Standard Setup Package option. Click Next.
   
   NOTE: You receive the error "Object variable or With block not Set" when you click the Package button. Click OK to ignore this error and continue.
   
   
9. On the Included Files screen, clear the option to include Sqldmo.rll because it is installed by the MSDE Setup. Also, clear the options for Msvcrt.dll and Scrrun.dll.
10. Select the option to include the Microsoft Access Runtime.
11. Manually add Msdex86.exe to the list. You can find the file in the MSDE folder on the Microsoft Office 2000 Developer CD. When you add this file, note that other dependent files are also added, such as MSDEInst.bat.
12. Include your MDF file. Click Next.
13. In the Run this Command text box, type MSDEinst.bat to perform a silent installation of MSDE to C:\MSSQL7. If you want the Setup program to ask the user where to install MSDE, see the MSDEDeploy.doc white paper located in the \Wpapers folder on the Microsoft Office 2000 Developer CD.
14. Click Next until you see the Package and Deployment Wizard - Install Locations screen.
15. Notice that MSDEInst.bat has an installation location of $(AppPath)\MSDETemp. Change the installation location to $(AppPath). Make no changes to the paths of the other MSDE files.
    
    NOTE: If you do not make this change, the MSDEInst.bat file will not be able to find the file Msdex86.exe and MSDE Setup will fail silently on the target computer.
16. Continue the packaging process to create the Setup program.

After the package has been created, you can install your application to another computer by running the program that Setup.exe created in the package.

For more information, see the PackagingAccessRT.doc and MSDEDeploy.doc white papers in the Wpapers folder on the Microsoft Office 2000 Developer CD.

Also, to see an example ADP, take a look at MSDEDeploy, which is also on the Microsoft Office 2000 Developer CD in the \ODETools\v9\Samples\MSDE folder.