先在http://www.mysql.com下载最新的mysql ODBC驱动程序,然后 Dim conn As ADODB.Connection Dim rs As ADODB.Recordset 'connect to MySQL server using MySQL ODBC 3.51 Driver 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
直接添加一个Microsoft ADO Data Control 6.0(OLEDB) 部件, 然后对它的属性进行设置即可。
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 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.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic rs.AddNew rs!Name = "Monty" rs!txt = "Insert row" rs.Update rs.Close 'rs update rs.Open "SELECT * FROM my_ado" rs!Name = "update" rs!txt = "updated-row" rs.Update rs.Close 'rs update second time.. rs.Open "SELECT * FROM my_ado" rs!Name = "update" rs!txt = "updated-second-time" rs.Update rs.Close 'rs delete 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
我的连接方法: 1。工程-》引用:Microsoft ActiveX data Objects 2.7 Library 2. Dim db As New ADODB.Connection Dim rd As New ADODB.Recordset 3。db.open(连接字符串,可通过Adodb 控件的ConnectString属性取得) set rd=db.execute(数据库操作命令,如:"Select * from MyTable") rd!字段名,可取得需要的数据 当然,也可用 db.execute "数据库操作命令"操作数据库,但它不返回任何值 4.记得最后要 db.Close 关闭数据库。 另。可以用Adodc控件与 DataGrid控件组合使用,设置一下属性就可以直接在DataGrid中 显示并操作数据库中的数据。
Dim rs As ADODB.Recordset 'connect to MySQL server using MySQL ODBC 3.51 Driver
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
然后对它的属性进行设置即可。
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
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.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Name = "Monty"
rs!txt = "Insert row"
rs.Update
rs.Close 'rs update
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-row"
rs.Update
rs.Close 'rs update second time..
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-second-time"
rs.Update
rs.Close 'rs delete
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
1。工程-》引用:Microsoft ActiveX data Objects 2.7 Library
2. Dim db As New ADODB.Connection
Dim rd As New ADODB.Recordset
3。db.open(连接字符串,可通过Adodb 控件的ConnectString属性取得)
set rd=db.execute(数据库操作命令,如:"Select * from MyTable")
rd!字段名,可取得需要的数据
当然,也可用 db.execute "数据库操作命令"操作数据库,但它不返回任何值
4.记得最后要 db.Close 关闭数据库。
另。可以用Adodc控件与 DataGrid控件组合使用,设置一下属性就可以直接在DataGrid中
显示并操作数据库中的数据。