ADO Record by Record

[Guest]

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

 

[Harald Staff]

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

 

[Guest]

Hi Harald

When I import using the complete recordset (Range("A2").CopyFromRecordset
rstRecordSet) I receive the following error:
Error - Automation error Unspecified error

I will bet that it's due to the RTF field being to long or unrecognizable. I
know that the Excel limit per cell is 32 767 characters and don't believe the
field is larger than this but I still think that the RTF field is causing the
problem as it's the only thing that changed since the query worked.

If I use the code attached the error is as follows:
Error - Item cannot be found in the collection corresponding to the
requested name or ordinal.

If I change:
TargetRange.Offset(i, intColIndex).Value = rstRecordSet!strFieldName
To:
TargetRange.Offset(i, intColIndex).Value = rstRecordSet!fldName
,where fldName is the actual field name, the query works but this means I
will have to hard code all the field names.

I even tried the following in vain:
rstRecordSet!rstRecordSet.Fields(intColIndex).Name

I know I know, it was a long shot in frustration!

Bottom line is that I will have to loop through the records to enable me to
edit the RTF field but I don't want to hardcode the field names as this just
does not look right and there must be a easier way of doing this.

 

[Harald Staff]

Nevermind the names. You should be able to address its index number
directly:

TargetRange.Offset(0, intColIndex).Value = rstRecordSet(intColIndex)

Why insert 32k + fields into Excel ? It will probably not make any sense in
a spreadsheet. What happens if you remove that field from your SQL, does it
still err ?

HTH. Best wishes Harald

 

[Guest]

Harald Staff is the best! Never knew you could referance the index number
instead of the field name.

Thanks alot!

 

[Harald Staff]

That's the spirit ! You're welcome.

Best wishes Harald