如何将本地MS Access数据库表数据无循环插入Oracle对应表

方案1：最高效（无需循环，单SQL完成） #

这种方法完全不需要写遍历逻辑，利用Access自身的查询能力直接跨库插入：

• 先在当前Access数据库中创建Oracle的RET2_PARTS_ORACLE表的ODBC链接表，命名为RET2_PARTS_ORACLE_LINK
• 直接执行对应插入SQL即可完成全量插入，对应VBA代码仅需一行：

CurrentDb.Execute "INSERT INTO RET2_PARTS_ORACLE_LINK SELECT * FROM RET_PARTS", dbFailOnError

这种方式是Access底层做批量传输，比自行编写循环逻辑性能高5~10倍。

方案2：基于现有代码的批量优化（无需创建链接表） #

你现有代码已经获取了Access侧的Recordset，无需逐行执行单条INSERT，可通过参数化批量提交的方式减少和Oracle的交互次数，修改后的完整代码如下：

Sub subFastUpload()
On Error GoTo Err_subFastUpload

    Dim intlength As Integer
    intlength = 8
    Dim strServer As String
    strServer = "MARS"
 
    Const maxItems = 500
    Dim batchCount As Integer ' 批量计数
   
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim objCon As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim strTable As String
    Dim fldCount As Integer
    Dim i As Integer
  
    Set dbs = CurrentDb()
    ' 按需求读取本地Access的RET_PARTS表
    Set rst = dbs.OpenRecordset("RET_PARTS")
    fldCount = rst.Fields.Count ' 两表结构一致可直接复用字段数
  
    Dim mars_pass As String
    mars_pass = "XXXXXXX"
    If strServer = "MARS" Then
        strTable = Environ("UserName") & ".RET2_PARTS_ORACLE"
        objCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MARSNEW.123;User ID=" & Environ("UserName") & ";Password=" & mars_pass & ";"
    End If
    objCon.Open
    objCon.CursorLocation = adUseClient
    objCmd.ActiveConnection = objCon
    objCmd.CommandType = adCmdText
    
    ' 拼接参数化插入SQL
    Dim sqlFields As String, sqlParams As String
    For i = 0 To fldCount - 1
        sqlFields = sqlFields & rst.Fields(i).Name & ","
        sqlParams = sqlParams & "?,"
    Next
    sqlFields = Left(sqlFields, Len(sqlFields) - 1)
    sqlParams = Left(sqlParams, Len(sqlParams) - 1)
    objCmd.CommandText = "INSERT INTO " & strTable & "(" & sqlFields & ") VALUES (" & sqlParams & ")"
    
    ' 预添加参数
    For i = 0 To fldCount - 1
        objCmd.Parameters.Append objCmd.CreateParameter(, rst.Fields(i).Type, adParamInput, , rst.Fields(i).Value)
    Next
    
    objCon.BeginTrans ' 开启事务
    batchCount = 0
    While Not rst.EOF
        ' 给参数赋值
        For i = 0 To fldCount - 1
            objCmd.Parameters(i).Value = rst.Fields(i).Value
        Next
        objCmd.Execute , , adExecuteNoRecords
        batchCount = batchCount + 1
        ' 每maxItems条提交一次，避免事务过大
        If batchCount >= maxItems Then
            objCon.CommitTrans
            objCon.BeginTrans
            batchCount = 0
        End If
        rst.MoveNext
    Wend
    ' 提交剩余未提交的记录
    If batchCount > 0 Then objCon.CommitTrans
    
    ' 清理资源
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    objCmd.Parameters.Delete all
    Set objCmd = Nothing
    objCon.Close
    Set objCon = Nothing
    
Exit_subFastUpload:
    Exit Sub
Err_subFastUpload:
    ' 出错回滚事务
    If objCon.State = adStateOpen Then objCon.RollbackTrans
    MsgBox "错误：" & Err.Description, vbCritical
    Resume Exit_subFastUpload
End Sub

注意事项 #

• 两种方案都要求Access的RET_PARTS和Oracle的RET2_PARTS_ORACLE的字段顺序、数据类型完全匹配
• 如果存在自增主键、时间戳这类Oracle侧自动生成的字段，需要在SELECT语句里排除对应字段，不要纳入插入范围
• 数据量超过10万条的话优先选方案1，性能优势更明显

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