將 Access 資料庫移轉至 SQL Server

開始之前

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

關於分割資料庫

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

因此，您仍然可以維護前端 Access 資料庫與 SQL Server 資料表的連結資料表。實際上，您可以衍生 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 前端資料庫，而 SQL Server 後端則位於 VPN 防火牆後面。
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 Server 選項。

第一個步驟

執行 SSMA 之前，您可以正面處理一些問題，協助簡化移移程式：

新增資料表索引和主鍵 請確定每個 Access 資料表都有索引和主鍵。 SQL Server 要求所有資料表至少都有一個索引，而且如果資料表可以更新，則要求連結資料表具有主鍵。
檢查主鍵/外鍵關係 請確定這些關係是以資料類型和大小一致的欄位為基礎。 SQL Server 不支援外鍵限制條件中資料類型和大小不同的聯聯資料行。
移除附件欄 SSMA 不會遷移包含附件資料行的資料表。

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

關閉 Access 資料庫。
請確定目前連接至資料庫的使用者也會關閉資料庫。
如果資料庫為 .mdb 檔案格式，則移除使用者層級安全性。
備份資料庫。詳細資訊請參閱使用備份和還原程式保護您的資料。

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

提示如果可以，請使用獨立的 Access 版本。如果您只能使用 Microsoft 365，請使用 Access 2010 資料庫引擎在使用 SSMA 時，將 Access 資料庫進行遷移。詳細資訊請參閱 Microsoft Access 資料庫引擎 2010 可重新發佈。

執行 SSMA

Microsoft 提供 Microsoft SQL Server 移 (SSMA) ，讓移移更容易。 SSMA 主要會遷移資料表，並選取沒有參數的查詢。表單、報表、宏和 VBA 模組不會轉換。 SQL Server 中繼資料管理器會顯示 Access 資料庫物件和 SQL Server 物件，讓您查看這兩個資料庫目前的內容。這兩個連結會儲存于移轉檔案中，如果您日後決定要傳輸其他物件。

注意移轉程式可能需要一些時間，視資料庫物件的大小和必須傳輸的資料量而不同。

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

您也可以在可以從共用資料夾的網路存取 Access 資料庫的機器上開啟。
請遵循 SSMA 中的開始指示，提供基本資訊，例如 SQL Server 位置、要遷移的 Access 資料庫和物件、連接資訊，以及是否要建立連結資料表。
如果您要移轉至 SQL Server 2016 或更新版本，並想要更新連結的資料表，請選取一般專案設定中的 >工具>資料行。

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 中繼資料 Explorer 來查看 SQL Server 或 SQL Azure 物件及其屬性。

若要轉換、載入及將物件遷移到 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 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 中，您可以選取位欄，將 Allow 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 Between 1/1/19 00：00：00 AM and 1/31/19 11：59：59 PM 確實包含所有訂單。

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

超連結 Access 資料表具有 SQL Server 不支援的超連結欄。根據預設，這些欄在 SQL Server 中會轉換成 Nvarchar (最多) 欄，但您可以自訂地圖以選擇較小的資料類型。在 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 視圖做為記錄來源。不過，請為每個報表建立個別的視圖，以便更輕鬆地更新特定報表，而不會影響其他報表。

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

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

何時使用本地資料表 請考慮針對極少變更的資料使用本地資料表，例如國家/地區或地區的州/省或城市清單。靜態資料表通常用於篩選，在 Access 前端執行效果更好。

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