Access 大量資料寫入效能提升方案：文字檔中轉 + SQL 批次匯入

痛點分析

Access 透過 DAO/ADO 逐筆 INSERT 寫入 30 萬筆資料，耗時數分鐘甚至更久，主要瓶頸在：

• 每筆圍繞 Jet/ACE 引擎的交易開銷
• 索引即時維護、鎖定升級
• 網路/介面層往返延遲

核心方案：資料 → 文字檔 → MDB

利用 Access 內建文字檔匯入引擎，繞過逐筆寫入路徑，直達儲存層。

實作步驟

步驟 1：產生標準 CSV 文字檔

• 每行一筆記錄，第一行為標題列
• 欄位間用逗號分隔，符合 CommaText 規範（含引號、逗號、換行的欄位需雙引號包裹並跳脫）
• 編碼建議 ANSI 或 UTF-8（視 Access 版本）

步驟 2：SQL 批次匯入

SELECT * INTO tableName1
FROM [Text;DATABASE=路徑].[檔名.txt]

關鍵語法細節

實測效能數據

• 30 萬筆資料：約 10 秒 完成匯入
• 對比逐筆 DAO 寫入：提升 100~500 倍

必要前置條件

• 啟用 VBA 巨集安全性：檔案 → 選項 → 信任中心 → 巨集設定 → 啟用所有巨集
• 或在程式碼中設定 Application.AutomationSecurity = msoAutomationSecurityLow
• 確保文字檔格式正確（可先用 Access 手動「外部資料 → 文字檔」導入驗證）

適用範圍與限制

擴展：VBA 完整自動化範例

Sub ImportBigData()
    Dim txtPath As String, txtFile As String, tblName As String
    txtPath = "C:\Data\"
    txtFile = "bigdata.txt"
    tblName = "TargetTable"
    
    On Error Resume Next
    DoCmd.DeleteObject acTable, tblName
    On Error GoTo 0
    
    Dim sql As String
    sql = "SELECT * INTO [" & tblName & "] FROM [Text;DATABASE=" & txtPath & "].[" & txtFile & "]"
    CurrentDb.Execute sql, dbFailOnError
    MsgBox "匯入完成：" & CurrentDb.TableDefs(tblName).RecordCount & " 筆"
End Sub

文字檔中轉法是 Access 大量資料匯入的「殺手鐧」，善用內建引擎，事半功倍。

1. 上一篇文章： Oracle 資料表匯出 INSERT 腳本：SQL*Plus SPOOL 實戰技巧
2. 下一篇文章： PowerDesigner 教程（六）：概念資料模型（CDM）關聯關係完整指南

關於SqlServer

SQL Server 叢集命名執行個體啟動失敗：UDP 1434 連接埠被封鎖導致 SQL Browser 無法解析

案例背景 國企對外服務平台運行於 Windows 2008

SQL Server 2008 APPLY 運算子詳解：CROSS APPLY vs OUTER APPLY

APPLY 運算子概念 APPLY 允許將左側資料表的每一列

SQL Server 復原模式深度解析：大量記錄復原模式 (BULK_LOGGED) 原理、優缺點與最佳實務

交易記錄檔暴增是 DBA 常見痛點。本文深度解析 大量記錄復

SQL Server 交易與資料庫管理核心概念圖解 (交易控制、備份還原、資料庫選項)

管理 SQL Server 資料庫核心在於交易處理與資料庫維