导入外部 access 更新及追加表的方法

bima

Private Sub in_Click()

    Dim fi As String

    Dim rs As New ADODB.Recordset

    Dim cnn As New ADODB.Connection

    Dim rst As New ADODB.Recordset

    Dim strSQL As String

    Dim strCnn As String

    Dim fileName As String

    Dim result As Integer

    With Application.FileDialog(msoFileDialogFilePicker)  ' 选择库

        .Title = "选择源数据库"

        .Filters.Add "mdb文件", "*.mdb"

        .FilterIndex = 1

        .AllowMultiSelect = False

        .InitialFileName = CurrentProject.Path

        result = .Show

        If (result <> 0) Then

            fileName = Trim(.SelectedItems.Item(1))

   

'/////////////////////////////////////////////////////////////////////////////////////

strCnn = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "'"

strSQL = "select * from exc_data"

        

cnn.Open strCnn

Set rs.ActiveConnection = cnn

rs.Open strSQL, , adOpenDynamic, adLockBatchOptimistic, adCmdText

   

Do While Not rs.EOF

fi = rs.Fields("goods_id")

If IsNull(DLookup("[w_date]", "[main]", "[bill_id]='" & fi & "'")) = False Then     '如果目标表有相同记录

DoCmd.RunSQL ("Update main set hk_weight=" & rs.Fields("weigth") & " where bill_id='" & rs.Fields("goods_id") & "'")

Else

MsgBox "如果无同记录则用追加查询"

End If

rs.MoveNext

Loop

rs.Close

'////////////////////////////////////////////////////////////////////////////////////

        End If

    End With

End Sub





搞不懂，只好用这种笨办法， 关键是每 update 一条记录，系统都会跳出一个对话框要你确认

有没有办法执行 update 语句时系统不要跳出确认框，

要是哪位有更好的方法那就最好了， 共享一下吧，谢谢！[em01]