Refresh QueryTable throwing "General ODBC error" - VBA Excel 2011 for Mac

Question

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

    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

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=*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

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.