如何部署為 Access 2002 專案,其中包含 Microsoft SQL Server 2000 桌面引擎

重要:本文是以 Microsoft 機器翻譯軟體翻譯而成,而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章,讓使用者可以依其使用語言使用知識庫中的所有文章。但是,機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤,就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。

按一下這裡查看此文章的英文版本:299297
本文已封存。本文係以「現狀」提供且不會再更新。
本文只適用於 Microsoft Access 專案 (.adp)。

進階: 須具備專家編碼、 互通性,與多使用者技能。

本文章的有 Microsoft Office 2000 程式開發人員] 版本請參閱 240293

在此工作

結論
在 Microsoft SQL Server 2000 桌面引擎 (MSDE 2000) 是 SQL Server 2000 相容資料存放區伺服器隨附於 Microsoft Office XP 開發人員轉散發的權限。Office XP 開發 o 人 h 員 û 工 u 具 ã 封裝精靈 」 有包括 SQL Server 2000 桌面引擎,當封裝 Microsoft Access 專案中的選項 (*.adp) 解決方案。方案是一位使用者的電腦上安裝,SQL Server 2000 桌面引擎安裝搭配方案。不過,未啟動 SQL Server 2000 桌面引擎,而且資料庫並未連接到 SQL Server 2000 桌面引擎。

注意: Microsoft SQL Server 2000 桌面引擎的前一版名為 [Microsoft 資料引擎 (MSDE)。

本文提供您必須使用來尋找要啟動伺服器,如果在不啟動已經,將資料庫附加到該伺服器,並將專案連接至新附加資料庫伺服器的程式碼。程式碼只在專案中使用。不過,大部分的程式碼可由任何 Visual Basic 應用程式 (VBA) 應用程式。

Microsoft 僅,為了說明提供程式設計範例,不提供任何明示或默示的保證。這包括,但不限於適售性或適合某特定用途之默示擔保責任。本文假設您已熟悉使用我們所示範的程式設計語言以及建立和偵錯程序所使用的工具。Microsoft 技術支援工程師可以協助解釋特定程序的功能,但它們不會修改這些範例以提供附加功能或建構程序,以符合您特定需求。back to the top

步驟來修改現有的專案部署的應用程式

下列步驟假設您已經有一個正常運作的專案已準備好要部署的應用程式。步驟會帶您逐步完成如何將額外的程式碼加入至您的 VBA 專案、 如何進行必要的調整,以您的啟動表單以及如何建立為現有的 Microsoft Access 專案的部署套件 (*.adp),包括 Microsoft SQL Server 2000 桌面引擎。
  1. 開啟您想要部署的 Access 專案,然後建立新的模組。

    因為您將會包含程式碼會使用 SQLDMO 程式碼和指令碼,您必須確定必要的參考存在。
  2. 按一下 [工具] 功能表的 Visual Basic 編輯器,引用項目
  3. 在 [參考] 對話方塊中如果按一下以選取下列它們尚未選取:
    • Microsoft SQLDMO 物件程式庫
    • Microsoft 指令碼執行階段
  4. 按一下 [確定] 以關閉 [參考] 對話方塊。
  5. 複製下列程式碼至新的模組:
    Option Compare DatabaseOption ExplicitDim adp_UseIntegratedSecurity As BooleanPublic 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 = "(local)"            If strUN = "" Then               MsgBox "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."               Exit Function            End If           adp_UseIntegratedSecurity = False    Else        strMachineName = ComputerName        If strUN = "" 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 = "This application requires SQL Server 2000 " & _            "to be installed on the local computer."        MsgBox strErrorMsg, vbCritical, "SQL Server 2000 not installed!"        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, "MSSQLSERVER") Then        strServername = strMachineName    Else        spaceLocation = InStr(1, strSQLInstances, " ")        If spaceLocation = 0 Then            strServername = strMachineName & "\" & strSQLInstances        Else            strServername = strMachineName & "\" & 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, strPWEnd FunctionPublic 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("SQLDMO.SQLServer")            On Error GoTo StartError 'Error Trap    osvr.LoginTimeout = 60    osvr.LoginSecure = adp_UseIntegratedSecurity    osvr.Start True, strServername, strUN, strPWExitSub:    Set osvr = Nothing    Exit FunctionStartError:    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 & ": " & Err.Description    End If    Resume ExitSubEnd FunctionPublic 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.FileSystemObjectDim osvr As SQLDMO.SQLServerDim strMessage As StringDim db As VariantDim fDataBaseFlag As BooleanDim dbCount As IntegerOn 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 = ""    fDataBaseFlag = False    Set FSO = CreateObject("Scripting.FileSystemObject")    Set osvr = CreateObject("SQLDMO.SQLServer")    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 _        & "\" & sMDFName, _        osvr.Databases("master").PrimaryFilePath & _        sMDFName, True        'Attach to database.        strMessage = osvr.AttachDBWithSingleFile(strDBName, _            osvr.Databases("master").PrimaryFilePath _            & sMDFName)    End If     ExitCopyMDF:    osvr.Disconnect    Set osvr = NothingExit Function    sCopyMDFTrap:    If Err.Number = -2147216399 Then 'DMO must be initialized        Resume Next    Else        MsgBox Err.Description    End If        Resume ExitCopyMDFExit Function    End FunctionFunction 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 ""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 = "Provider=SQLOLEDB.1" & _    ";Data Source=" & strServername & _    ";Initial Catalog=" & strDBName    If adp_UseIntegratedSecurity Then        strConnect = strConnect & ";integrated security=SSPI"    Else        strConnect = strConnect & ";user id=" & strUN        strConnect = strConnect & ";password=" & strPW    End If    Application.CurrentProject.OpenConnection strConnect    fChangeADPConnection = True    Exit FunctionEH:    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"    fChangeADPConnection = FalseEnd Function					
  6. 將此模組儲存為 modCopyConnect
  7. 建立第二個模組,然後將下列程式碼複製到第二個模組:
    Option Compare DatabaseOption 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 * 128End TypeDeclare Function GetVersionExA Lib "kernel32" _         (lpVersionInformation As OSVERSIONINFO) As IntegerPrivate Declare Function OSRegOpenKey Lib "advapi32" Alias _"RegOpenKeyA" (ByVal hKey As Long, ByVal lpszSubKey As String, _phkResult As Long) As LongPrivate Declare Function OSRegQueryValueEx Lib "advapi32" _Alias "RegQueryValueExA" (ByVal hKey As Long, _ByVal lpszValueName As String, ByVal dwReserved As Long, _lpdwType As Long, lpbData As Any, cbData As Long) As LongPrivate Declare Function GetComputerName _Lib "kernel32" Alias _"GetComputerNameA" (ByVal lpBuffer As String, _nSize As Long) As LongPrivate Declare Function OSRegCloseKey Lib "advapi32" _Alias "RegCloseKey" (ByVal hKey As Long) As LongPrivate Const MAX_COMPUTERNAME_LENGTH As Long = 15&Public Const HKEY_CLASSES_ROOT = &H80000000Public Const HKEY_CURRENT_USER = &H80000001Public Const HKEY_LOCAL_MACHINE = &H80000002Public Const HKEY_USERS = &H80000003Private Const ERROR_SUCCESS = 0&Private Const VER_PLATFORM_WIN32s = 0  'Win32s on Windows 3.1Private Const VER_PLATFORM_WIN32_WINDOWS = 1  'Windows 95/98/ME.Private Const VER_PLATFORM_WIN32_NT = 2  'Windows NT/2000/XPPrivate Const REG_SZ = 1Private Const REG_BINARY = 3Private Const REG_DWORD = 4Private Const REG_MULTI_SZ = 7Public 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 = ""    GetValidSQLInstances = 0        If RegOpenKey(HKEY_LOCAL_MACHINE, _    "Software\Microsoft\Microsoft SQL Server", hKey) Then        RegQueryStringValue hKey, "InstalledInstances", strSQLInstances        RegCloseKey hKey        StrConv strSQLInstances, vbUpperCase        If InStr(1, strSQLInstances, "MSSQLSERVER") Then           If RegOpenKey(HKEY_LOCAL_MACHINE, _           "Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion", _           hKey) Then                RegQueryStringValue hKey, "CurrentVersion", strVersionInfo                RegCloseKey hKey                If Mid(strVersionInfo, 1, 1) <> 8 Then                    Replace strSQLInstances, "MSSQLSERVER", ""                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) = " " Then                GetValidSQLInstances = GetValidSQLInstances + 1            End If        Next i    End IfEnd FunctionPublic 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 IfEnd FunctionPublic 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 FunctionPrivate 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 FunctionPrivate 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 = "HKEY_CLASSES_ROOT"        Case HKEY_CURRENT_USER            strGetPredefinedHKEYString = "HKEY_CURRENT_USER"        Case HKEY_LOCAL_MACHINE            strGetPredefinedHKEYString = "HKEY_LOCAL_MACHINE"        Case HKEY_USERS            strGetPredefinedHKEYString = "HKEY_USERS"    End SelectEnd FunctionPublic 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 IfEnd FunctionPublic 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 IfEnd FunctionPublic 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) = " "            End If        Next i        ReplaceNullsWithSpaces = Left$(str, Len(str) - 2)    Else        ReplaceNullsWithSpaces = str    End IfEnd FunctionPublic 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 = strComputerNameEnd 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 IfEnd Function
  8. 將第二個模組儲存為 GetSQLInstances
  9. 在 [設計] 檢視中開啟現有的啟動表單,或如果您沒有啟動表單建立新的啟動表單。
  10. 將命令加入至啟動表單呼叫 fStartUp 函式的 OnOpen 事件屬性。

    您必須指定您想要在 SQL Server 上建立的資料庫名稱和現有的 SQL Server 資料檔案名稱。 您也可以指定所需的 SQL Server 登入名稱及密碼做為選擇性的第三個和第四個引數如果您不使用整合式的安全性。 比方說如果想建立使用資料檔,稱為 NorthwindSQL.mdf 稱為北風資料庫函式顯示如下:
    =fStartUp("Northwind","NorthwindSQL.mdf","","")
    附註 此附註情況牽涉到 SQL Server 安全性。如果沒有提供登入名稱稍早提到的函式呼叫中本文的程式碼會嘗試使用整合式的安全性,如果基礎作業系統可以支援它 (Microsoft Windows NT 4.0、 Microsoft Windows 2000 和 Microsoft Windows XP)。如果基礎作業系統是 Microsoft Windows 98 或 Microsoft Windows 千禧版 (Me),您必須提供有效的 SQL Server 使用者帳戶及密碼。 無論作業系統] 系統如果您指定至少一個的登入名稱程式碼嘗試連線藉由使用 SQL 安全性具有所提供的登入名稱及密碼。如果您沒有 SQL Server 資料檔案的複本必須製作與部署套件包含該資料檔案的複本。
  11. 在 [工具] 功能表上指向 [資料庫公用程式,然後按一下 [複製資料庫檔案
  12. 在產生 開啟舊檔 對話方塊,指定名稱和您要儲存資料庫檔案,按一下 [儲存] 以完成此程序,然後關閉 [] 對話方塊中的位置。第一次執行專案時目標電腦上,Access 會嘗試連線到 SQL Server 所指定的檔案的 [連線] 內容中。雖然本文的程式碼仍然會執行,而且仍會更新連線資訊,最好在部署之前移除現有的連線資訊。

    若要移除現有的連線資訊,您可以執行 MakeADPConnectionless() 函式包含在 modCopyConnect 模組。

  13. 執行函式、 即時運算視窗 中輸入下列以及按下 ENTER:
    ?MakeADPConnectionless
  14. 儲存所做的變更。
  15. 在 [增益集] 功能表上如果列出封裝精靈請到步驟 19。
  16. 增益集] 功能表上按一下 [增益集管理員]。
  17. 在 [可用的增益集] 清單中,按一下 [封裝精靈]。
  18. 載入行為的按一下 [載入/卸載,然後按一下 [確定]]。
  19. 按一下 [增益集] 功能表 封裝精靈
  20. 直到您取得 相依性] 畫面,請遵循精靈中的步驟。
  21. 相依性] 畫面按一下 [新增檔案...],以將您先前已備份的 MDF 檔案新增]。
  22. 按一下 [下一步],直到您到達 Access 執行階段內容] 畫面。在此畫面上按一下以選取 [Microsoft SQL Server 2000 桌面引擎 (MSDE)] 核取方塊,以包含 MSDE 引擎。
  23. 依照精靈來完成封裝中,或者您可以按一下 完成 每當您要。
封裝建立之後您就可以在一般使用者電腦上安裝套件。

back to the top

参考
取得更多資訊有關在其他電腦的封裝中包含 SQL 2000 桌面引擎按一下 [下列面的文件編號,檢視 「 Microsoft 知識庫 」 中發行項]:
290623如何將現有的 SQL Server 2000 資料庫附加到 SQL Server 2000 桌面引擎
322228自訂應用程式 (白皮書) 的 [設定成內嵌 MSDE 2000 安裝程式
274199無法在 Windows 95 上安裝 MSDE 2000
299351注意: 有 SQL Server 2000 Service Pack 1 或 2 安裝的系統上 MSDE 安裝失敗
如 SQL 轉換的其他有關 SQL 2000 桌面引擎,您資料庫,請按一下下面的文件編號,檢視 「 Microsoft 知識庫 」 中的發行項:
325023到 SQL Server 轉換 SQL Server 2000 桌面引擎
back to the top
offxpdev ACCXP ACC2002

警告:本文為自動翻譯

內容

文章識別碼:299297 - 最後檢閱時間:10/23/2013 19:54:53 - 修訂: 5.1

Microsoft Office XP Developer Edition, Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kbmt kbhotfixserver kbqfe kbhowto kbadp kbfaq KB299297 KbMtzh
意見反應