mysql addnew_ADO: rs.addNew, rs.delete和rs.update--MySql数据

在下面的ADO(ActiveX数据对象)示例中，创建了表my_ado，并演示了rs.addNew、rs.delete和rs.update的用法。

Private Sub myodbc_ado_Click()

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim fld As ADODB.Field

Dim sql As String

'connect to MySQL server using MySQL ODBC 3.51 Driver(使用MySQL ODBC 3.51驱动程序连接到MySQL服务器)

Set conn = New ADODB.Connection

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_

& "SERVER=localhost;"_

& " DATABASE=test;"_

& "UID=venu;PWD=venu; OPTION=3"

conn.Open

'create table(创建表)

conn.Execute "DROP TABLE IF EXISTS my_ado"

conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _

& "txt text, dt date, tm time, ts timestamp)"

'direct insert(直接插入)

conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')"

conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')"

conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')"

Set rs = New ADODB.Recordset

rs.CursorLocation = adUseServer

'fetch the initial table ..(获取初始表…)

rs.Open "SELECT * FROM my_ado", conn

Debug.Print rs.RecordCount

rs.MoveFirst

Debug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")

For Each fld In rs.Fields

Debug.Print fld.Name,

Next

Debug.Print

Do Until rs.EOF

For Each fld In rs.Fields

Debug.Print fld.Value,

Next

rs.MoveNext

Debug.Print

Loop

rs.Close

'rs insert(rs插入)

rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic

rs.AddNew

rs!Name = "Monty"

rs!txt = "Insert row"

rs.Update

rs.Close

'rs update(rs更新)

rs.Open "SELECT * FROM my_ado"

rs!Name = "update"

rs!txt = "updated-row"

rs.Update

rs.Close

'rs update second time..(rs更新第2次…)

rs.Open "SELECT * FROM my_ado"

rs!Name = "update"

rs!txt = "updated-second-time"

rs.Update

rs.Close

'rs delete(rs删除)

rs.Open "SELECT * FROM my_ado"

rs.MoveNext

rs.MoveNext

rs.Delete

rs.Close

'fetch the updated table ..(获取更新的表…)

rs.Open "SELECT * FROM my_ado", conn

Debug.Print rs.RecordCount

rs.MoveFirst

Debug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")

For Each fld In rs.Fields

Debug.Print fld.Name,

Next

Debug.Print

Do Until rs.EOF

For Each fld In rs.Fields

Debug.Print fld.Value,

Next

rs.MoveNext

Debug.Print

Loop

rs.Close

conn.Close

End Sub