Option Explicit'工程->引用->Microsoft ActiveX Data Object 2.0 Library (后面为版本号) Dim cn As New ADODB.ConnectionPrivate Sub Command1_Click() Dim rs As New ADODB.Recordset
rs.Open "select * from astuff", cn, adOpenDynamic, adLockOptimistic rs.Close Set rs = Nothing End SubPrivate Sub Form_Load() On Error GoTo Errhandle cn.ConnectionString = "Driver={MySQL};Option=0;port=;stmt=;Server=orient-xxl;DataBase=orientnbcws;User=root;Password=" cn.Open
Exit Sub Errhandle: MsgBox Err.Description, vbExclamation End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) cn.Close Set cn = Nothing End Sub
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
'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
dim cn as adodb.connectionset cn=new adodb.connection cn.open"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=********;"
Dim cn As New ADODB.ConnectionPrivate Sub Command1_Click()
Dim rs As New ADODB.Recordset
rs.Open "select * from astuff", cn, adOpenDynamic, adLockOptimistic
rs.Close
Set rs = Nothing
End SubPrivate Sub Form_Load()
On Error GoTo Errhandle
cn.ConnectionString = "Driver={MySQL};Option=0;port=;stmt=;Server=orient-xxl;DataBase=orientnbcws;User=root;Password="
cn.Open
Exit Sub
Errhandle:
MsgBox Err.Description, vbExclamation
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
cn.Close
Set cn = Nothing
End Sub
更多的连接字符串上这里找!!
来这里看看!
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
cn.open"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=********;"