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.
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:
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
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
problem… - …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.
