vba操作mysql_增删改和事务

1. sql语句最后可以有0个，1个或多个分号。
2. 执行完不需要commit
3. conn.Execute sql1可以写成Set exeResult = conn.Execute(sql1)，debug发现exeResult是Fields类型，里面似乎没有保存条数信息，似乎没用

insert

insert多条数据：insert into s1 values(xx),(xx)，要删除最后的逗号否则报错。

Sub TestConnectTodb()
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySql ODBC 5.3 Unicode Driver};Server=localhost;Database=jd;uid=root;pwd=;Option=3;"
    
    'On Error GoTo closeC
    conn.Open
   
    'sql1 = "insert into s1(id,a,b,c,s) values(5,1,1,1,'s1'),"
    sql1 = "insert into s1(id,a,b,c,s) values"
    For i = 11 To 13
        sql1 = sql1 & "(" & i & ",1,1,1,'s1'),"
    Next i
    sql1 = Left(sql1, Len(sql1) - 1)
    'sql1 = "select pk,ppk,order from q"
    'On Error Resume Next
    Set exeResult = conn.Execute(sql1)
    
closeC:
    conn.Close
    Set conn = Nothing
End Sub

update

Sub TestConnectTodb()
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySql ODBC 5.3 Unicode Driver};Server=localhost;Database=jd;uid=root;pwd=;Option=3;"
    
    'On Error GoTo closeC
    conn.Open
   
    sql1 = "update s1 set a=a+1 where id>11"
    conn.Execute sql1
    'Set exeResult = conn.Execute(sql1)
      
closeC:
    conn.Close
    Set conn = Nothing
End Sub

delete

Sub TestConnectTodb()
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySql ODBC 5.3 Unicode Driver};Server=localhost;Database=jd;uid=root;pwd=;Option=3;"
    
    'On Error GoTo closeC
    conn.Open
   
    sql1 = "delete from s1 where id>11"
    conn.Execute sql1
    
closeC:
    conn.Close
    Set conn = Nothing
End Sub

事务：BeginTrans和CommitTrans

测试发现conn.Execute "update"报错退出后db数据不变，事务有效。

Sub TestConnectTodb()
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySql ODBC 5.3 Unicode Driver};Server=localhost;Database=jd;uid=root;pwd=;Option=3;"
    
    'On Error GoTo closeC
    conn.Open
    
    sql1 = "update s1 set a=a+1 where id=1"
    conn.BeginTrans
    conn.Execute sql1
    conn.Execute "update"
    conn.Execute sql1
    
    conn.CommitTrans
closeC:
    conn.Close
    Set conn = Nothing
End Sub