ADO Record by Record

[Braindeadbeachbum]

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

"Braindeadbeachbum" <Braindeadbeachbum@discussions.microsoft.com> skrev i
melding news:E65F95CA-25F4-425D-A781-5D298B20F4D2@microsoft.com...
> 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
>

[Braindeadbeachbum]

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" wrote:

> 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" <Braindeadbeachbum@discussions.microsoft.com> skrev i
> melding news:E65F95CA-25F4-425D-A781-5D298B20F4D2@microsoft.com...
> > 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]

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

"Braindeadbeachbum" <Braindeadbeachbum@discussions.microsoft.com> skrev i
melding news:5EA5B149-7254-477F-B58A-376BC1A7A986@microsoft.com...
> 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" wrote:
>
> > 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" <Braindeadbeachbum@discussions.microsoft.com> skrev
i
> > melding news:E65F95CA-25F4-425D-A781-5D298B20F4D2@microsoft.com...
> > > 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
> > >
> >
> >
> >

[Braindeadbeachbum]

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

Thanks alot!

"Harald Staff" wrote:

> 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
>
> "Braindeadbeachbum" <Braindeadbeachbum@discussions.microsoft.com> skrev i
> melding news:5EA5B149-7254-477F-B58A-376BC1A7A986@microsoft.com...
> > 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" wrote:
> >
> > > 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" <Braindeadbeachbum@discussions.microsoft.com> skrev
> i
> > > melding news:E65F95CA-25F4-425D-A781-5D298B20F4D2@microsoft.com...
> > > > 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]

That's the spirit ! You're welcome.

Best wishes Harald

"Braindeadbeachbum" <Braindeadbeachbum@discussions.microsoft.com> skrev i
melding news:3FC26863-05A6-4D85-9A51-9B9C0C8D937A@microsoft.com...
> Harald Staff is the best! Never knew you could referance the index number
> instead of the field name.
>
> Thanks alot!