Excel VBA ADODB RecordSet changes Field types from SQL Server

Question

I am running a simple query on a SQL Server table:

SELECT * FROM MyTable

The table contains three columns, with types int, bit, and datetime2(0). I am using code along the lines of:

Dim cnn As ADODB.Connection
Dim rst As ADODB RecordSet
Dim rngDest As Range
:
: ' etc
:
rst.Open "SELECT * FROM MyTable", cnn
rngDest.CopyFromRecordset rst

This works well, except that it seems to be getting the field types wrong: it says the field types are 3, 11, 202, which are, according to the documentation, Integer (correct), Boolean (correct, I guess), and NVarChar2 -- incorrect. The third field should be a date, surely ("Date" type is 135).

As background: I migrated the data from Access to SQL Server using SSMA. This table now has the three column types I mentioned. Also, I understand that it is a well-known problem that CopyFromRecordset can get the field types wrong, and I initially thought that was why the third column (the date) was appearing in Excel as a string, but upon closer inspection I can see that when the VBA has read the Recordset it already believes the field is text, before the CopyFromRecordset line. I feel if I could get the VBA to somehow recognise that the third field should be a date then I could convert it internally. I would rather not create a solution for this table specifically because I have many, many Tables and Views that have to be handled, so I would prefer to find an approach that works for all of them.

If it's important, the connection string I am using is:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=[REDACTED];Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=[REDACTED];

Can anyone help please?

Answer 1

The backwards compatible SQLOLEDB driver that ships with Windows has no notion SQL Server data types introduced over the last 20 years, like date. Try the latest SQL Server OLEDB driver, MSOLEDBSQL.

I ran a quick ADO test using the VbScript below and a date column returned ADO type 133 (adDBDate).

Set connection = CreateObject("ADODB.Connection")
connection.Open "Provider=MSOLEDBSQL;Data Source=.;Integrated Security=SSPI"
Set recordset = connection.Execute("SELECT CAST(SYSDATETIME() AS date);")
MsgBox recordset.Fields(0).Type
connection.Close

Answer 2

Here is the solution I have found to work (while I'm waiting for the support guys to update the driver): [TL;DR: Read the Recordset into a variant varOrig using rst.GetRows; transpose, then plonk into an Excel sheet; using .Value2 = causes Excel to convert it to a proper date. [We have to transpose because .GetRows, despite its name, returns columns of data.]]

Hope this is useful for anyone else in the future having a similar problem.

Public Sub RecordSetToRange(ByRef rst As ADODB.Recordset, ByRef lobDst As ListObject)

    Const strROUTINE As String = "RecordSetToRange"
    Dim iRow As Long
    Dim iCol As Long
    Dim varOrig As Variant
    Dim varTxp() As Variant

    ' -- Error Handling, Validation, Initialization.
    On Error GoTo ErrHandler

    ' -- Procedure.

    ' Headers first.
    With lobDst.HeaderRowRange.Cells(1)
        For iCol = 0 To rst.Fields.Count - 1
            .Offset(0, iCol).Value2 = rst.Fields(iCol).Name
        Next iCol
    End With
    ' Then data. Note that, despite the name of the function, GetRows returns columns of data, so we have
    ' to transpose it. Don't do anything if there is no data.
    If rst.RecordCount > 0 Then
        varOrig = rst.GetRows
        ReDim varTxp(UBound(varOrig, 2), UBound(varOrig, 1))
        For iRow = 0 To UBound(varOrig, 2)
            For iCol = 0 To UBound(varOrig, 1)
                varTxp(iRow, iCol) = varOrig(iCol, iRow)
            Next iCol
        Next iRow
        lobDst.DataBodyRange.Resize(UBound(varOrig, 2) + 1, UBound(varOrig, 1) + 1).Value2 = varTxp
    End If
    '
ErrHandler:        ' -- Error handling and Routine termination.
    If Err.Number <> 0 Then If DspErr(mstrMODULE, strROUTINE) Then Stop: Resume Else End
End Sub

Answer 3

Your date field may be string. so you convert the date. The following example converts a character date to date data. You will need to specify differently depending on what your character type is.

20130604060133 -> 2013/06/04 06:01:33

select
  convert(datetime,SUBSTRING( '20130604060133',1,8),112) +
  convert(datetime,
   ( substring( '20130604060133',9,2) + ':' +
    substring( '20130604060133',11,2)  + ':' +
    substring( '20130604060133',13,2)  )
    ,120) as myDate

The problem seems to be somewhere else, but it's not clear. But there is a way to change the data. See below.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rngDest As Range
:
: ' etc
:
rst.Open "SELECT * FROM MyTable", cnn
rngDest.CopyFromRecordset rst

Dim vDB
Dim rngDB As Range
Set rngDB = rngDest.CurrentRegion
vDB = rngDB
rngDB.NumberFormat = "General"
rngDB = vDB