excel 2003 - VBA - connect to SQL SERVER 2005

[silentbuddha]

Hi,

I am having difficulties in getting the column headers from a MS SQL Server 2005 database table

Here is the code I have so far :

Private Sub getRetentionDetails(date1 As String, date2 As String)
'This was set up using Microsoft ActiveX Data Components version 2.8

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strSQLHeaders As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rangeStart As Range
Dim iCol As Integer

Const CONN_STRING As String = "Provider=SQLNCLI;Server=sql.XYZreporting;Database=Business_Analysis;Trusted_Connection=yes;HDR=yes"";"

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("RETENTION_DETAILS")

With ws
Set rangeStart = .Range("A2")
End With

strSQL = "select [Week],[Requests],[SRN],[%SWA],[Pending%],[Cost/Save with Agreement],[Cost/Save W-O Agreement],[Cost/Save Total],[Saves],[Deacts],[%Mig],[Mig],[Saves Net],[Renewals],[Renewals 2Y%],[Renewals 3Y%],[Pendings],[SRV]" & _
" from dbo.Consolidated_KPI_RetentionDetails " & _
" where calendar_date between " & "'" & date1 & "'" & " and " & "'" & date2 & "'" & _
" group by [Week],[Requests],[SRN],[%SWA],[Pending%],[Cost/Save with Agreement],[Cost/Save W-O Agreement],[Cost/Save Total],[Saves],[Deacts],[%Mig],[Mig],[Saves Net],[Renewals],[Renewals 2Y%],[Renewals 3Y%],[Pendings],[SRV]" & _
" order by [week]"
strSQLHeaders = "select top 1 * from dbo.Consolidated_KPI_RetentionDetails"

'part 1 : get the headers and place them into worksheet RETENTION_DETAILS
Set conn = New ADODB.Connection

With conn
.CursorLocation = adUseClient
.Open CONN_STRING
.CommandTimeout = 0
Set rs = .Execute(strSQLHeaders)
End With

For iCol = 1 To rs.Fields.Count
Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
'cleanup and close connection
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

'***************** end of part 1 *****************'

Thanks in advance !

 

[silentbuddha]

Here is my solution with some modifications :

Private Sub getRetentionDetails(date1 As String, date2 As String)
'This was set up using Microsoft ActiveX Data Components version 2.8

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim strSQL As String
Dim strSQLHeaders As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rangeStart As Range
Dim iCol As Integer

Const CONN_STRING As String = "Provider=SQLNCLI;Server=sql.XYZreporting;Database=Business_Analysis;Trusted_Connection=yes;HDR=yes"";"

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("RETENTION_DETAILS")

With ws
Set rangeStart = .Range("A2")
End With

strSQL = "select [Week],[Requests],[SRN],[%SWA],[Pending%],[Cost/Save with Agreement],[Cost/Save W-O Agreement],[Cost/Save Total],[Saves],[Deacts],[%Mig],[Mig],[Saves Net],[Renewals],[Renewals 2Y%],[Renewals 3Y%],[Pendings],[SRV]" & _
" from dbo.Consolidated_KPI_RetentionDetails " & _
" where calendar_date between " & "'" & date1 & "'" & " and " & "'" & date2 & "'" & _
" group by [Week],[Requests],[SRN],[%SWA],[Pending%],[Cost/Save with Agreement],[Cost/Save W-O Agreement],[Cost/Save Total],[Saves],[Deacts],[%Mig],[Mig],[Saves Net],[Renewals],[Renewals 2Y%],[Renewals 3Y%],[Pendings],[SRV]" & _
" order by [week]"

'Create the ADO connection object
Set conn = New ADODB.Connection

'Apply some settings to the ADO connection object
'Open the connection to the database : .Open CONN_STRING
'Store the result in rs recordset object : Set rs = .Execute(strSQL)
With conn
.CursorLocation = adUseClient
.Open CONN_STRING
.CommandTimeout = 0
Set rs = .Execute(strSQL)
End With


'Apply the rs fieldnames ( column headers ) into Worksheets("RETENTION_DETAILS")
iCol = 1
For Each fld In rs.Fields

MsgBox "column # " & iCol & " filedname is : " & fld.Name

ws.Cells(1, iCol).Value = fld.Name
iCol = iCol + 1
Next

'Paste the dataportion of the recordset into worksheet RETENTION_DETAILS
rangeStart.CopyFromRecordset rs
'cleanup and close connection
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

 

[Blade Hunter]

change For iCol = 1 To rs.Fields.Count to For iCol = 1 To rs.Fields.Count + 1

You are starting at 1 which is fine because you minus 1 when referencing the field (which starts at zero) but because you minus one you never get the final value.