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

MORE INFORMATION

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

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.

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

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.

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.

Stop the server and copy the MDF to the same directory as the ADP.

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

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.

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.
Press ALT+F11 to switch to the Visual Basic Editor.
On the Add-ins menu, if the "Package and Deployment" Wizard is listed, skip to step 7.
On the Add-ins menu, click Add-in Manager.
In the Available Add-ins list, click VBA Package and Deployment Wizard.
Under Load Behavior, click Loaded/Unloaded, and then click OK.
On the Add-ins menu, click Package and Deployment Wizard.
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.
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.
Select the option to include the Microsoft Access Runtime.
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.
Include your MDF file. Click Next.
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.
Click Next until you see the Package and Deployment Wizard - Install Locations screen.
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.
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.