Question

请教如何在vba里同时运行多个update语句更新access数据库对应表

Answer 1

Private Sub CommandButton1_Click()
Dim mydate As String, mytable As String, sql As String, i As Integer
Dim cnn As ADODB.Connection
mydata = ThisWorkbook.Path & "\商品信息表.mdb"
mytable = "inventory"
Set cnn = New ADODB.Connection
With cnn
.Provider = "microsoft.jet.oledb.4.0;Persist Security Info=False;Jet OLEDB:Database Password=123"
.Open mydata
End With
For i = 1 To 4
sql = "update inventory set 商品编码='" & Chr(i + 64) & "'+MID(商品编码,3) where 商品编码 like '0" & i & "%'"
cnn.Execute sql
Next
MsgBox "存货编码批量替换成功!", vbInformation
cnn.Close
Set cnn = Nothing
End Sub