Microsoft KB Archive/299297

= How to deploy an Access 2002 project that includes the Microsoft SQL Server 2000 Desktop Engine =

Article ID: 299297

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft Office XP Developer Edition
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q299297



This article applies only to a Microsoft Access project (.adp).

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

For a Microsoft Office 2000 Developer version of this article, see 240293.

IN THIS TASK

 * SUMMARY
 * Steps to modify an existing project application for deployment
 * REFERENCES



SUMMARY
The Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is a SQL Server 2000-compatible data storage server that is included with Microsoft Office XP Developer, with rights to redistribute. The Office XP Developer Packaging Wizard has an option for including the SQL Server 2000 Desktop Engine when packaging a Microsoft Access project (*.adp) solution. When the solution is installed on the computer of a user, the SQL Server 2000 Desktop Engine is installed together with the solution. However, the SQL Server 2000 Desktop Engine is not started, and the database is not attached to the SQL Server 2000 Desktop Engine.

NOTE: The previous version of Microsoft SQL Server 2000 Desktop Engine is named Microsoft Data Engine (MSDE).

This article provides the code that you must use to find the server, to start the server if it is not started already, to attach the database to the server, and to connect the project to the newly attached database. The code is specific to use in a project. However, much of the code can be used by any Visual Basic for Applications (VBA) application.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. back to the top

Steps to modify an existing project application for deployment
The following steps assume that you already have a properly working project application that is ready to deploy. The steps walk you through how to add additional code to your VBA project, how to make required adjustments to your startup form, and how to create a deployment package for an existing Microsoft Access project (*.adp) that includes the Microsoft SQL Server 2000 Desktop Engine.  Open the Access project that you want to deploy, and then create a new module.

Because the code that you will include uses SQLDMO code and scripting, you must make sure that the required references are present. On the Tools menu of the Visual Basic Editor, click References. In the References dialog box, click to select the following if they are not already selected:  Microsoft SQLDMO Object Library Microsoft Scripting Runtime  Click OK to close the References dialog box.</li>  Copy the following code to the new module: Option Compare Database Option Explicit Dim adp_UseIntegratedSecurity As Boolean

Public Function fStartUp(strDBName As String, strMDFName As String, _       Optional strUN As String, Optional strPW As String) ' 'The code in this project connects the MDF file 'to a local MSDE, then establishes the connection between 'the Access Project and MSDE. '   Dim strSQLInstances As String Dim strServername As String Dim intInst As Integer Dim strMachineName As String Dim spaceLocation As Long 'If no username is supplied, and you cannot 'use integrated security, the function requires that you provide a valid SQL Server user account and password. If Not fCheckForCompatibleOS Then strMachineName = &quot;(local)&quot; If strUN = &quot;&quot; Then MsgBox &quot;Provide a valid SQL Server user account and password to log on to SQL Server because the current operating system does not support integrated security.&quot; Exit Function End If          adp_UseIntegratedSecurity = False Else strMachineName = ComputerName If strUN = &quot;&quot; Then adp_UseIntegratedSecurity = True Else adp_UseIntegratedSecurity = False End If   End If

'Find the available instances of SQL 2000 on the computer. intInst = GetValidSQLInstances(strSQLInstances) If intInst < 1 Then Dim strErrorMsg As String strErrorMsg = &quot;This application requires SQL Server 2000 &quot; & _ &quot;to be installed on the local computer.&quot; MsgBox strErrorMsg, vbCritical, &quot;SQL Server 2000 not installed!&quot; Exit Function End If   'At this point, it has been determined that there is at    'least one valid SQL Server 2000 instance on the computer. 'The code below picks the default or first instance if more than 'one is available. You may want to add code to prompt the user for 'a choice when there is more than one instance on the computer. If InStr(1, strSQLInstances, &quot;MSSQLSERVER&quot;) Then strServername = strMachineName Else spaceLocation = InStr(1, strSQLInstances, &quot; &quot;) If spaceLocation = 0 Then strServername = strMachineName & &quot;\&quot; & strSQLInstances Else strServername = strMachineName & &quot;\&quot; & Mid(strSQLInstances, 1, spaceLocation) End If   End If    'Call fstartMSDE to connect to SQL Server fStartMSDE strServername, strUN, strPW 'Call sCopyMDF to move the data file to the data folder 'of SQL Server, and then attach it to the server. fCopyMDF strServername, strUN, strPW, strDBName, strMDFName 'Connect the ADP to the new database fChangeADPConnection strServername, strDBName, strUN, strPW

End Function

Public Function fStartMSDE(strServername As String, _               Optional strUN As String, Optional strPW As String) ' 'This subroutine will turn on MSDE. If the server has been 'started, the error trap will exit the function leaving the 'server running. ' 'Note that it will not put the SQL Service Manager on 'the start bar. ' 'Input: '  strServername    The server to be started '  strUN        The user used to start server '  strPW        The password of user ' 'Output: '  Resolution of start ' 'References: '  SQLDMO '

Dim osvr As SQLDMO.SQLServer Set osvr = CreateObject(&quot;SQLDMO.SQLServer&quot;) On Error GoTo StartError 'Error Trap osvr.LoginTimeout = 60 osvr.LoginSecure = adp_UseIntegratedSecurity osvr.Start True, strServername, strUN, strPW

ExitSub: Set osvr = Nothing Exit Function

StartError: If Err.Number = -2147023840 Then 'This error is thrown when the server is already running, 'and Server.Start is executed on Windows NT, 2000, or XP. osvr.Connect strServername, strUN, strPW 'Connect to Server Else 'Unknown Error MsgBox Err.Number & &quot;: &quot; & Err.Description End If   Resume ExitSub End Function

Public Function fCopyMDF(strServername As String, _               strUN As String, strPW As String, _                strDBName As String, _                sMDFName As String)

' 'This Function determines whether the database is already on 'the MSDE Server. If the database does not exist, this 'function copies the MDF file from the same location as the 'ADP to MSDE's Data directory and then attaches the database. ' 'Input: '  strServername   The server to be started '  strUN           The user used to start server '  strPW       The password of user '  strDBName       The Name of the SQL Database '  sMDFName        The Name of the MSDE Database to be copied ' 'Output: '  Resolution of copy ' 'References: '  SQLDMO '  Scripting Runtime '

Dim FSO As Scripting.FileSystemObject Dim osvr As SQLDMO.SQLServer Dim strMessage As String Dim db As Variant Dim fDataBaseFlag As Boolean Dim dbCount As Integer

On Error GoTo sCopyMDFTrap

'The drive names used in FSO.Copyfile and 'oSvr.AttachDBWithSingleFile must match the 'locations for Program Files and MSDE on the 'computer of the end user.

fCopyMDF = &quot;&quot; fDataBaseFlag = False Set FSO = CreateObject(&quot;Scripting.FileSystemObject&quot;) Set osvr = CreateObject(&quot;SQLDMO.SQLServer&quot;) osvr.LoginSecure = adp_UseIntegratedSecurity osvr.Connect strServername, strUN, strPW dbCount = osvr.Databases.Count 'Look for database existence on Local MSDE Server 'by looping through all database names on the local 'MSDE Server. For Each db In osvr.Databases If db.Name = strDBName Then 'The database exists fDataBaseFlag = True Exit For 'Get out of loop End If   Next If Not fDataBaseFlag Then 'There is no database 'matching sDBName

'Copy File to data folder. FSO.CopyFile Application.CurrentProject.Path _ & &quot;\&quot; & sMDFName, _ osvr.Databases(&quot;master&quot;).PrimaryFilePath & _ sMDFName, True

'Attach to database. strMessage = osvr.AttachDBWithSingleFile(strDBName, _           osvr.Databases(&quot;master&quot;).PrimaryFilePath _            & sMDFName) End If ExitCopyMDF: osvr.Disconnect Set osvr = Nothing Exit Function sCopyMDFTrap:

If Err.Number = -2147216399 Then 'DMO must be initialized Resume Next Else MsgBox Err.Description End If   Resume ExitCopyMDF Exit Function End Function

Function MakeADPConnectionless ' 'This code removes the connection properties from the 'Access Project for troubleshooting purposes. 'The ADP will open in a disconnected state until new connection 'properties are supplied. '   Application.CurrentProject.OpenConnection &quot;&quot; End Function Function fChangeADPConnection(strServername, strDBName As String, Optional strUN As String, _       Optional strPW As String) As Boolean ' 'This Function resets the connection for an ADP by using the 'input parameters to create a new connection string. If no username 'is supplied, it tries to connect by using integrated security. ' 'Input: '  strServerName    The server to be started '  strDBName   The Name of the MSDE Database '  strUN        The user used to start server '  strPW        The password of user '   Dim strConnect As String On Error GoTo EH: strConnect = &quot;Provider=SQLOLEDB.1&quot; & _ &quot;;Data Source=&quot; & strServername & _ &quot;;Initial Catalog=&quot; & strDBName If adp_UseIntegratedSecurity Then strConnect = strConnect & &quot;;integrated security=SSPI&quot; Else strConnect = strConnect & &quot;;user id=&quot; & strUN strConnect = strConnect & &quot;;password=&quot; & strPW End If   Application.CurrentProject.OpenConnection strConnect fChangeADPConnection = True Exit Function EH: MsgBox Err.Number & &quot;: &quot; & Err.Description, vbCritical, &quot;Connection Error&quot; fChangeADPConnection = False End Function </li> Save this module as modCopyConnect .</li>  Create a second module, and then copy the following code to the second module: Option Compare Database Option Explicit

'This module provides functions that work together to 'find existing computers running SQL Servers, and also the computer name. Public Type OSVERSIONINFO dwOSVersionInfoSize As Long dwMajorVersion As Long dwMinorVersion As Long dwBuildNumber As Long dwPlatformId As Long szCSDVersion As String * 128 End Type

Declare Function GetVersionExA Lib &quot;kernel32&quot; _ (lpVersionInformation As OSVERSIONINFO) As Integer

Private Declare Function OSRegOpenKey Lib &quot;advapi32&quot; Alias _ &quot;RegOpenKeyA&quot; (ByVal hKey As Long, ByVal lpszSubKey As String, _ phkResult As Long) As Long

Private Declare Function OSRegQueryValueEx Lib &quot;advapi32&quot; _ Alias &quot;RegQueryValueExA&quot; (ByVal hKey As Long, _ ByVal lpszValueName As String, ByVal dwReserved As Long, _ lpdwType As Long, lpbData As Any, cbData As Long) As Long

Private Declare Function GetComputerName _ Lib &quot;kernel32&quot; Alias _ &quot;GetComputerNameA&quot; (ByVal lpBuffer As String, _ nSize As Long) As Long

Private Declare Function OSRegCloseKey Lib &quot;advapi32&quot; _ Alias &quot;RegCloseKey&quot; (ByVal hKey As Long) As Long

Private Const MAX_COMPUTERNAME_LENGTH As Long = 15& Public Const HKEY_CLASSES_ROOT = &H80000000 Public Const HKEY_CURRENT_USER = &H80000001 Public Const HKEY_LOCAL_MACHINE = &H80000002 Public Const HKEY_USERS = &H80000003 Private Const ERROR_SUCCESS = 0& Private Const VER_PLATFORM_WIN32s = 0 'Win32s on Windows 3.1 Private Const VER_PLATFORM_WIN32_WINDOWS = 1 'Windows 95/98/ME. Private Const VER_PLATFORM_WIN32_NT = 2 'Windows NT/2000/XP Private Const REG_SZ = 1 Private Const REG_BINARY = 3 Private Const REG_DWORD = 4 Private Const REG_MULTI_SZ = 7

Public Function GetValidSQLInstances(ByRef strSQLInstances _               As String) As Integer '--- ' This returns number of valid SQL instances and a space ' delimited string that lists the instances. '---

Dim hKey As Long, i As Integer Dim strVersionInfo As String strSQLInstances = &quot;&quot; GetValidSQLInstances = 0 If RegOpenKey(HKEY_LOCAL_MACHINE, _   &quot;Software\Microsoft\Microsoft SQL Server&quot;, hKey) Then RegQueryStringValue hKey, &quot;InstalledInstances&quot;, strSQLInstances RegCloseKey hKey StrConv strSQLInstances, vbUpperCase If InStr(1, strSQLInstances, &quot;MSSQLSERVER&quot;) Then If RegOpenKey(HKEY_LOCAL_MACHINE, _          &quot;Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion&quot;, _           hKey) Then RegQueryStringValue hKey, &quot;CurrentVersion&quot;, strVersionInfo RegCloseKey hKey If Mid(strVersionInfo, 1, 1) <> 8 Then Replace strSQLInstances, &quot;MSSQLSERVER&quot;, &quot;&quot; End If           End If        End If        Trim strSQLInstances If Len(strSQLInstances) > 0 Then GetValidSQLInstances = GetValidSQLInstances + 1 Else Exit Function End If       For i = 1 To Len(strSQLInstances) If Mid$(strSQLInstances, i, 1) = &quot; &quot; Then GetValidSQLInstances = GetValidSQLInstances + 1 End If       Next i    End If End Function

Public Function RegOpenKey(ByVal hKey As Long, _ ByVal lpszSubKey As String, phkResult As Long) As Boolean '--- ' FUNCTION: RegOpenKey ' Opens an existing key in the system registry. ' Returns: True, if the key opened successfully. False ' otherwise. ' Upon success, phkResult is set to the handle of the key. '---   Dim lResult As Long Dim strHkey As String

strHkey = strGetHKEYString(hKey)

lResult = OSRegOpenKey(hKey, lpszSubKey, phkResult) If lResult = ERROR_SUCCESS Then RegOpenKey = True End If End Function

Public Function RegCloseKey(ByVal hKey As Long) As Boolean Dim lResult As Long '--- ' FUNCTION: RegCloseKey ' Closes an open registry key. ' Returns: True on success, else False. '---   lResult = OSRegCloseKey(hKey) RegCloseKey = (lResult = ERROR_SUCCESS) End Function

Private Function strGetHKEYString(ByVal hKey As Long) As String '--- 'Given an HKEY, return the text string representing that key. '---   Dim strKey As String Dim intIdx As Integer strKey = strGetPredefinedHKEYString(hKey) If Len(strKey) > 0 Then strGetHKEYString = strKey Exit Function End If End Function

Private Function strGetPredefinedHKEYString(ByVal _ hKey As Long) As String '--- 'Given a predefined HKEY, return the text string representing 'that key, or else return vbNullString. '---   Select Case hKey Case HKEY_CLASSES_ROOT strGetPredefinedHKEYString = &quot;HKEY_CLASSES_ROOT&quot; Case HKEY_CURRENT_USER strGetPredefinedHKEYString = &quot;HKEY_CURRENT_USER&quot; Case HKEY_LOCAL_MACHINE strGetPredefinedHKEYString = &quot;HKEY_LOCAL_MACHINE&quot; Case HKEY_USERS strGetPredefinedHKEYString = &quot;HKEY_USERS&quot; End Select End Function

Public Function RegQueryStringValue(ByVal hKey As Long, _ ByVal strValueName As String, strData As String) As Boolean '--- ' Retrieves the string data for a named ' (strValueName = name) or unnamed (Len(strValueName) = 0) ' value in a registry key. If the named value ' exists, but its data is not a string, this function ' fails. ' ' Returns: True on success, else False. '  On success, strData is set to the string data value. '---   Dim lResult As Long Dim lValueType As Long Dim strBuf As String Dim lDataBufSize As Long lResult = OSRegQueryValueEx(hKey, strValueName, 0&, _             lValueType, _        ByVal 0&, lDataBufSize) If lResult = ERROR_SUCCESS Then If lValueType = REG_SZ Then strBuf = space$(lDataBufSize) lResult = OSRegQueryValueEx(hKey, strValueName, 0&, _                0&, ByVal strBuf, lDataBufSize) If lResult = ERROR_SUCCESS Then RegQueryStringValue = True strData = StringFromBuffer(strBuf) End If               ElseIf lValueType = REG_MULTI_SZ Then strBuf = space$(lDataBufSize) lResult = OSRegQueryValueEx(hKey, strValueName, 0&, _                     0&, _                ByVal strBuf, lDataBufSize) If lResult = ERROR_SUCCESS Then RegQueryStringValue = True strData = ReplaceNullsWithSpaces(strBuf) End If       End If    End If End Function

Public Function StringFromBuffer(Buffer As String) As String Dim nPos As Long nPos = InStr(Buffer, vbNullChar) If nPos > 0 Then StringFromBuffer = Left$(Buffer, nPos - 1) Else StringFromBuffer = Buffer End If End Function

Public Function ReplaceNullsWithSpaces(str As String) As String '--- ' Replace all null characters with spaces. '---   Dim i As Integer If Len(str) > 0 Then For i = 1 To Len(str) If Mid$(str, i, 1) = vbNullChar Then Mid$(str, i, 1) = &quot; &quot; End If       Next i        ReplaceNullsWithSpaces = Left$(str, Len(str) - 2) Else ReplaceNullsWithSpaces = str End If End Function

Public Function ComputerName As String '--- ' Returns the local computer name. '---   Dim nLen As Long Dim strComputerName As String nLen = MAX_COMPUTERNAME_LENGTH strComputerName = String$(nLen, 0) GetComputerName strComputerName, nLen strComputerName = Left$(strComputerName, nLen) ComputerName = strComputerName End Function Public Function fCheckForCompatibleOS As Boolean '--- ' Checks to see if the OS can use integrated security. '---   Dim osinfo As OSVERSIONINFO Dim retvalue As Integer osinfo.dwOSVersionInfoSize = 148 osinfo.szCSDVersion = space$(128) retvalue = GetVersionExA(osinfo) If osinfo.dwPlatformId >= VER_PLATFORM_WIN32_NT Then fCheckForCompatibleOS = True Else fCheckForCompatibleOS = False End If End Function </li> Save the second module as GetSQLInstances .</li> Open your existing startup form in Design view, or create a new startup form if you do not have a startup form.</li>  Add a command to the OnOpen event property of your startup form to call the fStartUp function.

You must specify the database name that you want to create on the SQL Server and the existing SQL Server data file name. You can also specify the required SQL Server logon name and password as optional third and fourth arguments if you are not using integrated security. For example, if you want to create a database that is called Northwind by using a data file called NorthwindSQL.mdf, the function appears as follows: =fStartUp(&quot;Northwind&quot;,&quot;NorthwindSQL.mdf&quot;,&quot;&quot;,&quot;&quot;) Note This note concerns SQL Server Security. If you do not supply a logon name in the function call that is mentioned earlier, the code in this article tries to use integrated security if the underlying operating system can support it (Microsoft Windows NT 4.0, Microsoft Windows 2000, and Microsoft Windows XP). If the underlying operating system is Microsoft Windows 98 or Microsoft Windows Millennium Edition (Me), you have to provide a valid SQL Server user account and password. Regardless of the operating system, if you specify at least a logon name, the code tries to connect by using SQL Security with the supplied logon name and password. If you do not have a copy of your SQL Server data file, you must make a copy of that data file to include with your deployment package. </li> On the Tools menu, point to Database Utilities, and then click Copy Database File.</li> In the resulting Open dialog box, specify the name and the location where you want to save the database file, click Save to finish the process, and then close the dialog box. When the project is first run on the target computer, Access tries to connect to the SQL Server that is specified in the connection properties of the file. Although the code in this article still runs and still updates the connection information, it is a good idea to remove the existing connection information before you deploy.

To remove the existing connection information, you can run the MakeADPConnectionless function, which is included in the modCopyConnect module.

</li>  To run the function, type the following into the Immediate Window, and then press ENTER: ?MakeADPConnectionless </li> Save your changes.</li> On the Add-ins menu, if Packaging Wizard is listed, go to step 19.</li> On the Add-ins menu, click Add-in Manager.</li> In the Available Add-ins list, click Packaging Wizard.</li> For Load Behavior, click Loaded/Unloaded, and then click OK.</li> On the Add-ins menu, click Packaging Wizard.</li> <li>Follow the steps in the wizard until you get to the Dependencies screen.</li> <li>On the Dependencies screen, click Add File... to add the MDF file that you backed up earlier.</li> <li>Click Next until you reach the Access Runtime Properties screen. On this screen, click to select the Microsoft SQL Server 2000 Desktop Engine (MSDE) check box to include the MSDE engine.</li> <li>Follow the steps in the wizard to complete the package, or you can click Finish whenever you want to.</li></ol>

After the package is created, you are ready to install the package on the computers of the end users.

back to the top

<div class="references_section">