VS2019 VB实现Access MDB跨库转移记录无报错无数据写入求助

核心错误排查 #

• 参数绑定逻辑错误：你当前代码中InsertCommand的参数直接固定为了"field1"、"field2"两个字面量字符串，没有和查询得到的DataTable列做关联，导致即使执行插入也不会读取源数据的字段值，甚至可能触发隐式的类型/约束错误被吞（你当前只输出了简略错误描述，可能没拿到完整异常信息）
• 缺少连接状态校验：需要确认con1和connTr两个连接在执行Fill和Update前是已打开状态，若连接未打开也会静默执行失败
• 权限校验：确认目标MDB文件所在目录有读写权限，文件未被其他程序（比如Access桌面端）独占锁定

修正后的代码 #

TransferConnection(nfile) 'Open connection to second DB
Dim con1 As OleDb.OleDbConnection = DBConnect.getDbConnection()
Dim connTr As OleDb.OleDbConnection = DBConnect.getTEMPDbConnection

Try
    ' 提前打开两个连接
    If con1.State <> ConnectionState.Open Then con1.Open()
    If connTr.State <> ConnectionState.Open Then connTr.Open()

    ' 从源库查询数据
    Dim da As New OleDb.OleDbDataAdapter("SELECT field1,field2 from TabName", con1)

    ' 配置插入命令，Access的OleDb驱动不支持命名参数，用?做占位符按顺序匹配更稳妥
    da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO TabName  (field1,field2) VALUES (?, ?)", connTr)

    ' 正确绑定参数到DataTable的对应列，OleDbType和长度请和你实际表结构匹配
    da.InsertCommand.Parameters.Add("@cb", OleDbType.VarChar, 255, "field1") ' 最后一个参数为源表列名
    da.InsertCommand.Parameters.Add("@frn", OleDbType.VarChar, 255, "field2")

    da.AcceptChangesDuringFill = False

    Dim dt As New DataTable
    da.Fill(dt)

    ' 执行插入并获取受影响行数，方便验证执行结果
    Dim affectedRows As Integer = da.Update(dt)
    MessageBox.Show($"成功插入{affectedRows}条记录")

Catch ex As Exception
    ' 输出完整异常信息方便排查问题
    MessageBox.Show(ex.ToString() & " Errore nella procedura di archiviazione")
Finally
    ' 执行完成后关闭连接释放资源
    If con1.State = ConnectionState.Open Then con1.Close()
    If connTr.State = ConnectionState.Open Then connTr.Close()
End Try

更高效的替代实现方案 #

如果两个Access数据库的表结构完全一致，不需要做中间数据处理的话，可以直接用Access原生的跨库插入语法，不需要把数据读到内存再写入，效率更高、代码更简洁：

Dim con1 As OleDb.OleDbConnection = DBConnect.getDbConnection()
Try
    If con1.State <> ConnectionState.Open Then con1.Open()
    ' 替换为你自己的目标MDB文件完整路径
    Dim targetMdbPath As String = "C:\your_path\target.mdb"
    Dim cmd As New OleDb.OleDbCommand($"INSERT INTO TabName IN '{targetMdbPath}' SELECT field1, field2 FROM TabName", con1)
    Dim affectedRows As Integer = cmd.ExecuteNonQuery()
    MessageBox.Show($"成功插入{affectedRows}条记录")
Catch ex As Exception
    MessageBox.Show(ex.ToString())
Finally
    If con1.State = ConnectionState.Open Then con1.Close()
End Try

内容的提问来源于stack exchange，提问作者Jayelef