ADO Recordset Not Returning Field Value With DSN; Does with Connection String

Question

I have the following Function:

Function downloadsqltoexcel(conn As ADODB.Connection, sSQL As String, exceldestinationrangename As String, sqltablename As String, bDownload As Boolean, Optional ws As Worksheet) As Variant

'================================================================================================================================
'== Procedure Name: downloadsqltoexcel
'== Purpose: downloads SQL table data (or query data) to Excel named range or grabs a specific value from an SQL table
'== Notes: ensure that SQL table or query name and Excel named range are identical
'================================================================================================================================

Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

Dim DestinationRange As Range
Dim sqlstring As String

Dim s As String

With rsPubs

    .ActiveConnection = conn
    .Open sSQL, conn, adOpenStatic, adLockReadOnly, adCmdText

    If bDownload Then 'if download switch is on, dump into Excel named range

        If ws Is Nothing Then
            Set DestinationRange = Range(exceldestinationrangename)
        Else
            Set DestinationRange = ws.Range(exceldestinationrangename)
        End If

        With DestinationRange
            .ClearContents
            .CopyFromRecordset rsPubs
        End With

        '.... more code below that is not relevant

Let's say I call the function like this:

Dim conPDDB As New ADODB.Connection
conPDDB.Open "myConnectionString" - see below

Dim sSQL as String
sSQL = "SELECT ... "

downloadsqltoexcel conPDDB, sSQL, "DSN", "", True, Sheet1

If I pass a hard-code connection string into myConnectionString I get the results I want. If I pass a DSN connection string the last column of data does not dump into Excel.

See picture

Provider=SQLOLEDB;Data Source=MYSERVER;Initial Catalog=MYDB;User Id=MYUID;Password=MYPWD

DSN=PDDB_QA;UID=MYUID;PWD=MYPWD (DSN configmirrors connection string)

The field itself is returning because when I print ?rsPubs.Fields(5).Value to the immediate window, I recieve AU,CI,GL,IM,PF in return. The last data has commas in this example, but it does not always commas. I have tried playing with DSN settings and also rs.Open arguments. No avail.

Anyone have any ideas?

Does the recordset just contain 1 record, or is that just your demo output data? If it contains more than 1 record, try changing the query to return just the top 1 record ("SELECT TOP 1 ..." perhaps? I think the syntax varies depending on what you are connecting to, and I can never remember which is which) to see whether it is something in the data itself that is causing the issue (e.g. nulls may be getting treated differently somehow). — YowE3K
@YowE3K - 1 record in this example. Same happens with multiple records. Returns last field value when passed with connection string or DSN. CopyFromRecordset method does not dump last column when passed with DSN. SQL is identical in both issues so not sure if data is the issue. — Scott Holtzman
This is my standard way of connecting to any MSSQL server and I never had a problem (including DNS in the connection string) in multiple countries: stackoverflow.com/questions/38744688/… Maybe you want to give this a try (just for comparison). Also, when I hear that one column is missing it sounds like Option Base 0 or Option Base 1 has been set someplace. Finally, have you set a break-point to verify the rsPub.Fields.Count? — Ralph
@Ralph - thanks for that link. It's an awesome piece of code. I'll reconfig it for my needs and try it out. Although connection is not really the issue and returning dataset is not the issue either. My recordset returns (see note about debugging rsPub.Fields(5).Value returns result to immediate window.... this was essentially same as verifying rsPub.Fields.Count). Re Option Base - are you saying it's set in Excel somewhere or on the DSN Config or something? I have an empty excel file with no option base set to recreate my issue. I run once with String, again with DSN and get above results. — Scott Holtzman
FWIW - I do also have SET NOCOUNT ON atop the SQL statement. — Scott Holtzman

Scott Holtzman Scott Holtzman · Accepted Answer · 2017-03-20T15:14:13

I am placing this as an answer for now but I am not satisfied with the solution. (I will implement now as release is two weeks away and there's plenty of more work to do).

If I don't find, or no one provides, a better solution I will accept this one and move on.

Refactoring the .CopyFromRecordset rsPubs line to the below, brings the last column.

Dim r as Integer
r = 1
With DestinationRange

    .ClearContents
    '.CopyFromRecordset rsPubs

    rsPubs.MoveFirst
    Do Until rsPubs.EOF

        Dim c As Integer
        For c = 0 To rsPubs.Fields.Count - 1
            DestinationRange.Cells(r, c + 1).Value = rsPubs.Fields(c)
        Next

        rsPubs.MoveNext
        r = r + 1

    Loop

End With

ADO Recordset Not Returning Field Value With DSN; Does with Connection String

1 Answers