SQL query doesn't return full results for only one field

Question

I'm having a problem using VBA to execute a SQL query and copy the results into an Excel worksheet.

When the sub excecutes, it only copies rows that are multiples of 256 (so rows 256, 512, 768 etc are the only ones that are filled into Excel). I'm having no problem copying any of the other fields from the database. Also, when I run the same query in MySQL it works fine. Being fairly new to both SQL and VBA I can't see any reason why this particular field should be causing trouble. The only thing I can think of is that its contents are a string that always begins with an underscore (and I only mention that because it's the only difference between it and some of the other fields).

Does anybody have any ideas as to why this may be happening?

Cheers,

Liam

EDIT: Here's a snippet of the code in question. To be honest, I'm not sure if seeing the code will make a difference, seeing as it works just fine for other situations, but then again, that's why I'm the newbie :)

        Dim con As ADODB.Connection
        Dim rst As ADODB.Recordset

        Set con = New ADODB.Connection
        Set rst = New ADODB.Recordset

        con.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=ipaddress;UID=userID;PWD=password;DATABASE=jiradb;OPTION=16427;"
        con.Open

        sql = "SELECT TEMPO_DATA FROM gssd_worklog WHERE WORK_DATE BETWEEN '2012-01-01' AND '2012-03-31'"

        'Open Recordset'
        rst.Open sql, con

        'Copy Data to Excel'
        Set ws = ActiveSheet

        ws.Range("A2").CopyFromRecordset rst

Answer 1

I ran into a very similar problem yesterday and found this thread whilst researching so wanted to add my "solution" in case it helps anyone else.

To refine the problem description I found that it applied to one particular field in my dataset and, interestingly, every subsequent field if I re-ordered the query. Adding trailing or removing earlier fields made no difference to my problem column.

Checking the types revealed it was the same type as some of the other fields that did work so no clue there either.

However, as it was a specific field (in this case a text field), I decided to try changing my SQL query to CAST the problem field, changing:

SELECT Col1, Col2, Col3 FROM TableName

...to...

SELECT Col1, Col2, CAST(Col3 AS VARCHAR(8)) AS Col3 FROM TableName

...and suddenly all the data appears (including any trailing columns).

Answer 2

I think that your problem is with your last line, when you are trying to copy the record on the worksheet. Try something like this (code modified from http://msdn.microsoft.com/en-us/library/aa223845(v=office.11).aspx):

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
ws.Range("A2").CopyFromRecordset rst