vba mysql update多字段_vba操作Mysql使用UPDATE一次更新多组数据

网上查到综合后确定的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

原文：https://www.cnblogs.com/tywusy/p/12129312.html