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

You should post the code of your sub for us to try to answer this — Lamak, Apr 19 '12 at 12:44

score 10 · Answer 1 · answered Nov 16 '12 at 09:03

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).

This seems to be an ADODB issue, I had the exact same problem in an ASP Classic application which use ADODB to connect to SQL server. — ADyson, May 05 '21 at 13:54

Lamak · Answer 2 · 2012-04-19T13:19:57.077

3

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

edited Apr 19 '12 at 13:19

answered Apr 19 '12 at 13:12

Lamak

69,480
12
108
116

I'm afraid that didn't work. It did print the field name at the head of the column, but I still have the same problem with missing data. Thanks anyway though! – Liam Barrett Apr 19 '12 at 13:37
@LiamBarrett - You have this problem with every query you try on that sub? – Lamak Apr 19 '12 at 13:42
No, only on this one particular query, I've tried others and they all work fine. It's quite strange. The only thing I can think of is that it must be the actual content of the database field that's somehow causing the problem. – Liam Barrett Apr 19 '12 at 13:46
@LiamBarrett - That must be the problem, can you compare the results that you get from querying the database directly with the data that your sub is pasting? – Lamak Apr 19 '12 at 13:49
I've actually figured it out, based on the code you gave me. Thanks a million, couldn't have done it without your help! I'm still a new user, so I can't answer my own question before 6 hours are up, but I can put my solution here later if you're curious to see it – Liam Barrett Apr 19 '12 at 14:23
@LiamBarrett - Good thing that you figured it out. Be sure to post your answer when you can and mark it as accepted. – Lamak Apr 19 '12 at 14:31

score 3 · Accepted Answer · answered Apr 20 '12 at 09:16

I think I've found the solution, thanks to Lamak's help:

rst.Open sql, con

Dim iRows As Integer
For iCols = 0 To rst.Fields.Count - 1
    ws.Cells(1, iCols + 1).Select
    With Selection
        .Value = rst.Fields(iCols).Name
        .Font.Bold = True
        .EntireColumn.AutoFit
    End With
Next iCols

iRows = 2

While Not rst.EOF
    For iCols = 0 To rst.Fields.Count - 1
        ws.Cells(iRows, iCols + 1).Value = rst.Fields(iCols).Value
    Next iCols
    rst.MoveNext
    iRows = iRows + 1
Wend

The problem seems to have been trying to copy all the fields out of the record set at once, copying the record field by field and row by row seems to solve the problem.

score 1 · Answer 4 · edited Jun 16 '14 at 09:45

I've had a similar problem using a recordset. It would return a set of customer results in the SQL query just fine, but would not paste the results to the worksheet on certain customers.

Following the tip to cast my data, I dug deeper in my query and noticed that there were null values hidden in the result set. From the appearance of the null in the recordset, no results beyond that null value (ordered by that specific column) would be pasted on my worksheet.

Or in pseudocode:

SELECT DISTINCT a,b,e
FROM DATASET

where column c would contain a null value.

So my solution : Remove duplicates and null values in your result set in SQL before using it in Excel.

score 0 · Answer 5 · answered Jun 11 '19 at 15:15

I'm wagering that your issue was that the recordset (in memory) was not fully populated with data from the data source. This is a known behavior of ADO recordsets.

The simplest workaround for this is that before the ".CopyFromRceordset rst" method call that you do a "rst.movelast" - and that this method will assure the full loading of the recordset with data from the datasource.

Your RBAR (Row By Agnonizing Row) alternative process achieves this same result by its use of the .movenext method to sequentially move through the records in the recordset (which causes the ADO code to continually load new data as the record pointer moves through the loaded data pages in memory).

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

5 Answers5

Linked