vb 访问远程mysql_VB远程访问MYSQL代码图解

首先需要安装MySQL Connector/ODBC

地址:http://dev.mysql.com/downloads/connector/odbc/

根据自己的系统(运行环境: Win7/XP/Win9x/2000/2003),选择相应的版本,如果页面打不开,可以直接试试下面的下载链接:

http://dev.mysql.com/get/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.4-win32.msi

http://dev.mysql.com/get/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.4-winx64.msi

如果安装的时候,弹出下面的警告对话框

说明缺少VC++组件,点击Abort终止,然后去微软官网下载一个安装后再重复刚才的

Microsoft Visual C++ 2010 Redistributable Package

64-bit version:http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

32-bit version:http://www.microsoft.com/en-gb/download/details.aspx?id=5555

安装过程全英文,一路下一步

关于VB链接MYSQL的资料百度一大堆,大多数和下面这种差不多相似,而且是很久以前的代码,使用的时候会报错(-2147467259)

主要的问题在于"DRIVER={MySQL ODBC 5.1 Driver}"这句错了,首先来看看刚才我们安装后的MySQL Connector/ODBC

开始--控制面板--管理工具--数据源(ODBC)

在  用户DSN 选项卡中的表格如果没有发现MySQL字样的,那么就点击右侧的  添加查看,如下图

发现了两个MySQL的驱动(然后关闭上面的窗口,不用做添加操作等,只是看看)

MySQL ODBC 5.3 ANSI Driver   和  MySQL ODBC 5.3 Unicode Driver

将上面的名称随便选一个替换到刚才的链接字符试试

我这里测试两个都可以连接上使用,即:

cn.ConnectionString = "DRIVER={MySQL ODBC 5.3 Unicode Driver};Persist Security Info=True;SERVER=... ..."

cn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};Persist Security Info=True;SERVER=... ..."

下面是工程示例,新建工程,添加textbox和command

Option Explicit

'引用 Microsoft ActiveX Data Objects 2.x Library

Private Sub Command1_Click()

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Set cn = ConnMySQL("root", "123456", "test", "192.168.1.101")

If cn Is Nothing Then

If Err.Number <> 0 Then

MsgBox Err.Number & " " & Err.Description, vbOKOnly & "连接MySQL发生错误"

Else

MsgBox "连接失败", vbOKOnly & "提示"

End If

Exit Sub

End If

rs.CursorLocation = adUseClient

rs.Open "SELECT * FROM ssc_members", cn, adOpenDynamic, adLockOptimistic, adCmdText

Me.Caption = rs.RecordCount

If rs.RecordCount > 0 Then

rs.MoveFirst

Do Until rs.EOF

Text1.Text = Text1.Text & rs("username").Value & vbCrLf

rs.MoveNext

DoEvents

Loop

End If

cn.Close

End Sub

'-----------------------------------------------------------------------------------------------------

Public Function ConnMySQL(uid As String, pwd As String, db_name As String, Optional host As String = "127.0.0.1", Optional port As Integer = 3306) As ADODB.Connection

Dim cn As New ADODB.Connection

On Error GoTo errline

cn.Mode = adModeReadWrite

cn.ConnectionString = "DRIVER={MySQL ODBC 5.3 Unicode Driver};" _

& "Persist Security Info=True;" _

& "SERVER=" & host _

& ";PORT=" & port _

& ";DATABASE=" & db_name _

& ";UID=" & uid _

& ";PWD=" & pwd _

& ";OPTION=3"

cn.Open

If cn.State = adStateOpen Then

Set ConnMySQL = cn

End If

Exit Function

errline:

End Function

关于远程访问时MYSQL的设置,详见另一篇图解 http://www.cnblogs.com/xiii/p/4890885.html