excel - 刷新 QueryTable 抛出 "General ODBC error"- VBA Excel 2011 for Mac

Question

编辑:发现新错误？

我可能刚刚发现为什么我的脚本不再工作了。最近有一个 Office 更新，似乎他们已经实现了 microsoft 查询，它摆脱了我根据需要安装和配置的 ODBC 管理器。现在实际的问题可能是这个微软查询没有 UNICODE 库，并且我不知道可以将它们放在哪里以便微软查询可以读取/获取它们。或者更确切地说，我不知道如何为微软查询提供 UNICODE 库的路径，因为在管理器中无法更改它，或者也许有，但我只是没有找到它？除此之外，我无法打开 Microsoft 查询管理器，除非直接从 Excel 工作簿中打开它。

原始问题

这段代码工作了一段时间。现在使用完全相同的代码我收到运行时错误。 Here我已经在这种情况下寻求帮助并让它发挥作用。

代码如下:

Sub ConnectSQL()

    Dim connstring As String
    Dim sLogin As String
    Dim qt As QueryTable

    sLogin = "Uid=*;Pwd=*;"
    sqlstringFirma = "select * from gi_kunden.tbl_Firma"
    sqlstringPerson = "select * from gi_kunden.tbl_Person"
    connstring = "ODBC;DSN=KundeDB;" & sLogin

    ActiveWorkbook.Sheets("Firma").Select
    ActiveSheet.Range("A1:T2000").Clear

    For Each qt In ActiveSheet.QueryTables
        qt.Delete
    Next qt

    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstringFirma)
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .Refresh '"General ODBC error" hereeee
    End With

    ActiveWorkbook.Sheets("Person").Select
    ActiveSheet.Range("A1:T2000").Clear

    For Each qt In ActiveSheet.QueryTables
        qt.Delete
    Next qt

    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstringPerson)
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .Refresh 'And hereee again
    End With


    Call Replace
    ActiveWorkbook.Sheets("Firma").Select

End Sub

我确实在这里使用了 .Select 两次，我知道我应该更改它。

我在 Mac 上使用 Excel 2011，因此请注意，并非 Windows 上适用的所有内容都适用。

* 编辑*

首先:即使我激活了所有对象库，这仍然不起作用。

其次，当我打开对象浏览器时，我刚刚意识到一些事情:

1. “ODBCConnection”是声明的类(“Excel 的类 ODBCConnection 成员”)。但是当我在对象浏览器中单击 Excel 时，找不到“ODBCConnection”。如果我输入代码“Excel.ODBCConnection.Refresh”，它会抛出错误“找不到方法或数据成员”，仅突出显示“ODBCConnection”一词。当我仅使用 ODBCConnection.Refresh(根据对象浏览器，刷新是 ODBCConnection 的一种方法)时，会显示相同的错误。

2. 我对“QueryTable(1).Refresh”有完全相同的问题，它抛出错误:“变量未定义”，即使它在对象浏览器中列出并声明(“Class QueryTable Member of Excel”)。

我相信与此相关的所有事情都会出现此类错误。

编辑

这会在 ActiveWorkbook.RefreshAll 行上引发相同的“常规 ODBC 错误”:

Dim strConn As String
Dim strLoginData As String
Dim QT As QueryTable

Dim strFirmSQL As String
Dim strPersSQL As String

strConn = "ODBC;DSN=KundeDB;" & strLoginData
strLoginData = "Uid=*myUid*;Pwd=*myPwd*"

strFirmSQL = "Select * From gi_kunden.tbl_firma"
strPersSQL = "Select * From gi_kunden.tbl_person"

Dim WSFirm As Worksheet
Set WSFirm = ActiveWorkbook.Sheets("Firma")

WSFirm.Range("A1:T1000").Clear

For Each QT In WSFirm.QueryTables
    QT.Delete
Next QT

With WSFirm.QueryTables.Add(strConn, WSFirm.Range("A1"), strFirmSQL)
    .SaveData = True
    .BackgroundQuery = True
    '.Refresh
End With


Dim WSPers As Worksheet
Set WSPers = ActiveWorkbook.Sheets("Person")

For Each QT In WSPers.QueryTables
    QT.Delete
Next QT

With WSPers.QueryTables.Add(strConn, WSPers.Range("A1"), strPersSQL)
    .SaveData = True
    .BackgroundQuery = True
    '.Refresh
End With

ActiveWorkbook.RefreshAll 'FAILS here

Call Replace

WSFirm.Activate

Answer 1

我无法解决你的问题，但我可以帮你调试。

第一个问题是:该错误消息是什么意思？它是否告诉您对数据运行的查询或命令有错误，或者有什么原因阻止您连接到数据库？

实际上可以编写信息丰富的错误消息，并且雷德蒙德实现查询表的任何被误导的天才都选择不传递数据库服务器和连接库发出的详细错误信息。

幸运的是，我们自己做了一些工作。

QueryTable 对象有一个连接属性 - 它是一个字符串，不是一个功能齐全的连接对象，但您可以更详细地检查它并针对 ADODB 连接对象进行测试。尝试使用此函数来测试连接字符串，看看是否有任何有用的信息:

Public Sub ConnectionTest(ConnectionString As String)<br/>
' Late-binding: requires less effort, but he correct aproach is
' to create a reference to 'Microsoft ActiveX Data Objects' -<br/>
'Dim conADO As ADODB.Connection
'Set conADO = New ADODB.Connection<br/>Dim conADO As Object
Set conADO = CreateObject("ADODB.Connection")<br/>
Dim i As Integer<br/>
conADO.ConnectionTimeout = 30
conADO.ConnectionString = ConnectionString<br/>
On Error Resume Next<br/>
conADO.Open<br/>
If conADO.State = 1 Then
    Debug.Print "Connection string is valid"
Else
    Debug.Print "Connection failed:"<br/><br/>
    For i = 0 To conADO.Errors.Count
        With conADO.Errors(i)
            Debug.Print "ADODB connection returned error " & .Number & " (native error '" & .NativeError & "') from '" & .Source & "': " & .Description
        End With
    Next i<br/><br/>
End If<br/>
Debug.Print "Connection String: "
Debug.Print vbTab & Replace(.Connection, ";", ";" & vbCrLf & vbTab)
Debug.Print<br/><br/>
Set conADO = Nothing<br/><br/>
End Sub

...并将其插入到您的代码中:

Dim objQueryTable As Excel.QueryTable
Dim strConnect as String<br/>
set objQueryTable = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstringFirma)<br/> 
    With objQueryTable 
        strConnect = .Connection
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .Refresh '  "General ODBC error" hereeee
    End With<br/> 
ConnectionTest strConnect  ' view the output in the debug window/immediate pane

如果您看到其中的错误，则可能只是我的 ADODB 连接实现在您的 Mac Office 环境中不起作用:但您完全有可能有以下任一情况:

1. 创建了一个连接，观察其工作情况，并消除了您的连接字符串或 DSN 的来源可能是问题...
2. ...或者在连接参数中发现错误，您可以修复。

如果连接正常，则可能是您在数据库中运行的查询或命令才是问题的根源 - 而且我在您的问题中看到的错误消息确实指向了这个方向 - 所以我们需要深入研究再深一点。

不幸的是，我无法知道我使用的工具是否适合您:这是开发人员的调试代码，您需要修改它才能使其工作。

之所以如此繁琐，是因为实现 QueryTable 的 Office 团队做出了一些有趣的决定:QueryTable 公开的“连接”和“记录集”属性不是功能齐全的对象 - 我认为它们是接口(interface)允许 QueryTable 使用来自各种不同提供者的称为“连接”和“记录集”的对象，并公开一组通用的属性和方法。对于跨平台可用性来说，这是一个很好的决定，但这意味着需要询问这些对象的开发人员不能依赖运行时存在的任何给定方法 - 因此这仅适用于“调试”模式下的反编译代码.

您还需要在 IDE 中注册 Microsoft DAO 和 ADO 引用:当您需要能够在“本地”窗口中查看这些对象时，使用“CreateObject”进行后期绑定(bind)是错误的工具:

Public Sub ConnectionDetails(objQueryTable As Excel.QueryTable)<br/>
Dim rstADO As ADODB.Recordset
Dim conADO As ADODB.Connection<br/>
Dim rstDAO As DAO.Recordset
Dim conDAO As DAO.Connection<br/>
Dim i As Integer<br/><br/>
Set objQueryTable = Sheet1.ListObjects(1).QueryTable<br/>
With objQueryTable<br/><br/>
    Debug.Print "Connection String: "
    Debug.Print vbTab & Replace(.Connection, ";", ";" & vbCrLf & vbTab)
    Debug.Print<br/><br/>
    Debug.Print "Query Type: " & .QueryType  ' Documented here: <a href="https://msdn.microsoft.com/en-us/library/office/ff835313.aspx" rel="noreferrer noopener nofollow">https://msdn.microsoft.com/en-us/library/office/ff835313.aspx</a>
    Debug.Print "Query: " & .CommandText
    Debug.Print "Database request type: " & .CommandType   ' XlCmdType documented here: <a href="https://msdn.microsoft.com/en-us/library/office/ff197456.aspx" rel="noreferrer noopener nofollow">https://msdn.microsoft.com/en-us/library/office/ff197456.aspx</a><br/><br/>
    .MaintainConnection = True<br/>
    On Error Resume Next
    If TypeOf .Recordset Is DAO.Recordset Then<br/><br/>
        On Error Resume Next
        Set rstDAO = .Recordset<br/><br/>
        rstDAO.OpenRecordset
        For i = 0 To DAO.Errors.Count
            With DAO.Errors(i)
                Debug.Print "DAO Recordset '" & Left(rstDAO.Name, 24) & "' returned error " & .Number & " from '" & .Source & "': " & .Description
            End With
        Next i<br/><br/>
        Set conADO = DAO.DBEngine.OpenConnection(.Connection)
        For i = 0 To DAO.Errors.Count
            With DAO.Errors(i)
                Debug.Print "DAO Connection '" & Left(conDAO.Name, 24) & "' returned error " & .Number & " from '" & .Source & "': " & .Description
            End With
        Next i<br/><br/>
    ElseIf TypeOf .Recordset Is ADODB.Recordset Then<br/><br/>
        On Error Resume Next
        Set rstADO = .Recordset<br/><br/>
        If rstADO.State <> 0 Then rstADO.Close
        rstADO.Open
        Set conADO = rstADO.ActiveConnection
        For i = 0 To conADO.Errors.Count
            With conADO.Errors(i)
                Debug.Print "ADODB Recordset '" & Left(rstADO.Source, 24) & "' connection returned error " & .Number & " (native error '" & .NativeError & "') from '" & .Source & "': " & .Description
            End With
        Next i<br/>
    ElseIf Err.Number <> 0 Then<br/><br/>
        Debug.Print Err.Source & " Error " & Err.Number & ":" & Err.Description<br/><br/>
    Else<br/><br/>
        Debug.Print "recordset type is: '" & TypeName(.Recordset) & "': for further information, place a breakpoint in the code and use the 'Locals' window."<br/><br/>
    End If<br/>
End With<br/>
End Sub<br/>

代码执行的操作(或尝试执行的操作)非常简单:它询问数据库并检索详细的错误消息。

他们可能会告诉您存在语法错误，或者 SQL 中缺少参数 - 如果数据库是 MS-Access，这可能会产生误导:“缺少参数”可能意味着字段名称或函数名称未知。这也可能意味着 you can't run that SQL outside an MS-Access user session 。

如果无法工作，请返回 ConnectionTest 代码并对 conADO 连接对象运行命令文本:conADO.执行strCommandText...并再次询问错误集合。

这就是我可以用来解决这个问题的调试工具:希望另一个“Stacker”可以建议其他方法。