Excel插件连接到MySQL服务器

gpfsuwkq 于 2024-01-05 发布在 Mysql

关注(0)|答案(1)|浏览(175)

我在db4free.net上建立了一个测试数据库，并上传了一份northwind训练数据库的副本，看看我是否可以将一些信息拉到excel工作簿中，并继续得到通用的unspecified/automation错误。
我在参考资料中包括了“Microsoft ActiveX Data Objects 2.8 library”，甚至尝试了6.1。
在任何人对我发脾气之前，包括用户名和密码;这个测试数据库中唯一存在的是一个训练数据集。我在那里存储了零个个人信息。
下面是我的代码：

Sub sqlTest()
    'Declare some strings to hold the connection string and the SQL statement
    Dim cnStr As String
    Dim sqlStr As String
    
    'Define a connection and a recordset to hold extracted information
    Dim oConn As ADODB.Connection
    Dim rcSet As New ADODB.Recordset
    Set oConn = New ADODB.Connection
    Set rcSet = CreateObject("ADODB.Recordset")
    
    'connection string to connect to db4free.net
    cnStr = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=85.10.205.173;DATABASE=resumedemo;PORT=3306;UID=jwaycaster;PWD=resumedemo123;"
    
    'Test SQL query
    sqlStr = "SELECT * FROM `Employees`"
    
    'This is where it crashes
    oConn.Open cnStr
    
    oConn.CommandTimeout = 900
    
    rcSet.Open sqlStr, oConn
    
    Sheets(1).Range("A1").CopyFromRecordset rcSet
    
    rcSet.Close
    
    oConn.Close
End Sub

字符串
我已经搜索了几个相关的主题，似乎找不到答案。希望我错过了一些简单的东西。
为后代编辑：在阅读回复后，我意识到这台计算机上没有安装驱动程序（我正在拜访亲戚，不敢相信我忘记检查了）。在安装驱动程序并更新我的引用后，我的代码执行得很好，但我建议使用CDP 1802。

mysql

来源：https://stackoverflow.com/questions/70212373/excel-vba-connect-to-mysql-server

1条答案

按热度按时间

6ioyuze21#

有时候自己处理错误会很有用。

Microsoft ActiveX数据对象6.1库
Microsoft ActiveX数据对象RecordSet 6.0库

Option Explicit
Sub sqlTest()
    ' credentials
    Const SERVER = "85.10.205.173"
    Const DB = "resumedemo"
    Const UID = "jwaycaster"
    Const PWD = "resumedemo123"
      
    'Define a connection and a recordset to hold extracted information
    Dim oConn As ADODB.Connection, rcSet As ADODB.Recordset
    Dim cnStr As String, n As Long, msg As String, e
    
    'connection string to connect to db4free.net
    cnStr = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=" & SERVER & _
            ";PORT=3306;DATABASE=" & DB & _
            ";UID=" & UID & ";PWD=" & PWD & ";"
    
    'Test SQL query
    Const SQL = "SELECT * FROM `Employees`"
    
    ' connect
    Set oConn = New ADODB.Connection
    'oConn.CommandTimeout = 900
    
    On Error Resume Next
    oConn.Open cnStr
    If oConn.Errors.Count > 0 Then
        For Each e In oConn.Errors
            msg = msg & vbLf & e.Description
        Next
        MsgBox msg, vbExclamation, "ERROR - Connection Failed"
        Exit Sub
    Else
        MsgBox "Connected to database " & oConn.DefaultDatabase, vbInformation, "Success"
    End If
    
    ' run query
    Set rcSet = oConn.Execute(SQL, n)
    If oConn.Errors.Count > 0 Then
        msg = ""
        For Each e In oConn.Errors
            msg = msg & vbLf & e.Description
        Next
        MsgBox msg, vbExclamation, "ERROR - Execute Failed"
    Else
        Sheets(1).Range("A1").CopyFromRecordset rcSet
        MsgBox SQL & " returned " & n & " records", vbInformation
    End If
    On Error GoTo 0
    
    oConn.Close
    
End Sub

字符串

展开查看全部

赞(0）回复(0）举报 2024-01-05

我来回答

Excel插件连接到MySQL服务器

1条答案

相关问题

热门标签

最新问答