VB6访问MySQL

环境：WinXP Pro + VB6 + Mysql-essential-5.1.55

 

1、安装mysql-connector-odbc-5.1.8-win32.msi

 

2、在MySQL里面建立数据库和表（用SQLyog）

CREATE DATABASE /*!32312 IF NOT EXISTS*/`AWgao` /*!40100 DEFAULT CHARACTER SET utf8 */;

 

USE `AWgao`;

 

DROP TABLE IF EXISTS `test1`;

 

CREATE TABLE `test1` (

  `ZD1` int(11) DEFAULT NULL,

  `ZD2` varchar(50) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

3、用ADO连接MySQL      conn定义成全局ADOConnection变量

    Dim rs As ADODB.Recordset

    Dim Sql As String

   

    Set conn = Nothing

   

    'connect　to　MySQL　server　using　MySQL　ODBC　3.51　Driver

    Set conn = New ADODB.Connection

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

    & "SERVER=" & Trim(txtServer.Text) & ";" _

    & "　DATABASE=" & Trim(txtDB.Text) & ";" _

& "UID=" & Trim(txtUser.Text) & ";PWD=" & Trim(txtPwd.Text) & ";　OPTION=3"

‘txtServer、txtDB、txtUser、txtPwd分别是四个文本框，用来输入MySQL服务器、数据库、用户名和密码

    conn.Open

 

4、获取表记录数

    Dim myrs As New Recordset

    Dim mySQL As String

    mySQL = "SELECT count(*) from test1"

   

    myrs.Open mySQL, conn

   

    MsgBox "表 test1 共有 " & myrs.Fields(0) & " 条记录"

   

    myrs.Close

 

5、插入一条数据

    Dim Sql As String

    Sql = "INSERT INTO test1 (ZD1,ZD2) Values( 135,'文本字段')" ‘替换成对应的Insert语句

 

    conn.Execute Sql