Microsoft KB Archive/240293

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

Article ID: 240293

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Office 2000 Developer Edition
 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q240293



Advanced: Requires expert coding, interoperability, and multiuser skills.

For a Microsoft Office XP Developer version of this article, see 299297.



SUMMARY
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.



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 </li> When you are finished testing, run MakeADPConnectionless again. After the Access project is connectionless, it is ready to deploy.</li></ol>

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.
 * 1) Press ALT+F11 to switch to the Visual Basic Editor.
 * 2) On the Add-ins menu, if the "Package and Deployment" Wizard is listed, skip to step 7.
 * 3) On the Add-ins menu, click Add-in Manager.
 * 4) In the Available Add-ins list, click VBA Package and Deployment Wizard.
 * 5) Under Load Behavior, click Loaded/Unloaded, and then click OK.
 * 6) On the Add-ins menu, click Package and Deployment Wizard.
 * 7) 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.


 * 1) 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.
 * 2) Select the option to include the Microsoft Access Runtime.
 * 3) 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.
 * 4) Include your MDF file. Click Next.
 * 5) 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.
 * 6) Click Next until you see the Package and Deployment Wizard - Install Locations screen.
 * 7) 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.
 * 1) 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.

<div class="references_section">