Question or issue on macOS:
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.
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:
I believe I would get these kind of errors for all the things that are connected to this.
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
How to solve this problem?
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.ConnectionDim 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:
- Created a connection, seen it working, and eliminated the
possibility that your connection string or DSN is the source of the
- …Or spotted an error in the connection parameters which you can
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:
…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.