The "an error" is trying to tell you what's wrong. What does it say ?

You dont' specify a cursor. If it's forwardonly then you can't move back and
forth.

And your paste method is painfully slow. Looping record by record is only
required if you manipulate them on the fly. This single line ought to do the
whole job:

Range("A2").CopyFromRecordset rstRecordSet

HTH. Best wishes Harald

"Braindeadbeachbum" skrev i
melding ...
Hi Guys

I need to go through each record one at a time to convert RTF to Text from
SQL. The code I currently use is the following. My problem is that I
cannot
define the rstRecordSet!strFieldName like I'm doing it now as I receive an
error. Is there any way around hard coding each fieldname?


Private Sub GetData()
Dim i As Integer
Dim strCellLocation As String
Dim strRecord As String
Dim strFieldName As String


Sheets("Raw Data").Select
Range("A1").Select

On Error GoTo ErrorHandler

Set connConnect = CreateObject("ADODB.Connection")
Set rstRecordSet = CreateObject("ADODB.Recordset")

ActiveSheet.Cells.Clear
Set TargetRange = ActiveSheet.Cells(1, 1)

With connConnect
.provider = "SQLOLEDB"
.connectionString = "Data Source=?????????;Integrated
Security=SSPI;Initial Catalog=???????"
.Open
End With

With rstRecordSet
.Open strSQLCommAND, connConnect
For intColIndex = 0 To rstRecordSet.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value =
rstRecordSet.Fields(intColIndex).Name
Next
End With

i = 2
rstRecordSet.movefirst
Do Until rstRecordSet.EOF
For intColIndex = 0 To rstRecordSet.Fields.Count - 1
strFieldName = rstRecordSet.Fields(intColIndex).Name
TargetRange.Offset(i, intColIndex).Value =
rstRecordSet!strFieldName
Next
i = i + 1
rstRecordSet.movenext
Loop

rstRecordSet.Close
Set rstRecordSet = Nothing
connConnect.Close
Set connConnect = Nothing

Exit Sub
ErrorHandler:
If connConnect.State = 1 Then
connConnect.Close
Set connConnect = Nothing
End If
MsgBox "Error - " & Err.Description, vbCritical, Err.Source

End Sub