How to convert a Microsoft SQL date to a vba string?

Question

I have a database in Microsoft SQL with 3 columns. The last column is a date which has the format: dd-mm-yyyy hh:mm-ss (ex: 20-May-15 22:31:38). I managed to export the other 2 columns in Excel, but when I try to import the last column it gives me the next error: "Run-time error '3265': Application-defined or object-defined error".

My vba script is:

Private Sub CommandButton1_Click()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim r As Integer
    Set con = New ADODB.Connection
    Dim s As String 
    con.ConnectionString = "Provider=SQLOLEDB;Data Source=AEG-PC\WINCC; Initial Catalog=DBRapPlopeni; User ID=AEGRap; Password=1qaz2wsx; Trusted_Connection=yes"
    con.Open
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = con
    rs.Open "EXEC SotoredProcedure 20,05,2015"
    r = 5
    Do While Not rs.EOF
      Cells(r, 2) = rs.Fields(0)
      Cells(r, 3) = rs.Fields(1)
      Cells(r, 4) = rs.Fields(2) ' this field of date which cannot be imported
rs.MoveNext
r = r + 1
Loop
End Sub

I tried to convert date field in a string format but it didn't work for me. Does anyone know how to solve this problem?

Many thanks in advance!

Answer 1

Had a similar issue and I used the GetRows method to store the data in an array and manipulate them from there.

Something like the below:

Replace:

Do While Not rs.EOF
  Cells(r, 2) = rs.Fields(0)
  Cells(r, 3) = rs.Fields(1)
  Cells(r, 4) = rs.Fields(2) ' this field of date which cannot be imported
  rs.MoveNext
  r = r + 1
Loop

with

dim ar() as variant
ar = rs.GetRows

and then do a for loop to store the array data in the required cells.

for r =0 to ubound(ar,2) 'your array has 0 to ubound(ar,2) rows
        cells(r,2) = ar(1,r)
        cells(r,3) = ar(2,r)
        cells(r,4) = ar(3,r)
next r

Hope this helps.