VB远程访问MYSQL代码图解

求知881 2021-05-05

展开全文

首先需要安装MySQL Connector/ODBC

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

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

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

http://dev./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