请教如何在vba里同时运行多个update语句更新access数据库对应表
发布网友
发布时间：2022-05-03 04:24
共2个回答
懂视网
时间：2022-05-03 08:45
网上查到综合后确定的update语法范例：
UPDATE mytable SET myfield = CASE WHEN 1 THEN ‘value‘ WHEN 2 THEN ‘value‘ WHEN 3 THEN ‘value‘ END WHERE id IN (1,2,3)
下面是我写的一个通用的update库表内容过程：kku为库表，zd为字段，frow为更新值表头空行数，col为更新值所在列，ygs为员工数量。
注：更新字符和数字写法有所不同，因此用IsNumeric(Cells(1 + frow, col))进行了判断。
功能：可以把表在一列数据，写入表的一个字段。写入值数量可控的通用过程。
Sub writeb(kku As String, zd As String, frow As Integer, col As Integer, ygs As Integer)
psw = "123456"
ku = "kp123"
user = "user123"
ip = "127.0.0.1"
Dim Cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As String
a = "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=" & ip & ";Database=" & ku & ";Uid=" & user & ";Pwd=" & psw & ";Stmt=set names gb2312"
Set Cnn = CreateObject("ADODB.Connection")
Cnn.ConnectionString = a
Cnn.Open
Set rs = CreateObject("ADODB.recordset")
rs.CursorType = adOpenStatic ‘设置游标类型,否则无法获得行数
rs.CursorLocation = adUseClient ‘设置游标属性,否则无法获得行数
rr = "(1" ‘用rr存入id的范围，用qq存入条件和赋值，首先给rr和qq赋个初值（即id为1时的值）
If IsNumeric(Cells(1 + frow, col)) Then
qq = "when id=1 then " & Cells(1 + frow, col) ‘更新的是数字
Else
qq = "when id=1 then ‘" & Cells(1 + frow, col) & "‘" ‘更新的为字符串，必须单引号引住
End If
For i = 2 To ygs
rr = rr & "," & i
If IsNumeric(Cells(i + frow, col)) Then
qq = qq + " when id=" & i & " then " & Cells(i + frow, col)
Else
qq = qq + " when id=" & i & " then ‘" & Cells(i + frow, col) & "‘"
End If
Next
rr = rr & ")"
rs.Open "update " & kku & " set " & zd & " = case " & qq & " end where id in " & rr & ";", Cnn, 3, 1
Cnn.Close
Set Cnn = Nothing
End Sub
vba操作Mysql使用UPDATE一次更新多组数据
标签：ase str gb2312 unicode type data for user open
热心网友
时间：2022-05-03 05:53
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
