ADO link to MySQL - Strange Results?!

[DarrenSL]

I want to be able to run queries ona MySQL database from Excel. It is something I have never done before so I set up a prototype model just to see if I could set up the link. The following code works except that only the ID's are returned, not the 'description'. Oddly, the same query works fine when looking at the same table in a MS SQL database (and MS Access).

Any idea's why? (the UNION seems to be casing the problem as without the UNION all data is returned).

Thanks in advance.


Sub RecordsetwithMYSQL()

'Open Connection and Recordeset
Dim rs As ADODB.Recordset
Dim SQLString As String
Set rs = CreateObject("ADODB.Recordset")

' Define SQL String
SQLString = "SELECT ID, Description from evo_CustomerType WHERE ID <= 10 " & _
"UNION SELECT ID, Description from evo_CustomerType WHERE ID > 30"


'Set Recordset Properties
With rs
.Source = SQLString
.ActiveConnection = "Warehouse-mysql" 'DatabaseName (in ODBC DSN)
.Open
End With

'Return Data to Excel
Sheet1.Range("a1").CopyFromRecordset rs

'Close Recordset
rs.Close
Set rs = Nothing

End Sub

[Macdave_19]

UNION ALL is only really used to extract data from 2 tables where the column headings are exactly the same mate.

so really the only reason you would use it is if you had 2 table say containing data from domain 1 and domain 2 respectively.

i.e.

Please Login or Register  to view this content.

Hope that helps a little mate

[DarrenSL]

Thanks MacDave. The query is a simple prototype just to test that I can link into a MYSQL database and that I can return data. Ultimately, I would want to add multiple tables, joins, Unions, subqueries etc but for now am trying to keep the SQL as simple as possible.

Its the fact that the UNION is not returning anything in the 'Description' fields that is confusing me, especially considering that this works fine for MS SQL databases.

[Macdave_19]

Yeah good luck with that mate lol i use SQL here and there and it's an pain in the butt, even more so when your throwing in excel because of god damn speech marks etc.

anyway like you said it works ok without it so "if it ain broke...."

cheers