mysql - 对不同单元的多个 MySQL 查询

Question

我尝试在一个 Sub 中执行不同的 MySQL 查询，这些查询应该填充一张表中的不同范围。

这是我到目前为止所得到的:

使用函数“ConnectionDB”我想连接到远程数据库。在子“QueryDatabase”中，我打开连接并执行两个 Sql 查询。在同一工作表中，第一个 Sql 查询应填充范围 A2，第二个查询应填充范围 D2。

我将 Sql 字符串从录制的宏中提取到远程数据库，并且在录制的宏中运行良好。

当我执行代码(见下文)时，Excel 正在工作一段时间，但寻址的单元格保持为空。我没有收到调试错误。

所以，伙计们，我对我能得到的每一个帮助都感到很高兴 =) 这是否可能是现在想要的方式？

Public conMySQL As ADODB.Connection
Public rs As ADODB.Recordset
Public strSql As String

Public Function ConnectionDB()

Set conMySQL = New ADODB.Connection
Set rs = New ADODB.Recordset

conMySQL.Open 
"Driver={MySQL ODBC 5.3 UNICODE Driver}; _
    Server={XXX}; UID=XXX; PWD=XXX; DATABASE=XXX; _
    Option=3; PORT=3306"
End Function


Sub QueryDatabase()

  Call ConnectionDB 

    With Sheets("Energiedaten").Range("A2") 

        strSql = "SELECT Messdaten_0.Terminal, Messdaten_0.Timestamp, Messdaten_0.Value" & Chr(13) & "" & Chr(10) & "FROM BLB_Data.Messdaten Messdaten_0" & Chr(13) & "" & Chr(10) & _
        "WHERE (Messdaten_0.Terminal = 'TerminalX') AND (Messdaten_0.Timestamp>=('" & UserForm1.TextBox1.Text & "') And (Messdaten_0.Timestamp<=('" & UserForm1.TextBox2.Text & "'))" _
        & Chr(13) & "" & Chr(10) & "ORDER BY Messdaten_0.Timestamp" 

    End With

    With Sheets("Energiedaten").Range("D2")

        strSql = "SELECT Messdaten_0.Terminal, Messdaten_0.Timestamp, Messdaten_0.Value" & Chr(13) & "" & Chr(10) & "FROM BLB_Data.Messdaten Messdaten_0" & Chr(13) & "" & Chr(10) & _
        "WHERE (Messdaten_0.Terminal = 'TerminalY') AND (Messdaten_0.Timestamp>=('" & UserForm1.TextBox1.Text & "') And Messdaten_0.Timestamp<=('" & UserForm1.TextBox2.Text & "'))" _
        & Chr(13) & "" & Chr(10) & "ORDER BY Messdaten_0.Timestamp " 

    End With

conMySQL.execute strSql

conMySQL.Close

End Sub

Answer 1

您可以使用Range.CopyFromRecordset方法。您已经声明了rs As ADODB.Recordset，因此您需要打开记录集，将其写入工作表，然后针对每个 SQL 语句关闭它。

编辑:用完整代码替换示例代码部分:

Option Explicit

Public conMySQL As ADODB.Connection

Public Function ConnectionDB()

    Set conMySQL = New ADODB.Connection

    conMySQL.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};" _
        & "Server={XXX}; UID=XXX; PWD=XXX; DATABASE=XXX;" _
        & "Option=3; PORT=3306"

End Function

Sub QueryDatabase()

Dim rs As ADODB.Recordset
Dim strSql As String

    Set rs = New ADODB.Recordset

    Call ConnectionDB

    strSql = "SELECT `Terminal`, `Timestamp`, `Value` FROM Messdaten WHERE `Terminal` = 'Extruder' AND `Timestamp` >= '" & UserForm1.TextBox1.Text & "' AND `Timestamp` <= '" & UserForm1.TextBox1.Text & "' ORDER BY `Timestamp` ASC"

    rs.Open Source:=strSql, ActiveConnection:=conMySQL, CursorType:=adOpenStatic, _
        LockType:=adLockOptimistic
    ThisWorkbook.Sheets("Energiedaten").Range("A2").CopyFromRecordset rs
    rs.Close

    strSql = "SELECT `Terminal`, `Timestamp`, `Value` FROM Messdaten WHERE `Terminal` = 'Beschichtungseinheit' AND `Timestamp` >= '" & UserForm1.TextBox1.Text & "' AND `Timestamp` <= '" & UserForm1.TextBox1.Text & "' ORDER BY `Timestamp` ASC "

    rs.Open Source:=strSql, ActiveConnection:=conMySQL, CursorType:=adOpenStatic, _
        LockType:=adLockOptimistic
    ThisWorkbook.Sheets("Energiedaten").Range("D2").CopyFromRecordset rs
    rs.Close

    conMySQL.Close

End Sub