vba更新access数据库

coliniskiddie

跪求一个用vba来更新access数据库的值的代码……谢谢了…………

coliniskiddie

唉……到现在也没有人来回复啊……我自己来回复一下，把代码放上

coliniskiddie

Dim mydata As String, mytable As String, sql As String, a As String, b As String
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim memo As ADODB.Field
    Dim ygday As ADODB.Field
    Dim wtms As ADODB.Field
    Dim jjfa As ADODB.Field
    Dim sfcl As ADODB.Field
   
   
    '清除工作表的全部数据
   'ActiveSheet.Cells.Clear
    mydata = ThisWorkbook.Path & "\item.mdb"    '制定数据库
     mytable = "itemwt"     '制定数据表
    '建立与数据库的链接
    Set cnn = New ADODB.Connection
    a = TextBox7.Value
    b = Mid(ComboBox7.Text, 1, 4)
    With cnn
        .Provider = "microsoft.jet.oledb.4.0"
        .Open mydata
    End With
    '查询数据表
   
  sql = "select ygday,wtms,jjfa,sfcl,memo from " & mytable & " where id= " & """" & a & """" & " and cusid=" & """" & b & """"

    'MsgBox sql
   
   Set rs = New ADODB.Recordset
    rs.Open sql, cnn, adOpenKeyset, adLockOptimistic
   Set memo = rs.Fields("memo")
   Set ygday = rs.Fields("ygday")
   Set wtms = rs.Fields("wtms")
   Set jjfa = rs.Fields("jjfa")
   Set sfcl = rs.Fields("sfcl")
   memo = Me.TextBox6
   ygday = Me.TextBox2
   wtms = Me.TextBox8
   jjfa = Me.TextBox5
   sfcl = Me.ComboBox6
   rs.Update
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    MsgBox "修改成功！"
   
        Label15.Caption = ""
        Label16.Caption = ""
        Label17.Caption = ""
        TextBox2.Text = ""
        Label18.Caption = ""
        TextBox8.Text = ""
        TextBox5.Text = ""
        Label19.Caption = ""
        ComboBox6.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        ComboBox7.Text = ""