CopyFromRecordset not getting Field Names 1

[MacroAlan]

My proof of concept is mostly working and pulling back 915K rows (now a typo) using my connection string and SQL. My problem is that I am not getting the field names into Excel 2010.

Connection String:

    'Use the SQL Server OLE DB Provider.
    strConn = "Trusted_Connection=Yes;Initial Catalog=CompanyWideMetrics;"  
    strConn = strConn & "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;"
    'Connect to the Pubs database on the local server.
    strConn = strConn & "SERVER=dev-sql;"
    'Use an integrated login.
    strConn = strConn & " UID=" & UserName & ";Workstation ID=" & CurMach & ";"

Recordset:

    Set rsPubs = New ADODB.Recordset
    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        .Open SQLstr
        ' Copy the records into cell A1 on Sheet1.
        Sheets("Inv").Range("A2").CopyFromRecordset rsPubs
        ' Tidy up
        .Close
    End With

Is there something I need to add to my connection string to get the field names?


Alan

 

[Andrzejek]

Try:

With rsPubs[green]
    ' Assign the Connection object.[/green]
    .ActiveConnection = cnPubs[green]
    ' Extract the required records.[/green]
    .Open SQLstr[green]
[blue]
    For i = 0 To .Fields.Count - 1
        xlSheet.Cells(1, i + 1) = .Fields(i).Name
    Next
[/blue]
    ' Copy the records into cell A2 on Sheet1.[/green]
    Sheets("Inv").Range("A2").CopyFromRecordset rsPubs[green]
    ' Tidy up[/green]
    .Close
End With

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.