刷新QueryTable抛出“常规ODBC错误” - VBA Excel 2011 for Mac [英] Refresh QueryTable throwing "General ODBC error" - VBA Excel 2011 for Mac

问题描述

编辑：发现新错误

我可能刚刚发现为什么我的脚本不会工作了最近有办公室更新，似乎他们已经实现了微软查询，摆脱了我安装并配置为我的需要的ODBC管理器。现在实际的问题可能是这个微软查询没有UNICODE库，我不知道我可以把它们放在哪里，以便微软的查询可以读取/获取它们。或者我不知道如何给微软查询到UNICODE库的路径，就像在管理器中没有可能改变它，或者也许有没有，我只是没有找到它？除此之外，我无法打开微软查询管理器，除非我直接从excel工作簿打开它。

ORIGINAL QUESTION

有一段时间现在使用完全相同的代码，我得到一个运行时错误。 这里我已经在此请求帮助案例，并得到它的工作。

这是代码：

  Sub ConnectSQL（）
 
 Dim connstring As String 
 Dim sLogin As String 
 Dim qt As QueryTable 
 
 sLogon =Uid = *; Pwd = * ; 
 sqlstringFirma =select * from gi_kunden.tbl_Firma
 sqlstringPerson =select * from gi_kunden.tbl_Person
 connstring =ODBC; DSN = KundeDB &安培; sLogon 
 
 ActiveWorkbook.Sheets（Firma）。选择
 ActiveSheet.Range（A1：T2000）。清除
 
对于每个qt在ActiveSheet.QueryTables 
 qt.Delete 
下一个qt 
 
使用ActiveSheet.QueryTables.Add（Connection：= connstring，Destination：= Range（A1），Sql：= sqlstringFirma）
 .BackgroundQuery = False 
 .RefreshStyle = xlOverwriteCells 
 .Refresh'一般ODBC错误hereeee 
结束
 
 ActiveWorkbook.Sheets（Person）。选择
 ActiveSheet.Range（A1：T2000）。清除
 
对于每个qt在ActiveSheet.QueryTables 
 qt.Delete 
下一个qt 
 
 With ActiveSheet.QueryTables.Add（Connection：= connstring，Destination：= Range（A1），Sql：= sqlstringPerson）
 .BackgroundQuery = False 
 .RefreshStyle = xlOverwriteCells 
再次重新出现
结束
 
 
调用替换
 ActiveWorkbook.Sheets（Fir ma）。选择
 
 End Sub 
  

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

我正在Mac上在Excel 2011中工作，所以请注意，并非所有在Windows上工作的内容都可以正常工作。

*编辑*

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

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

1. ODBCConnection是一个声明的类（Class ODBCConnection Member of Excel）。但是当我点击对象浏览器中的Excel时，无法找到ODBCConnection。如果我输入代码Excel.ODBCConnection.Refresh，它将抛出错误方法或数据成员未找到，仅突出显示单词ODBCConnection。当我仅使用ODBCConnection.Refresh（刷新是根据对象浏览器的ODBCConnection的方法）时，会显示相同的错误。




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

我相信我会收到所有与此相关的事情的错误。

编辑

一般ODBC错误在线ActiveWorkbook.RefreshAll：

  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 = usr_gi_kunden; Pwd = au382k8？X。 
 
 strFirmSQL =选择*从gi_kunden.tbl_firma
 strPersSQL =选择*从gi_kunden.tbl_person
 
 Dim WSFirm作为工作表
设置WSFirm = ActiveWorkbook.Sheets（Firma）
 
 WSFirm.Range（A1：T1000）。清除
 
对于每个QT在WSFirm.QueryTables 
 QT。删除
下一个QT 
 
使用WSFirm.QueryTables.Add（strConn，WSFirm.Range（A1），strFirmSQL）
 .SaveData = True 
 .BackgroundQuery = True 
'.Refresh 
 End with 
 
 
 Dim WSPers As Worksheet 
设置WSPers = ActiveWorkbook.Sheets（Person）
 
对于每个QT在WSPers.QueryTables 
 QT.Delete 
下一个QT 
 
使用WSPers.QueryTables.Add（strConn，WSPers.Range（A1），strPersSQL ）
 .SaveData = True 
 .BackgroundQuery = True 
'.Refresh 
结束
 
 ActiveWorkbook.RefreshAll'FAILS here 
 
调用替换
 
 WSFirm.Activate 
  

解决方案

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

第一个问题是：该错误消息是什么意思？是否告知您在数据上运行的查询或命令有错误，还是阻止您连接到数据库？

实际上是可能的撰写有意义的错误消息，以及Redmond的任何错误的天才实施了查询表选择，不会传递数据库服务器和连接库发出的详细错误信息。

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

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

  
公共子连接测试（ConnectionString As String）
 
'后期绑定：需要较少的努力，但是他正确的aproach是
'来创建对Microsoft ActiveX数据对象的引用 -  
 
'Dim conADO As ADODB.Connection 
'Set conADO = New ADODB.Connection 
 Dim conADO As Object 
 Set conADO = CreateObject（ADODB.Connection）
 
 Dim i As Integer 
 
 conADO.ConnectionTimeout = 30 
 conADO.ConnectionString = ConnectionString 
 
 On Error Resume Next 
 
 conADO。打开
 
如果conADO.State = 1然后
 Debug.Print连接字符串有效
 Else 
 Debug.Print连接失败：
 
 
 For i = 0 to conADO.Errors.Count 
 with conADO.Errors（i）
 Debug.PrintADODB connection returned error&数字& （native error'& .NativeError&'）来自'&来源& '：& 。描述
结束
下一步我
 
 
结束如果
 
 Debug.Print连接字符串：
 Debug.Print vbTab&替换（.Connection，;，;& vbCrLf& vbTab）
 Debug.Print 
 
 
设置conADO = Nothing 
 
 
 End Sub 
  

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

  
 Dim objQueryTable As Excel.QueryTable 
 Dim strConnect as String 
 
 set objQueryTable = ActiveSheet.QueryTables.Add（Connection： = connstring，Destination：= Range（A1），Sql：= sqlstringFirma）
 
使用objQueryTable 
 strConnect = .Connection 
 .BackgroundQuery = False 
 .RefreshStyle = xlOverwriteCells 
 .Refresh'一般ODBC错误hereeee 
结束与
 
 ConnectionTest strConnect'查看调试窗口/直接窗格中的输出
  

如果您可以看到错误，可能只是我的ADODB连接的实现在您的Mac Office环境：但您完全有可能：

1. 创建一个连接，看到它的工作，并消除了
   的可能性，您的连接字符串或DSN是
   问题的来源...

如果连接正常工作，那么您可以在连接参数中发现错误。 ，这可能是您在数据库中运行的查询或命令，这是问题的根源 - 我在您的问题中看到的错误消息指向了这个方向 - 因此我们需要深入研究。

不幸的是，我无法知道我使用的工具是否适用于您：这是开发人员的调试代码，您需要修补它才能获得它的工作。

它的原因是这样的，执行QueryTable的Office团队做出了一些有趣的决定：公开的连接和记录集属性QueryTable不是全功能的对象 - 我认为他们是允许QueryTable使用来自各种不同提供程序的名为connection和recordset的对象的接口，并公开一组通用的属性和方法。这对于跨平台可用性是一个很好的决定，但这意味着需要询问这些对象的开发人员不能依赖运行时存在的任何给定的方法 - 所以这只是在调试模式下的反编译代码

您还需要在IDE中注册Microsoft DAO和ADO引用：当您需要时，与CreateObject的后期绑定是错误的工具能够在本地窗口中查看这些对象：

  
 Public Sub ConnectionDetails（objQueryTable作为Excel.QueryTable）
 
 Dim rstADO As ADODB.Recordset 
 Dim conADO As ADODB.Connection 
 
 Dim rstDAO As DAO.Recordset 
 Dim conDAO As DAO.Connection 
 
 Dim i As Integer 
 
 
设置objQueryTable = Sheet1.ListObjects（1）.QueryTable 
 
使用objQueryTable 
 
 
 Debug.PrintConnection String：
 Debug.Print vbTab&替换（.Connection，;，;& vbCrLf& vbTab）
 Debug.Print 
 
 
 Debug.Print查询类型：& .QueryType'记录在这里： https://msdn.microsoft.com/ en-us / library / office / ff835313.aspx  
 Debug.PrintQuery：& .CommandText 
 Debug.Print数据库请求类型：& .CommandType'XlCmdType记录在这里： https://msdn.microsoft.com /en-us/library/office/ff197456.aspx  
 
 
 .MaintainConnection = True 
 
 On Error Resume Next 
如果TypeOf。记录集是DAO.Recordset然后
 
 
错误恢复下一步
设置rstDAO = .Recordset 
 
 
 rstDAO.OpenRecordset 
对于我= 0到DAO.Errors.Count 
使用DAO.Errors（i）
 Debug.PrintDAO Recordset'&左（rstDAO.Name，24）& 返回错误&数字& from&来源& '：& $说明
结束
下一步我
 
 
设置conADO = DAO.DBEngine.OpenConnection（.Connection）
对于i = 0到DAO.Errors。 Count 
与DAO.Errors（i）
 Debug.PrintDAO Connection&左（conDAO.Name，24）& 返回错误&数字& from&来源& '：& 。描述
结束
下一步我
 
 
 ElseIf TypeOf .Recordset是ADODB.Recordset然后
 
 
错误恢复下一个
设置rstADO = .Recordset 
 
 
如果rstADO.State<> 0然后rstADO.Close 
 rstADO.Open 
设置conADO = rstADO.ActiveConnection 
 For i = 0 To conADO.Errors.Count 
 With conADO.Errors（i）
 Debug.PrintADODB Recordset'&左（rstADO.Source，24）& 'connection returned error'&数字& （native error'& .NativeError&'）来自'&来源& '：& 。描述
结束
下一步我
 
 ElseIf Err.Number<> 0然后
 
 
 Debug.Print Err.Source& 错误&错误编号& ：& Err.Description 
 
 
否则
 
 
 Debug.Print记录集类型是：'& TypeName（.Recordset）& '：有关更多信息，请在代码中放置断点并使用本地窗口。
 
 
如果
 
以
 
 End Sub 
 
  

代码做什么或尝试做的很简单：它询问数据库和检索详细的错误消息。

他们可能会告诉您SQL中存在语法错误或缺少的参数 - 如果数据库是MS-访问：'missing parameter'可能意味着字段名称或函数名称未知。这也许意味着您无法在MS-访问用户会话。

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

这几乎是对于我可以对这个问题提出的调试工具：希望另一个堆垛机可以建议其他方法。

EDIT: New mistake found?

I may have just found out why my script wouldn't work anymore. There has been an office update recently and it seems they have implemented the microsoft query which gets rid of the ODBC-Manager which I installed and configured to my needs. Now the actual problem may be that this microsoft query doesn't have the UNICODE-library and I don't know where I could put them so that the microsoft query would read/get them. Or rather I don't know how to give the microsoft query the path to the UNICODE-library as in the manager there is no possibility to change it, or maybe there is and I just didn't find it? In addition to this I cannot open the microsoft query manager unless I open it directly from the excel workbook.

ORIGINAL QUESTION

This code worked for some time. Now with the exact same code I get a runtime error. Here I already asked for help in this case and got it to work.

Here's the code:

Sub ConnectSQL()

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

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

    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

I did use .Select twice here and I know I should change it.

I am working in Excel 2011 on a Mac so be aware that not everything that works on windows will work.

* EDIT *

First of all: Even if I have all the object libraries activated, this still doesn't work.

Secondly, I just realized some things as I opened the Object Browser:

1. "ODBCConnection" is a declared class ("Class ODBCConnection Member of Excel"). But when I click on Excel in the Object-Browser "ODBCConnection" is nowhere to be found. If I enter the code "Excel.ODBCConnection.Refresh" it throws the error "Method or Data Member not found" highlighting only the word "ODBCConnection". Same error is shown when I use only ODBCConnection.Refresh (Refresh being a method of ODBCConnection according to the Object Browser).

2. I have exactly the same problem with "QueryTable(1).Refresh", which is throwing the error: "Variable not defined" even though it's listed and declared in the object-browser ("Class QueryTable Member of Excel").

I believe I would get these kind of errors for all the things that are connected to this.

EDIT

This throws the same "General ODBC error" on the line ActiveWorkbook.RefreshAll:

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=usr_gi_kunden;Pwd=au382k8?X."

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

解决方案

I can't solve your problem, but I can help you debug it.

The first question is: what does that error message mean? Is it telling you that there's an error with the query or command you're running on the data, or is something preventing you connecting to the database?

It is actually possible to write informative error messages, and whatever misguided genius at Redmond implemented the Query Table chose not to pass through the detailed error information emitted by the database server and the connection libraries.

Fortunately, we do some of that work ourselves.

The QueryTable object has a connection property - it's a string, not a fully-featured connection object, but you can examine it in more detail and test it against the ADODB connection object. Try this function for testing connection strings, and see if there's any useful information:

Public Sub ConnectionTest(ConnectionString As String)

' Late-binding: requires less effort, but he correct aproach is
' to create a reference to 'Microsoft ActiveX Data Objects' -

'Dim conADO As ADODB.Connection
'Set conADO = New ADODB.Connection
Dim conADO As Object
Set conADO = CreateObject("ADODB.Connection")

Dim i As Integer

conADO.ConnectionTimeout = 30
conADO.ConnectionString = ConnectionString

On Error Resume Next

conADO.Open

If conADO.State = 1 Then
    Debug.Print "Connection string is valid"
Else
    Debug.Print "Connection failed:"


    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


End If

Debug.Print "Connection String: "
Debug.Print vbTab & Replace(.Connection, ";", ";" & vbCrLf & vbTab)
Debug.Print


Set conADO = Nothing


End Sub

...And insert it into your code:

Dim objQueryTable As Excel.QueryTable
Dim strConnect as String

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

If you can see errors in that, it might just be that my implementation of an ADODB connection doesn't work in your Mac Office environment: but it's entirely possible that you have either:

1. Created a connection, seen it working, and eliminated the possibility that your connection string or DSN is the source of the problem...
2. ...Or spotted an error in the connection parameters which you can fix.

If the connection's working, it's probably the query or command you're running at the database that's the source of the problem - and the error messages I've seen in your question do point in that direction - so we need to delve a little deeper.

Unfortunately, I have no way of knowing whether the tools I use for that will work for you: this is developers' debugging code, and you'll need to tinker with it to get it to work.

The reason it's so fiddly is that the Office team who implemented the QueryTable made some interesting decisions: the 'connection' and 'recordset' properties exposed by the QueryTable aren't fully-featured objects - I think that they are interfaces which allow the QueryTable to make use of objects called 'connection' and 'recordset' from a variety of different providers, and expose a common set of properties and methods. It's a good decision for cross-platform usability, but it means that a developer who needs to interrogate those objects can't rely on any given method being present at runtime - so this is for decompiled code in 'debug' mode only.

You'll also need to register Microsoft DAO and ADO references in the IDE: late-binding with 'CreateObject' is the wrong tool when you need to be able to view these objects in the 'Locals' window:

Public Sub ConnectionDetails(objQueryTable As Excel.QueryTable)

Dim rstADO As ADODB.Recordset
Dim conADO As ADODB.Connection

Dim rstDAO As DAO.Recordset
Dim conDAO As DAO.Connection

Dim i As Integer


Set objQueryTable = Sheet1.ListObjects(1).QueryTable

With objQueryTable


    Debug.Print "Connection String: "
    Debug.Print vbTab & Replace(.Connection, ";", ";" & vbCrLf & vbTab)
    Debug.Print


    Debug.Print "Query Type: " & .QueryType  ' Documented here: https://msdn.microsoft.com/en-us/library/office/ff835313.aspx
    Debug.Print "Query: " & .CommandText
    Debug.Print "Database request type: " & .CommandType   ' XlCmdType documented here: https://msdn.microsoft.com/en-us/library/office/ff197456.aspx


    .MaintainConnection = True

    On Error Resume Next
    If TypeOf .Recordset Is DAO.Recordset Then


        On Error Resume Next
        Set rstDAO = .Recordset


        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


        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


    ElseIf TypeOf .Recordset Is ADODB.Recordset Then


        On Error Resume Next
        Set rstADO = .Recordset


        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

    ElseIf Err.Number <> 0 Then


        Debug.Print Err.Source & " Error " & Err.Number & ":" & Err.Description


    Else


        Debug.Print "recordset type is: '" & TypeName(.Recordset) & "': for further information, place a breakpoint in the code and use the 'Locals' window."


    End If

End With

End Sub

What the code does - or attempts to do - is quite straightforward: it interrogates the database and retrieves the detailed error messages.

They will probably tell you that there's a syntax error, or a missing parameter in the SQL - which can be misleading if the database is MS-Access: 'missing parameter' might mean a field name or function name is unknown. It might also mean that you can't run that SQL outside an MS-Access user session.

If it fails to work, go back to the ConnectionTest code and run the command text against the conADO connection object: conADO.Execute strCommandText ...And interrogate the errors collection again.

That's pretty much it for the debugging tools I can bring to bear on this problem: hopefully another 'Stacker can suggest other approaches.

这篇关于刷新QueryTable抛出“常规ODBC错误” - VBA Excel 2011 for Mac的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！