將 Access 資料庫移轉到 SQL Server

套用到
Microsoft 365 Access Access 2024 Access 2021 Access 2019 Access 2016

我們都有限制，Access 資料庫也不例外。 例如，Access 資料庫的大小限制為 2 GB，且不能支援超過 255 個同時使用者。 因此，當 Access 資料庫需要更上一層樓時，您可以移轉到 SQL Server。 SQL Server (內部部署或在 Azure 雲端中) 支援較大量資料、更多同時使用者，而且容量大於 JET/ACE 資料庫引擎。 本指南可讓您順利開始 SQL Server 之旅、協助保留您所建立的 Access 前端解決方案，並希望能協助您在未來的資料庫解決方案中使用 Access。 使用 Microsoft SQL Server 移轉小幫手 (SSMA) 成功移轉，請遵循下列階段。

資料庫遷移至 SQL Server 的階段

開始之前

下列各節提供背景和其他資訊，協助您開始使用。

關於分割資料庫

所有 Access 資料庫物件都可以放在一個資料庫檔案中，也可以儲存在兩個資料庫檔案中：前端資料庫和後端資料庫。 這稱為分割資料庫，其設計可促進網路環境中的共用。 後端資料庫檔案只能包含資料表和關聯性。 前端檔案必須只包含所有其他物件，包括表單、報表、查詢、巨集、VBA 模組，以及後端資料庫的連結資料表。 當您移轉 Access 資料庫時，類似該 SQL Server 中的分割資料庫，針對現在位於伺服器上的資料作為新的後端。

因此，您仍然可以使用連結至 SQL Server 資料表的前端 Access 資料庫。 您可以有效地獲得 Access 資料庫所提供的快速應用程式開發優點，以及 SQL Server 的可擴充性。

SQL Server 優點

還是需要一些說服力才能移轉到 SQL Server？ 以下是一些額外考量建議：

  • 更多同時使用者 SQL Server 能處理遠多於 Access 的同時使用者，且當新增更多使用者時，記憶體需求會被最小化。
  • 增加可用性使用 SQL Server，你可以在資料庫使用時動態備份，無論是增量備份還是完整備份。 因此，您不需要強制使用者離開資料庫，即可備份資料。
  • 高效能與可擴展性SQL Server 資料庫通常比 Access 資料庫表現更好，尤其是大型、TB 級的資料庫。 此外，SQL Server 透過平行處理查詢，在單一處理程序中使用多個原生線程控制使用者要求，以更快且有效率地處理查詢。
  • 安全性提升透過受信任的連線，SQL Server 與 Windows 系統安全整合，提供單一整合的網路與資料庫存取，並結合兩者安全系統的優點。 這可讓您更輕鬆地管理複雜的安全性配置。 SQL Server 是存放身分證號碼、信用卡資料和地址等敏感性資訊的理想儲存空間。
  • 立即恢復若作業系統當機或停電，SQL Server 能在數分鐘內自動恢復資料庫至一致狀態，無需資料庫管理員介入。
  • VPN 的使用 VPN (Access 和虛擬私人網路) 並不合適。 但使用 SQL Server，遠端使用者仍可在桌面上使用 Access 前端資料庫，以及位於 VPN 防火牆後方的 SQL Server 後端資料庫。
  • Azure SQL Server 除了 SQL Server 的優點外，還提供動態可擴展性且無停機、智慧優化、全球可擴展性與可用性、消除硬體成本及減少管理。

選擇最佳的 Azure SQL Server 選項

如果您要移轉到 Azure SQL Server，有三個選項可供選擇，各自都有不同的優點：

  • 單一資料庫/彈性池此選項有自己的資源集合，透過 SQL Database 伺服器管理。 單一資料庫就像是 SQL Server 中包含的資料庫。 您也可以新增彈性資料庫，這是資料庫集合，其中包含透過 SQL Database 伺服器管理的共用資源集合。 最常使用的 SQL Server 功能隨附內建備份、修補和復原。 但是無法保證確切的維護時間，而從 SQL Server 移轉可能會有困難。
  • 受管理實例 此選項是系統與使用者資料庫的集合，並擁有一組共享的資源。 受控執行個體就像是 SQL Server 資料庫的執行個體，與內部部署 SQL Server 高度相容。 受控執行個體具有內建的備份、修補、復原，而且很容易從 SQL Server 移轉。 不過，有少數 SQL Server 功能無法使用，且無法保證確切的維護時間。
  • Azure 虛擬機 這個選項允許你在 Azure 雲端的虛擬機器中執行 SQL Server。 您擁有 SQL Server 引擎的完整控制權，以及簡易的移轉路徑。 但您需要管理備份、修補程序和復原。

欲了解更多資訊，請參閱「選擇您的資料庫遷移路徑至Azure」及「什麼是 Azure SQL？」。

第一組步驟

您可以在執行 SSMA 之前，先解決一些有助於簡化移轉流程的問題：

  • 新增表格索引與主鍵 確保每個 Access 表格都有索引和主鍵。 SQL Server 要求所有資料表至少要有一個索引，而且連結的資料表必須有主索引鍵，才能更新資料表。
  • 檢查主鍵/外鍵關係 確保這些關聯基於資料型別和大小一致的欄位。 SQL Server 不支援外部索引鍵限制式中具有不同資料類型和大小的連結資料行。
  • 移除附件欄位 SSMA 不會遷移包含附件欄位的資料表。

執行 SSMA 之前，請先執行下列第一組步驟。

  1. 關閉 Access 資料庫。
  2. 確定目前連線到資料庫的使用者也已關閉資料庫。
  3. 如果資料庫是.mdb檔案格式，則移除使用者層級的安全。
  4. 備份資料庫。 如需詳細資訊，請參閱使用備份與還原處理程序保護您的資料。

提示考慮在桌機上安裝 Microsoft SQL Server Express 版，支援最高 10 GB，是免費且更方便的遷移檢查方式。 連線時，使用 LocalDB 作為資料庫實例。

提示 如果可能，請使用獨立版本的 Access。

執行 SSMA

Microsoft提供Microsoft SQL Server 移轉小幫手 (SSMA) ，讓遷移更為便利。 SSMA 主要移轉資料表並選取沒有參數的查詢。 表單、報表、巨集和 VBA 模組不會轉換。 SQL Server 中繼資料總管會顯示您的 Access 資料庫物件和 SQL Server 物件，讓您檢閱這兩個資料庫的目前內容。 如果您未來決定要傳輸其他物件，這兩個連線會儲存在您的移轉檔案中。

註 遷移過程可能會花些時間，視資料庫物件大小及需傳輸的資料量而定。

  1. 要使用 SSMA 遷移資料庫，首先透過雙擊下載的 MSI 檔案來 下載 並安裝軟體。 請確定您已安裝適合您電腦的 32 或 64 位元版本。
  2. 安裝 SSMA 之後，請在桌面上開啟它，最好是從具有 Access 資料庫檔案的電腦開啟它。
    您也可以在可從共用資料夾中的網路存取 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 資料庫：

  • 資料表和資料行
  • 選取 [沒有參數的查詢]。
  • 主索引鍵和外部索引鍵
  • 索引和預設值
  • 檢查限制式 (允許零長度欄屬性、資料行驗證規則、資料表驗證)

最佳作法是使用顯示轉換結果的 SSMA 評估報告，包括錯誤、警告、資訊訊息、執行移轉的時間估計值，以及實際移動物件之前要採取的個別錯誤修正步驟。

轉換資料庫物件時，會將 Access 元資料中的物件定義轉換成等效的 Transact-SQL (T-SQL) 語法，然後將這些資訊載入專案中。 接著，您可以使用 SQL Server 或 SQL Azure 中繼資料總管來檢視 SQL Server 或 Azure SQL Database 物件及其屬性。

要轉換、載入及遷移物件到 SQL Server，請遵循此指南。

提示 成功遷移 Access 資料庫後，請將專案檔案保存以備後用，以便再次遷移資料以供測試或最終遷移使用。

請考慮安裝最新版本的 SQL Server OLE DB 和 ODBC 驅動程式，而不是使用隨 Windows 一起出貨的原生 SQL Server 驅動程式。 新驅動程式不僅更快，還支援 Azure SQL 中舊驅動程式沒有的新功能。 您可以在每部使用轉換資料庫的電腦上安裝驅動程式。 欲了解更多資訊，請參閱 Microsoft OLE DB 驅動程式 18 for SQL Server 及 Microsoft ODBC 驅動程式 17 for SQL Server。

移轉 Access 資料表之後，您可以連結到現在託管您資料的 SQL Server 中的資料表。 直接從 Access 連結也能讓您以更簡單的方式檢視資料，而不是使用更複雜的 SQL Server 管理工具。 你可以根據 SQL Server 資料庫管理員設定的權限查詢和編輯連結資料。

註如果你在連結過程中連結到 SQL Server 資料庫時建立 ODBC DSN，要麼在所有使用新應用程式的機器上建立相同的 DSN，要麼以程式方式使用 DSN 檔案中儲存的連接字串。

如需詳細資訊，請參閱連結或從 Azure SQL 伺服器資料庫中匯入資料，以及匯入或連結至 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 中，您可以選取位欄，將 [允許 Nulls] 屬性設為 NO。 在 TSQL 中，請使用 CREATE TABLE 或 ALTER TABLE 語句。

日期與時間 有幾個日期和時間的考量：

  • 如果資料庫的相容性層級為 130 (SQL Server 2016) 或更高版本，且連結的資料表包含一或多個日期時間或日期時間2 欄，則資料表可能會傳回結果中的訊息 #deleted。 欲了解更多資訊，請參閱 Access 連結表 SQL-Server 資料庫回報 #deleted。

  • 使用 Access 日期/時間資料類型對應至日期時間資料類型。 使用 Access Date/Time Extended 資料類型來對應到 datetime2 資料型別，該類型具有較大的日期與時間範圍。 如需詳細資訊，請參閱使用 [延伸的日期/時間] 資料類型。

  • 查詢 SQL Server 中的日期時，請考慮時間和日期。 例如：

    • 2019 年 1 月 1 日到 2019 年 1 月 31 日之間的 DateOrdered 不能包含所有訂單。
    • 2019 年 1 月 1 日上午 00:00:00 到 2019 年 1 月 31 日下午 11:59:59 之間的 DateOrdered 包含所有訂單。

依附 附件資料類型會在 Access 資料庫中儲存檔案。 在 SQL Server 中，您有幾個選項可以考慮。 您可以從 Access 資料庫擷取檔案，然後考慮將檔案連結儲存在 SQL Server 資料庫中。 或者，您可以使用 FILESTREAM、FileTables 或遠端 BLOB 存放區 (RBS) 來保留儲存在 SQL Server 資料庫中的附件。

超連結存取資料表有超連結欄位，但 SQL Server 不支援。 根據預設，這些欄會轉換為 SQL Server 中的 nvarchar (最大量) 欄，但您可以自訂對應以選擇較小的資料類型。 在 Access 解決方案中，如果您將控制項的 Hyperlink 屬性設為 True，您仍然可以在表單和報表中使用超連結行為。

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

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

註 多值欄位不會被轉換。

欲了解更多資訊，請參閱 日期與時間類型、 字串與二進位類型，以及 數值類型。

Visual Basic

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

VBA 在查詢中的功能 存取查詢支援查詢欄位資料的 VBA 功能。 但使用 VBA 函數的 Access 查詢無法在 SQL Server 上執行，因此所有要求的資料都會傳遞給 Microsoft Access 進行處理。 在大多數的情況下，這些查詢應該轉換成 傳遞查詢。

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

效能最佳化

到目前為止，使用新的後端 SQL Server 將效能最佳化的最重要方式，是決定何時使用本機或遠端查詢。 當您將資料移轉到 SQL Server 時，也會從檔案伺服器移轉到用戶端伺服器資料庫的運算模型。 請依照下列一般準則：

  • 在用戶端上執行小型的唯讀查詢，以最快速地存取。
  • 在伺服器上執行冗長的讀/寫查詢，以利用更大的處理能力。
  • 使用篩選和彙總將網路流量最小化，僅傳輸您需要的資料。

優化用戶端伺服器資料庫模型的效能 欲了解更多資訊，請參閱 建立直通查詢。

以下是其他建議的指導方針。

把邏輯放到伺服器上 您的應用程式也可以利用檢視、使用者自訂函式、儲存程序、計算欄位與觸發器，集中並分享應用程式邏輯、商業規則與政策、複雜查詢、資料驗證及參照完整性程式碼，設置於伺服器端，而非用戶端。 詢問您自己，這個查詢或工作是否能在伺服器上更好更快地執行？ 最後，測試每個查詢以確保最佳效能。

在表單和報告中使用檢視 在 Access 中，請執行以下操作：

  • 對於表單，請將唯讀表單的 SQL 檢視和讀取/寫入表單的 SQL 索引檢視表做為記錄來源。
  • 針對報表，請使用 SQL 檢視做為記錄來源。 不過，請為每個報表建立個別的檢視，讓您可以更輕鬆地更新特定報表，而不會影響其他報表。

盡量減少表單或報告中的載入資料 在使用者要求之前，不要顯示資料。 例如，將 recordsource 屬性保留空白，讓使用者選取表單上的篩選，然後使用篩選填入 recordsource 屬性。 或者，使用 DoCmd.OpenForm 和 DoCmd.OpenReport 的 where 子句來顯示使用者所需的確切記錄。 考慮關閉記錄瀏覽。

對於異質查詢要小心避免執行結合本地存取表與 SQL Server 連結表的查詢，有時稱為混合查詢。 此類型的查詢仍然需要 Access 將所有 SQL Server 資料下載到本機電腦，然後執行查詢，而不會在 SQL Server 中執行查詢。

何時使用本地表格 考慮使用地方表格來處理很少變動的資料，例如某個國家或地區的州或省份列表。 靜態資料表常用於篩選，可在 Access 前端執行得更好。

欲了解更多資訊，請參閱 Database Engine Tuning Advisor、使用效能效能分析器優化 Access 資料庫，以及優化連結至 SQL Server 的 Microsoft Office Access 應用程式。

另請參閱

Azure 資料庫移轉指南

Microsoft 資料遷移部落格

Microsoft 存取 SQL Server 遷移、轉換與擴充

共用 Access 桌面資料庫的方法