I need column headings to be returned with this code ? 1

[Recce]

Good day,

I got some very cool code of the net and it works wonders for me.
I was just wondering if someone can tell me how do I go about getting the column headings returned with the data from SQL server ? (below is my code)


Sub ImportData()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=MyServer CATALOG=MyDB

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn



With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM MyTable
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub


"We know nothing but, that what we know is not the truth..." - Me

 

[PHV]

Replace this:
Sheet1.Range("A1").CopyFromRecordset rsPubs
with something like this:
For i = 0 To .Fields.Count - 1
Sheet1.Cells(1, i + 1) = .Fields(i).Name
Next
Sheet1.Range("A2").CopyFromRecordset rsPubs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Recce]

Works briliantly, thx !

"We know nothing but, that what we know is not the truth..." - Me