將 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 使用量 Access 和虛擬專用網 (VPN) 無法一起使用。但是使用 SQL Server 時，遠端使用者仍可使用桌面上的 Access 前端資料庫，以及位於 VPN 防火牆後方的 SQL Server 後端資料庫。
Azure SQL Server 除了 SQL Server 的優點之外，還提供不停機、智慧優化、全域擴充性和可用性、消除硬體成本，以及降低系統管理的動態擴充性。

選擇最佳 Azure SQL Server 選項

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

單一資料庫/彈性集區 此選項擁有一組由 SQL 資料庫伺服器管理的資源。單一資料庫就像 SQL Server 中的內含資料庫。您也可以新增彈性集區，這是資料庫集合，其中包含透過 SQL 資料庫伺服器管理的共用資源集合。最常使用的 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檔格式，則移除用戶層級安全性。
備份您的資料庫。如需詳細資訊，請參閱使用備份和還原程式保護您的數據。

祕訣請考慮在您的桌面上安裝 Microsoft SQL Server Express 版本，它支援高達 10 GB，且是一種可執行和檢查移轉的免費且更輕鬆的方式。當您連線時，請使用 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 或 SQL Azure 物件及其屬性。

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

祕訣成功移轉 Access 資料庫後，請儲存專案檔以供日後使用，以便再次移轉數據以進行測試或最終移轉。

鏈接資料表

請考慮安裝最新版本的 SQL Server OLE DB 和 ODBC 驅動程式，而不是使用隨 Windows 一起出貨的原生 SQL Server 驅動程式。不僅較新的驅動程式更快，還支援舊版驅動程式不支援的新 Azure SQL 功能。您可以在每部使用轉換資料庫的電腦上安裝驅動程式。如需詳細資訊，請參閱適用於 SQL Server 的 Microsoft OLE DB 驅動程式 18 和適用於 SQL Server 的 Microsoft ODBC Driver 17。

在您移轉 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 中，您可以選取位欄，將 [允許 Nulls ] 屬性設為 NO。在 TSQL 中，使用 CREATE TABLE 或 ALTER TABLE 語句。

日期和時間 有幾種日期和時間考慮：

如果資料庫的相容性層級為 130 (SQL Server 2016) 或更高版本，且鏈接數據表包含一或多個日期時間或日期時間2 欄，則數據表可能會傳回結果中 #deleted 訊息。如需詳細資訊，請參閱 Access 鏈接資料表 SQL-Server 資料庫傳回 #deleted。
使用 Access 日期/時間資料類型對應至日期時間數據類型。使用 Access [延長的日期/時間] 資料類型對應到日期時間 2 資料類型，該數據類型具有較大的日期和時間範圍。如需詳細資訊，請參閱使用 [延長的日期/時間] 數據類型。
在 SQL Server 中查詢日期時，請考慮時間和日期。例如：
- DateOrdered Between 1/1/19 and 1/31/19 may may not include all orders.
- DateOrdered Between 1/1/19 00：00：00 And 1/31/19 11：59：59 PM does include all orders.

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

連結 Access 數據表具有 SQL Server 不支援的超連結欄。根據預設，這些欄將會轉換為 nvarchar (SQL Server 中的最大) 欄，但您可以自定義對應以選擇較小的數據類型。在 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 前端執行得更好。

如需詳細資訊，請參閱資料庫引擎調整建議程式、使用效能分析器優化 Access 資料庫，以及優化連結至 SQL Server 的 Microsoft Office Access 應用程式。