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

kebds

想按条件批量替换access数据库里有一张表inventory
把01打头的编码替换成A打头,02打头的编码替换成B打头，03打头替换成C打头，04打头的替换成D打头。效果如上面右图
我现在的语句如下，一次只能改一个打头的，如何能一次执行多个SQL语句，或在EXCEL里做个对照表来执行语句，谢谢大家指点。
Dim mydate As String, mytable As String, sql As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
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
sql = "update inventory set 商品编码='A'+MID(商品编码,3,6) where 商品编码 like '01%'"
Set rs = cnn.Execute(sql)
MsgBox "存货编码批量替换成功!", vbInformation
cnn.Close
Set rs = Nothing
Set cnn = Nothing

zhaogang1960

加一个循环：

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

复制代码

zhaogang1960

请看附件
替换商品编码.rar (16.64 KB, 下载次数: 129)

2012-9-26 00:57 上传

点击文件名下载附件

LMY123

update inventory set 商品编码='" & Chr(i + 64) & "'+MID(商品编码,3) where 商品编码 like '0" & i & "%'