將 Access 資料庫移轉至 SQL Server

將 Access 資料庫移轉至 SQL Server

我們都有限制,而 Access 資料庫則不例外。 例如,Access 資料庫的大小限制為 2 GB,且不支援超過255個併發使用者。 所以,在您的 Access 資料庫移至下一層級時,您可以遷移至 SQL Server。 SQL Server (無論是內部部署或是 Azure 雲端)支援大量的資料、更多並行的使用者,且擁有的容量比 JET/ACE 資料庫引擎還大。 本指南可讓您順利開始使用 SQL Server 歷程,協助保留您所建立的 Access 前端解決方案,並希望 motivates 您使用 Access 來供未來的資料庫解決方案使用。 [升遷嚮導] 已從 access 2013 的 Access 中移除,所以現在您可以使用 Microsoft SQL Server 遷移小幫手(SSMA)。 若要成功遷移,請依照下列步驟進行。

資料庫移轉至 SQL Server 的階段

開始之前

下列各節提供背景及其他資訊,以協助您快速入門。

關於分割資料庫

所有 Access 資料庫物件都可以在一個資料庫檔案中,也可以儲存在兩個資料庫檔案中:前端資料庫和後端資料庫。 這稱為分割資料庫,旨在協助在網路環境中共用。 後端資料庫檔案必須只包含資料表和關聯。 前端檔案必須只包含所有其他物件,包括表單、報表、查詢、宏、VBA 模組及連結資料表至後端資料庫。 當您遷移 Access 資料庫時,它類似于分割資料庫,因為該 SQL Server 是目前位於伺服器上的資料的新後端。

因此,您仍然可以在 SQL Server 資料表中使用連結資料表來維護前端 Access 資料庫。 有效地,您可以衍生 Access 資料庫快速應用程式開發的優點,以及 SQL Server 的可伸縮性。

SQL Server 的優點

您仍需要一些想要遷移至 SQL Server 的說服嗎? 以下是一些您可以考慮的其他好處:

  • 其他並行使用者    當您新增更多使用者時,SQL Server 所能處理的併發使用者數比存取及將記憶體需求降至最低。

  • 提高可用性    有了 SQL Server,您就可以在使用中的資料庫進行動態備份(無論是遞增或完成)。 因此,您不需要強制使用者離開資料庫,即可備份資料。

  • 高效能與伸縮性    SQL Server 資料庫通常會比 Access 資料庫執行得更好,尤其是對於大型、tb 大小的資料庫。 此外,SQL Server 會以並行方式處理查詢,並在單一處理程式中使用多個原生執行緒來處理使用者要求,以更快且更有效率的方式處理查詢。

  • 改良的安全性    使用信任連線時,SQL Server 會與 Windows 系統安全性整合,以提供網路和資料庫的單一整合存取,同時使用這兩個安全系統的最佳做法。 這可讓管理複雜的安全性方案變得更容易。 SQL Server 是適用于機密資訊(例如社會保險號碼、信用卡資料以及機密位址)的理想儲存空間。

  • 立即可恢復性    如果作業系統當機或電源出現問題,SQL Server 可以在幾分鐘內自動將資料庫復原至一致的狀態,而且不需要資料庫管理員的干預。

  • 使用 VPN    Access 和虛擬私人網路(VPN)不會出現。 但有了 SQL Server,遠端使用者仍然可以使用桌上型電腦上的 Access 前端資料庫,且 SQL Server 後端位於 VPN 防火牆後面。

  • Azure SQL Server    除了 SQL Server 的優點之外,提供動態可擴充性、無停機、智慧優化、全域可擴充性及可用性、消除硬體成本,以及減少管理。

選擇最佳的 Azure SQL Server 選項

如果您要遷移至 Azure SQL Server,有三個選項可供選擇,每個選項都有不同的優點:

  • 單一資料庫/彈性池    此選項有自己的資源集,可透過 SQL 資料庫伺服器來管理。 單一資料庫就像是 SQL Server 中包含的資料庫。 您也可以新增彈性池,這是一個由 SQL 資料庫伺服器管理之共用資源集的資料庫集合。 最常使用的 SQL Server 功能可搭配內建備份、修補及復原使用。 但不保證確切的維護時間及從 SQL Server 進行的遷移也很困難。

  • Managed 實例    此選項是具有共用資源集的系統和使用者資料庫集合。 受管理的實例就像是高度相容 SQL Server 內部部署的 SQL Server 資料庫實例。 受管理的實例有內建的備份、修補、復原,而且很容易從 SQL Server 進行遷移。 不過,有少量的 SQL Server 功能無法使用,且無法保證確切的維護時間。

  • Azure 虛擬機器    這個選項可讓您在 Azure 雲端的虛擬機器內執行 SQL Server。 您可以完全控制 SQL Server 引擎和輕鬆的遷移路徑。 但您需要管理您的備份、修補程式及恢復。

如需詳細資訊,請參閱在 azure 中選擇您的資料庫移轉路徑選擇正確的 SQL Server 選項

第一個步驟

您可以提前解決一些問題,在您執行 SSMA 之前,可以協助簡化遷移程式:

  • 新增資料表索引和主鍵    請確定每個 Access 資料表都有索引和主鍵。 SQL Server 必須至少有一個索引,而且如果可以更新資料表,必須有連結資料表的主鍵。

  • 檢查主要/外鍵關聯    請確定這些關聯性是以一致資料類型和大小的欄位為基礎。 SQL Server 不支援具有不同資料類型的聯接欄,以及外鍵約束中的大小。

  • 移除附件欄    SSMA 不會遷移包含附件資料行的資料表。

在執行 SSMA 之前,請先執行下列第一個步驟。

  1. 關閉 Access 資料庫。

  2. 確定連接至資料庫的目前使用者也關閉資料庫。

  3. 如果資料庫是.mdb 檔案格式,則移除使用者層級的安全性

  4. 備份您的資料庫。 如需詳細資訊,請參閱使用備份與還原程式保護您的資料

提示    考慮在桌上型電腦上安裝MICROSOFT SQL Server 速成版,最多可支援 10 GB,且可以免費且更輕鬆的方式來執行並檢查您的遷移。 當您連線時,請使用LocalDB 做為資料庫實例

提示    如果可能的話,請使用獨立版本的 Access。 如果您只能使用 Office 365,請在使用 SSMA 時,使用 Access 2010 資料庫引擎來遷移您的 Access 資料庫。 如需詳細資訊,請參閱Microsoft Access 資料庫引擎2010可轉散發元件

執行 SSMA

Microsoft 提供MICROSOFT SQL Server 移植小幫手(SSMA),讓遷移變得更容易。 SSMA 主要是遷移資料表,並選取不含參數的查詢。 表單、報表、宏及 VBA 模組不會轉換。 SQL Server 中繼資料瀏覽器會顯示您的 Access 資料庫物件與 SQL Server 物件,讓您查看兩個資料庫的目前內容。 如果您以後決定要轉移其他物件,則會將這兩個連線儲存在您的遷移檔案中。

注意事項    遷移程式可能需要一些時間,視資料庫物件的大小及必須傳輸的資料量而定。

  1. 若要使用 SSMA 遷移資料庫,請先按兩下下載的 MSI 檔案,以下載並安裝軟體。 請確定您已為電腦安裝適當的32或64位版本。

  2. 安裝 SSMA 之後,請在您的電腦上開啟它,最好是從擁有 Access 資料庫檔案的電腦上開啟。

    您也可以在可存取共用資料夾中網路存取權的電腦上開啟該檔案。

  3. 依照 SSMA 中的開始指示,提供基本資訊,例如 SQL Server 位置、Access 資料庫和要遷移的物件、連線資訊,以及您是否要建立連結資料表。

  4. 如果您要遷移至 SQL Server 2016 或更新版本,並想要更新連結資料表,請選取 [檢查工具] >專案設定>一般],即可新增 rowversion 資料行。

    [Rowversion] 欄位可協助避免記錄衝突。 Access 會在 SQL Server 連結資料表中使用這個 rowversion 欄位來判斷上次更新記錄的時間。 此外,如果您將 [rowversion] 欄位新增至查詢,Access 就會使用它,在更新作業之後重新選取列。 這可協助避免寫入衝突錯誤和記錄刪除案例,在 Access 偵測到原始提交的不同結果(例如,可能會發生浮點數資料類型及修改分欄. 不過,請避免使用表單、報表或 VBA 程式碼中的 rowversion 欄位。 如需詳細資訊,請參閱 rowversion。

    注意事項    避免混淆 rowversion 與時間戳記。 雖然 [關鍵字時間戳記] 是在 SQL Server 中 rowversion 的同義字,但您無法使用 rowversion 做為資料輸入的時間戳記方式。

  5. 若要設定精確的資料類型,請選取 [檢查工具] > [專案設定] >類型對應]。 例如,如果您只儲存英文文字,您可以使用Varchar而不是Nvarchar資料類型。

轉換物件

SSMA 會將 Access 物件轉換成 SQL Server 物件,但不會立即複製物件。 SSMA 會提供下列要遷移物件的清單,讓您決定是否要將它們移至 SQL Server 資料庫:

  • 表格和欄

  • 選取不含參數的查詢。

  • 主鍵和外鍵

  • 索引及預設值

  • Check 限制式(允許零長度欄屬性、欄驗證規則、表格驗證)

最佳做法是使用 SSMA 評定報告,其中會顯示轉換結果,包括錯誤、警告、資訊性訊息、執行遷移的時間估計,以及在您實際移動前要採取的個別錯誤校正步驟。物件.

轉換資料庫物件時,會採用來自 Access 中繼資料的物件定義,將它們轉換成等效的transact-sql (t-sql)語法,然後將此資訊載入到專案中。 然後,您就可以使用 SQL Server 或 SQL Azure 中繼資料瀏覽器來查看 SQL Server 或 SQL Azure 物件及其屬性。

若要將物件轉換、載入及遷移至 SQL Server,請依照本指南進行。

提示    當您成功地遷移 Access 資料庫之後,請儲存專案檔案以供日後使用,所以您可以再次遷移資料以進行測試或進行最終的遷移。

連結資料表

考慮安裝最新版本的 SQL Server OLE DB 和 ODBC 驅動程式,而不是使用隨 Windows 隨附的原生 SQL Server 驅動程式。 不僅較新的驅動程式更快,而且它們支援 Azure SQL 中舊版驅動程式的新功能。 您可以在每一台已轉換的資料庫所使用的電腦上安裝驅動程式。 如需詳細資訊,請參閱MICROSOFT OLE DB 驅動程式 18 FOR Sql servermicrosoft ODBC DRIVER 17 for sql server

在您遷移 Access 資料表之後,您可以連結至 SQL Server 中現在裝載資料的資料表。 直接從 Access 連結也提供更簡單的方式來查看資料,而不是使用更複雜的 SQL Server 管理工具。  您可以根據您的 SQL Server 資料庫管理員所設定的許可權,查詢及編輯連結資料。

注意事項    如果您在連結期間連結至您的 SQL Server 資料庫時建立 ODBC DSN,請在使用新應用程式的所有電腦上建立相同的 DSN,或以程式設計方式使用儲存在 DSN 檔案中的連接字串。

如需詳細資訊,請參閱連結至或匯入 AZURE SQL Server 資料庫中的資料,並匯入或連結至 SQL Server 資料庫中的資料。

技巧   請勿忘記在 Access 中使用連結資料表管理員,輕鬆地重新整理及重新連結資料表。 如需詳細資訊,請參閱管理連結的資料表

測試與修正

下列各節說明遷移期間您可能會遇到的常見問題,以及如何處理這些問題。

查詢

只會轉換選取的查詢;其他查詢不含,包括採用參數的選取查詢。 某些查詢可能無法完全轉換,而且在轉換過程中 SSMA 報告查詢錯誤。 您可以手動編輯不使用 T-sql 語法轉換的物件。 語法錯誤也可能需要手動將 Access 專用函數及資料類型轉換成 SQL Server 一個。 如需詳細資訊,請參閱比較 ACCESS sql 與 Sql SERVER TSQL

資料類型

Access 和 SQL Server 的資料類型相似,但請注意下列潛在問題。

大型數位    大型數位資料類型會儲存非貨幣、數值,且與 SQL Bigint 資料類型相容。 您可以使用這種資料類型來高效地計算大量數位,但需要使用 Access 16 (16.0.7812 或更新版本) .accdb 資料庫檔案格式,並使用64位版本的 Access 來執行更佳的工作。 如需詳細資訊,請參閱使用大型數位資料類型,並選擇64或32位版本的 Office

是/否    根據預設,Access 的 [是/否] 欄會轉換成 SQL Server 位欄位。 若要避免記錄鎖定, 確認 [位] 欄位已設定為 [不允許 Null 值]。 在 SSMA 中,您可以選取 [bit] 欄,將 [允許 Null 值]屬性設定為 [否]。 在 TSQL 中,使用CREATE 資料表ALTER table語句。

日期及時間    有數個日期和時間的考慮:

  • 如果資料庫的相容性等級是130(SQL Server 2016)或更高版本,且連結的資料表包含一或多個 datetime 或 datetime2 欄,則資料表可能會傳回結果中 #deleted 的訊息。 如需詳細資訊,請參閱Access 連結資料表至 SQL-Server 資料庫傳回 #deleted

  • 使用與 datetime 相比較大的日期範圍的datetime2資料類型。

  • 查詢 SQL Server 中的日期時,請考慮時間及日期。 例如:

    • 1/1/19 和1/31/19 之間的 DateOrdered 可能不會包含所有訂單。

    • 1/1/19 00:00:00 AM 與 1/31/19 11:59:59 PM 之間的 DateOrdered 會包含所有訂單。

附件   [附件] 資料類型會在 Access 資料庫中儲存檔案。 在 SQL Server 中,您有幾個選項可供考慮。 您可以從 Access 資料庫解壓縮檔案,然後考慮將連結儲存在您的 SQL Server 資料庫中。 或者,您也可以使用 FILESTREAM、FileTables 或遠端 BLOB 存放區(RBS),將附件儲存在 SQL Server 資料庫中。

超連結    Access 資料表具有 SQL Server 不支援的超連結欄。 根據預設,這些欄會在 SQL Server 中轉換成 Nvarchar (max)欄,但您可以自訂對應,以選擇較小的資料類型。 在您的 Access 方案中,如果您將控制項的hyperlink屬性設定為 true,您仍然可以使用表單和報表中的超連結行為。

多重值欄位    Access 多重值欄位會轉換為 SQL Server,成為包含分隔值集的 Ntext 欄位。 因為 SQL Server 不支援採用多對多關聯模型的多重值資料類型,因此可能需要進行額外的設計和轉換工作。

如需對應 Access 和 SQL Server 資料類型的詳細資訊,請參閱比較資料類型

注意事項    多重值欄位不會轉換,且在 Access 2010 中已停用。

如需詳細資訊,請參閱日期和時間類型字串和二進位類型,以及數位類型

Visual Basic

雖然 SQL Server 不支援 VBA,但請注意下列可能的問題:

查詢中的 VBA 函數    Access 查詢支援查詢欄中資料的 VBA 函數。 但使用 VBA 函數的 Access 查詢無法在 SQL Server 上執行,因此所有要求的資料都會傳送到 Microsoft Access 來處理。 在大多數情況下,這些查詢應該轉換為傳遞查詢

查詢中的使用者定義函數    Microsoft Access 查詢支援使用 VBA 模組中定義的函數來處理傳遞給它們的資料。 查詢可以是獨立查詢、表單/報表記錄來源的 SQL 語句、表單上的下拉式方塊與清單方塊的資料來源、報表和資料表欄位,以及預設或驗證規則運算式。 SQL Server 無法執行這些使用者定義的函數。 您可能需要手動重新設計這些函數,並將它們轉換成 SQL Server 上的儲存程式。

優化效能

到目前為止,使用新的後端 SQL Server 優化效能最重要的方法是,決定何時使用本機或遠端查詢。 當您將資料移轉至 SQL Server 時,您也會從檔案伺服器移至計算的用戶端伺服器資料庫模型。 請遵循下列一般指導方針:

  • 在用戶端上執行小型、唯讀查詢,以快速存取。

  • 在伺服器上執行長、讀/寫查詢,以充分利用較高的處理能力。

  • 使用篩選和匯總最小化網路流量,只傳輸您所需的資料。

在用戶端伺服器資料庫模型中優化效能

如需詳細資訊,請參閱建立傳遞查詢

以下是額外的建議指導方針。

在伺服器上放置邏輯    您的應用程式也可以使用視圖、使用者定義的函數、儲存程式、匯出欄位及觸發程式來集中及共用應用程式邏輯、商務規則與原則、複雜的查詢、資料驗證,以及參照完整性程式碼(在伺服器,而不是在用戶端。 問自己,您可以在伺服器上更快速地執行此查詢或工作嗎? 最後,測試每個查詢以確保最佳效能。

在表單和報表中使用視圖    在 Access 中,請執行下列動作:

  • 如果是表單,請使用唯讀表單的 SQL 視圖,以及將讀/寫表單的 SQL 索引視圖做為記錄來源。

  • 針對報表,請使用 SQL 視圖作為記錄來源。 不過,您可以為每個報表建立一個單獨的視圖,這樣您就能更輕鬆地更新特定報表,而不會影響其他報表。

在表單或報表中最小化載入資料    除非使用者要求,否則不會顯示資料。 例如,請讓 [記錄來源] 屬性保持空白,讓使用者選取表單上的篩選,然後使用您的篩選填入 [記錄來源] 屬性。 或者,使用 DoCmd. OpenForm 和 DoCmd. OpenReport 的 where 子句,顯示使用者所需的確切記錄。 考慮關閉記錄流覽。

小心使用異類查詢   避免執行結合本機 Access 資料表與 SQL Server 連結資料表的查詢,有時稱為混合式查詢。 此類型的查詢仍需要 Access 將所有 SQL Server 資料下載到本機電腦,然後執行查詢,不會在 SQL Server 中執行查詢。

使用本機資料表的時機    考慮針對很少變更的資料(例如國家或地區的省/市/自治區清單)使用本機資料表。 靜態表格通常是用來進行篩選,而且可以在 Access 前端執行更佳的功能。

如需詳細資訊,請參閱資料庫引擎優化顧問使用效能分析器來優化 Access 資料庫,以及優化連結至 SQL Server 的 Microsoft Office Access 應用程式

另請參閱

Azure 資料庫移轉指南

Microsoft 資料移轉博客

Microsoft 對 SQL Server 遷移、轉換與升遷https://www.fmsinc.com/consulting/sqlserverupsizing.aspx的存取權

共用 Access 桌面資料庫的方法

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×