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
MySQL 是一个开放源代码的数据库管理系统 (DBMS)。它采用客户端/服务器结构,是一个多线程、多用户的数据库服务器。MySQL 是为高速应用设计的,因此,它并不提供关系数据库系统提供的许多功能,比如子查询、外键、引用完整性、存储过程、触发器和视图。此外,它有一个锁定机制,这对同时有不同用户进行许多写操作的数据表来说是不够的。它还缺少对软件应用程序和工具的支持。 SQL Server 2000 是一个完整的关系数据库管理系统 (RDBMS),它还包括用于 OLAP 和数据挖掘的集成分析功能。SQL Server 2000 满足最大的数据处理系统和商业 Web 站点对数据及分析的存储要求,同时可以为个人和小企业提供易用的数据存储服务。 Microsoft SQL Server 的结构支持高级的服务器功能,比如行一级的锁定、高级查询优化、数据复制、分布式数据库管理以及分析服务。Transact-SQL (T-SQL) 是 SQL Server 2000 支持的 SQL 语言。
sqlserver则可以使用很多种联接方法
然后用ado进行操作
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