將 Access 資料庫移轉至 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 資料庫效能更好，尤其因為有大小 1 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 之前，請先執行下列第一組步驟。

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

祕訣請考慮在桌面上安裝最多支援 10 GB 的 Microsoft SQL Server Express 版本，這是一種免費且更容易執行及檢查移轉的方式。當您連線時，請使用 LocalDB 做為資料庫執行個體。

祕訣如果可能，請使用獨立版本的 Access。

執行 SSMA

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

附註移轉流程可能需要一些時間，視您的資料庫物件大小以及必須傳輸的資料量而定。

若要使用 SSMA 移轉資料庫，請先按兩下下載的 MSI 檔案來下載並安裝軟體。請確定您已安裝適合您電腦的 32 或 64 位元版本。
安裝 SSMA 之後，請在桌面上開啟它，最好是從具有 Access 資料庫檔案的電腦開啟它。

您也可以在可從共用資料夾中的網路存取 Access 資料庫的電腦上開啟該資料庫。
請依照 SSMA 中的開頭指示，提供基本資訊，例如 SQL Server 位置、Access 資料庫和要移轉的物件、連線資訊，以及您是否要建立連結的資料表。
如果您要移轉至 SQL Server 2016 或更新版本，並想要更新連結的資料表，請選取 [校閱工具] > [專案設定] > [一般] 來新增 rowversion 欄。

rowversion 欄有助於避免記錄衝突。 Access 會在 SQL Server 資料表中使用此 rowversion 欄來判斷上次更新記錄的時間。此外，如果您將 rowversion 欄新增至查詢，Access 會在更新作業後使用該欄重新選取資料列。這有助於避免寫入衝突錯誤，並記錄 Access 偵測到原始提交中不同結果時可能發生的刪除案例，例如可能會發生浮點數字資料類型，以及修改欄的觸發程式，從而提升效率。不過，請避免在表單、報表或 VBA 程式碼中使用 rowversion 欄。如需更多資訊，請參閱 rowversion。

附註避免混淆 rowversion 與時間戳記。雖然關鍵字時間戳記是 SQL Server 中 rowversion 的同義字，但您無法使用 rowversion 做為資料輸入的時間戳記方式。
若要設定精確的資料類型，請選取 [檢閱工具] > [專案設定] > [類型對應]。例如，如果您只儲存英文文字，您可以使用 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 Driver 18 for SQL Server 和 Microsoft ODBC Driver 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 [延長的日期/時間] 延伸的資料類型對應到日期時間 2 資料類型，該資料類型具有較大的日期和時間範圍。如需詳細資訊，請參閱使用 [延伸的日期/時間] 資料類型。
查詢 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 資料庫中的附件。

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

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

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

附註不會轉換多重值欄位。

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

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 檢視做為記錄來源。不過，請為每個報表建立個別的檢視，讓您可以更輕鬆地更新特定報表，而不會影響其他報表。

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

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

何時使用本機資料表 請考慮使用本機資料表來處理極少變更的資料，例如國家或地區中的州或省的清單。靜態資料表常用於篩選，可在 Access 前端執行得更好。

如需詳細資訊，請參閱 Database Engine Tuning Advisor、使用效能分析器將 Access 資料庫最佳化，以及將連結至 SQL Server 的 Microsoft Office Access 應用程式最佳化。