Alright, I've setup a little macro in excel based on VBA code which runs a query against a MySQL database. This setup has been working great for us without any problems until now.
It seems the SQL query I'm trying to run is breaking QueryTables somehow. This query I'm trying to run is complex and involves a "UNION ALL". This SQL query runs perfectly fine in MySQL Query Browser.
However, when I try and run this query using QueryTables.Add and ODBC, some of the columns are not being returned.
Specifically, I have found that the only columns not being returned are ones which do not have matching UNION "Field Types".
aka: On the top part of my UNION, the first column returned is an INT, and on the bottom of my UNION (the second query), the first column is a VARCHAR. For some reason MySQL Query Browser is perfectly okay showing these results, but QueryTables can't handle it, and drops these columns.
ANY help would be VERY APPRECIATED!!
Here is my VBA code:
It seems the SQL query I'm trying to run is breaking QueryTables somehow. This query I'm trying to run is complex and involves a "UNION ALL". This SQL query runs perfectly fine in MySQL Query Browser.
However, when I try and run this query using QueryTables.Add and ODBC, some of the columns are not being returned.
Specifically, I have found that the only columns not being returned are ones which do not have matching UNION "Field Types".
aka: On the top part of my UNION, the first column returned is an INT, and on the bottom of my UNION (the second query), the first column is a VARCHAR. For some reason MySQL Query Browser is perfectly okay showing these results, but QueryTables can't handle it, and drops these columns.
ANY help would be VERY APPRECIATED!!
Here is my VBA code:
Code:
Sub SQLGetNewData()
Dim sConn As String
Dim sSql As String
Dim sDateFrom As String
Dim sDateTo As String
Dim oQt As QueryTable
'Sets the database connection
sConn = "ODBC;DSN=Database_Name;UID=username;PWD=password"
'get query parameters
Worksheets("Data").Activate
'build the query.
' I have not included the original complex SQL query here, and I have created a "VIEW" in MySQL to replicate the query. Because I have created a VIEW, I simply need to query this view just like it's a table.
sSql = "SELECT * from view_revenue "
Worksheets("Query Info").Activate
[B7].Formula = sSql
'clear the data area
Worksheets("Data").Activate
Range("a1").CurrentRegion.ClearContents
'clear existing data connections
For Each oQt In ActiveSheet.QueryTables
oQt.Delete
Next oQt
If ActiveWorkbook.Connections.Count > 0 Then
i = 1
Do While ActiveWorkbook.Connections.Count > 0
ActiveWorkbook.Connections.Item(i).Delete
i = i - 1
Loop
End If
'get new data
With ActiveSheet.QueryTables.Add(Connection:=sConn, _
Destination:=Range("A1"), Sql:=sSql)
.Refresh BackgroundQuery:=False
.RefreshPeriod = 0
.RefreshOnFileOpen = False
End With
Worksheets("Data").Activate
End Sub
