Access MDB 表數據整表刷新：TRUNCATE 後批量導入的 SQL 實現

VBA 實現

Public Sub RefreshTableFromGrid()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    
    Set db = CurrentDb
    
    ' 1. 清空目標表 (保留結構)
    db.Execute "DELETE * FROM TargetTable", dbFailOnError
    
    ' 2. 遍歷 DataGridView (假設綁定 Recordset)
    Set rs = db.OpenRecordset("TargetTable", dbOpenDynaset, dbAppendOnly)
    
    For Each row In DataGridView1.Rows
        If Not row.IsNewRow Then
            rs.AddNew
            rs!Field1 = row.Cells("Col1").Value
            rs!Field2 = row.Cells("Col2").Value
            ' ...
            rs.Update
        End If
    Next
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

ADO.NET (C#) 批量導入

using (var conn = new OleDbConnection(connStr)) {
    conn.Open();
    // 1. 清空
    new OleDbCommand("DELETE FROM TargetTable", conn).ExecuteNonQuery();
    
    // 2. 批量插入 (參數化)
    string sql = "INSERT INTO TargetTable (F1, F2) VALUES (?, ?)";
    using (var cmd = new OleDbCommand(sql, conn)) {
        cmd.Parameters.Add("@F1", OleDbType.VarWChar);
        cmd.Parameters.Add("@F2", OleDbType.Integer);
        
        foreach (DataGridViewRow row in dgv.Rows) {
            if (row.IsNewRow) continue;
            cmd.Parameters["@F1"].Value = row.Cells["Col1"].Value;
            cmd.Parameters["@F2"].Value = row.Cells["Col2"].Value;
            cmd.ExecuteNonQuery();
        }
    }
}

大數據量優化：SQLBulkCopy (需 SQL Server)

Access 無原生 BulkCopy，大量數據建議：

1. 導出 CSV
2. 用 DoCmd.TransferText 導入
3. 或臨時遷移至 SQL Server LocalDB