ordinal position of fields in ado recordsets

[Donald Grove]

I want to populate an array with values from an ado recordset
(multiple rows)

I use the absolute position of the cursor in the recordset to define
the row of my array to be populated. I have a workaround for lack of
a way to define the ordinal position of a field (incrementing a
counter variable), but it feels so primitive:

dim Fld as Field
dim rst1 as new adodb.recordset
dim varArray()
dim intfieldmarker is integer

Redim varArray(rst1.r ecordcount, rst1.Fields.cou nt)

rst1.MoveFirst

Do While Not rst1.EOF
intfieldMarker = 0
For Each Fld In rst1.Fields
varArray(rst1.A bsolutePosition , intfieldMarker) = Fld
intfieldMarker = intfieldMarker + 1
Next Fld
rst1.MoveNext
Loop

If I used DAO, I could call the ordinal position of each field, and
use that to indicate the column of my array. But that method isn't
available with ADO, it seems. Does anyone know something a little
less risky than incrementing a counter?

Is it possible, for instance to store the field names in the first row
of the array, and then somehow use those fieldnames as pointers to the
column of the array? Just a thought...

[Terry Kreft]

Re: ordinal position of fields in ado recordsets

The absolute simplest way is to use the GetRows method

Dim varArray as variant

varArray = rst1.GetRows

You then have a two dimensional array the first dimension of which is the
columns the second dimenstion is the rows. (the opposite way round to how
you've defined your array).

Alternatively (to get rows by columns as you have now)

dim Fld as Field
dim rst1 as new adodb.recordset
dim varArray()
dim intfieldmarker as integer
dim lngRowCount as long


with rst1
.MoveLast
Redim varArray(0 to .RecordCount -1, 0 to .Fields.count -1)

.MoveFirst
lngRowCount = -1
Do Until .EOF
lngRowCount = lngRowCount + 1
For intfieldmarker = 0 To .Fields.Count -1
varArray(lngRow Count, intfieldMarker) = .fields(intfiel dMarker)
Next Fld
rst1.MoveNext
Loop
End With



--

Terry Kreft


"Donald Grove" <donaldgrove@ve rizon.net> wrote in message
news:7422v1p5r0 h04ku8cjg6mg4ol nl0knv7g0@4ax.c om...[color=blue]
> I want to populate an array with values from an ado recordset
> (multiple rows)0
>
> I use the absolute position of the cursor in the recordset to define
> the row of my array to be populated. I have a workaround for lack of
> a way to define the ordinal position of a field (incrementing a
> counter variable), but it feels so primitive:
>
> dim Fld as Field
> dim rst1 as new adodb.recordset
> dim varArray()
> dim intfieldmarker is integer
>
> Redim varArray(rst1.r ecordcount, rst1.Fields.cou nt)
>
> rst1.MoveFirst
>
> Do While Not rst1.EOF
> intfieldMarker = 0
> For Each Fld In rst1.Fields
> varArray(rst1.A bsolutePosition , intfieldMarker) = Fld
> intfieldMarker = intfieldMarker + 1
> Next Fld
> rst1.MoveNext
> Loop
>
> If I used DAO, I could call the ordinal position of each field, and
> use that to indicate the column of my array. But that method isn't
> available with ADO, it seems. Does anyone know something a little
> less risky than incrementing a counter?
>
> Is it possible, for instance to store the field names in the first row
> of the array, and then somehow use those fieldnames as pointers to the
> column of the array? Just a thought...
>[/color]

[Anthony England]

Re: ordinal position of fields in ado recordsets

"Donald Grove" <donaldgrove@ve rizon.net> wrote in message
news:7422v1p5r0 h04ku8cjg6mg4ol nl0knv7g0@4ax.c om...[color=blue]
>I want to populate an array with values from an ado recordset
> (multiple rows)
>
> I use the absolute position of the cursor in the recordset to define
> the row of my array to be populated. I have a workaround for lack of
> a way to define the ordinal position of a field (incrementing a
> counter variable), but it feels so primitive:
>
> dim Fld as Field
> dim rst1 as new adodb.recordset
> dim varArray()
> dim intfieldmarker is integer
>
> Redim varArray(rst1.r ecordcount, rst1.Fields.cou nt)
>
> rst1.MoveFirst
>
> Do While Not rst1.EOF
> intfieldMarker = 0
> For Each Fld In rst1.Fields
> varArray(rst1.A bsolutePosition , intfieldMarker) = Fld
> intfieldMarker = intfieldMarker + 1
> Next Fld
> rst1.MoveNext
> Loop
>
> If I used DAO, I could call the ordinal position of each field, and
> use that to indicate the column of my array. But that method isn't
> available with ADO, it seems. Does anyone know something a little
> less risky than incrementing a counter?
>
> Is it possible, for instance to store the field names in the first row
> of the array, and then somehow use those fieldnames as pointers to the
> column of the array? Just a thought...[/color]


Why not do it in one line?
varArray=rst1.G etRows

If you really had to do something else, why would you not simply use two
counters lngRow and lngCol to do this without referring to the
AbsolutePositio n property which is 1-based whereas your array seems to be
zero-based.

I also don't understand what you can do with DAO recordsets that you can't
with ADO. Both of these will get you the value of the first field in the
recordset assuming its name is "FirstField ".
rst.Fields(0).V alue
rst.Fields("Fir stField").Value

[Terry Kreft]

Re: ordinal position of fields in ado recordsets

Hmmm, Thinking about this again I would get the Rows/Columns version this
way

dim rst1 as new adodb.recordset
dim varTemp as Variant
dim varArray()
dim intfieldmarker as integer
dim lngRowCount as long

varTemp = rst1.GetRows

Set rst1 = nothing

' Then swap the array around
redim varArray(lbound (vartemp, 2) to ubound(vartemp, 2), lbound(vartemp,
1) to ubound(vartemp, 1))

For intfieldmarker = lbound(vartemp, 1) to ubound(vartemp, 1)
For lngRowCount = lbound(vartemp, 2) to ubound(vartemp, 2)
varArray(lngRow Count , intfieldmarker ) =
varTemp(intfiel dmarker, lngRowCount)
Next
Next


--

Terry Kreft


"Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
news:odycnWTSyN duTGzeSa8jmw@ka roo.co.uk...[color=blue]
> The absolute simplest way is to use the GetRows method
>
> Dim varArray as variant
>
> varArray = rst1.GetRows
>
> You then have a two dimensional array the first dimension of which is the
> columns the second dimenstion is the rows. (the opposite way round to how
> you've defined your array).
>
> Alternatively (to get rows by columns as you have now)
>
> dim Fld as Field
> dim rst1 as new adodb.recordset
> dim varArray()
> dim intfieldmarker as integer
> dim lngRowCount as long
>
>
> with rst1
> .MoveLast
> Redim varArray(0 to .RecordCount -1, 0 to .Fields.count -1)
>
> .MoveFirst
> lngRowCount = -1
> Do Until .EOF
> lngRowCount = lngRowCount + 1
> For intfieldmarker = 0 To .Fields.Count -1
> varArray(lngRow Count, intfieldMarker) =[/color]
..fields(intfie ldMarker)[color=blue]
> Next Fld
> rst1.MoveNext
> Loop
> End With
>
>
>
> --
>
> Terry Kreft
>
>
> "Donald Grove" <donaldgrove@ve rizon.net> wrote in message
> news:7422v1p5r0 h04ku8cjg6mg4ol nl0knv7g0@4ax.c om...[color=green]
> > I want to populate an array with values from an ado recordset
> > (multiple rows)0
> >
> > I use the absolute position of the cursor in the recordset to define
> > the row of my array to be populated. I have a workaround for lack of
> > a way to define the ordinal position of a field (incrementing a
> > counter variable), but it feels so primitive:
> >
> > dim Fld as Field
> > dim rst1 as new adodb.recordset
> > dim varArray()
> > dim intfieldmarker is integer
> >
> > Redim varArray(rst1.r ecordcount, rst1.Fields.cou nt)
> >
> > rst1.MoveFirst
> >
> > Do While Not rst1.EOF
> > intfieldMarker = 0
> > For Each Fld In rst1.Fields
> > varArray(rst1.A bsolutePosition , intfieldMarker) = Fld
> > intfieldMarker = intfieldMarker + 1
> > Next Fld
> > rst1.MoveNext
> > Loop
> >
> > If I used DAO, I could call the ordinal position of each field, and
> > use that to indicate the column of my array. But that method isn't
> > available with ADO, it seems. Does anyone know something a little
> > less risky than incrementing a counter?
> >
> > Is it possible, for instance to store the field names in the first row
> > of the array, and then somehow use those fieldnames as pointers to the
> > column of the array? Just a thought...
> >[/color]
>
>[/color]

[Tom van Stiphout]

Re: ordinal position of fields in ado recordsets

On Tue, 14 Feb 2006 12:18:54 GMT, Donald Grove
<donaldgrove@ve rizon.net> wrote:

And what are you going to do with that array? Iterate over it, find
items in it, sort it? All operations you can do on the recordset
itself. So keep the data in the rs, and don't create an unnecessary
copy.

-Tom.

[color=blue]
>I want to populate an array with values from an ado recordset
>(multiple rows)
>
>I use the absolute position of the cursor in the recordset to define
>the row of my array to be populated. I have a workaround for lack of
>a way to define the ordinal position of a field (incrementing a
>counter variable), but it feels so primitive:
>
>dim Fld as Field
>dim rst1 as new adodb.recordset
>dim varArray()
>dim intfieldmarker is integer
>
>Redim varArray(rst1.r ecordcount, rst1.Fields.cou nt)
>
>rst1.MoveFir st
>
>Do While Not rst1.EOF
>intfieldMark er = 0
>For Each Fld In rst1.Fields
>varArray(rst1. AbsolutePositio n, intfieldMarker) = Fld
>intfieldMark er = intfieldMarker + 1
>Next Fld
>rst1.MoveNex t
>Loop
>
>If I used DAO, I could call the ordinal position of each field, and
>use that to indicate the column of my array. But that method isn't
>available with ADO, it seems. Does anyone know something a little
>less risky than incrementing a counter?
>
>Is it possible, for instance to store the field names in the first row
>of the array, and then somehow use those fieldnames as pointers to the
>column of the array? Just a thought...[/color]

[Lyle Fairfield]

Re: ordinal position of fields in ado recordsets

When I want an array that emulates the recordset I generally use
GetString and Split, first on the row delimiter, and Split on the
column delimiter again on each element (row) of the array, giving me
an array of "sub" arrays, each sub array being the values of one
record.

Of course, if one is going to do multiple scans of the data, arrays are
much quicker than recordsets (or seem to be).

In languages where Arrays can be Sparse (eg Javascript) this can result
in amazing performance. We can number our row arrays according to the
(numerical) primary key of the recordset, assuming such exists. Then
Customers[7][10] gives us the value of the eleventh field of the
customer whose ID (PK) is 7. Is this possible in VBA? Sure, but I think
not nearly so effciently.

[polite person]

Re: ordinal position of fields in ado recordsets

On 14 Feb 2006 08:15:23 -0800, "Lyle Fairfield" <lylefairfield@ aim.com> wrote:
[color=blue]
>When I want an array that emulates the recordset I generally use
>GetString and Split, first on the row delimiter, and Split on the
>column delimiter again on each element (row) of the array, giving me
>an array of "sub" arrays, each sub array being the values of one
>record.
>
>Of course, if one is going to do multiple scans of the data, arrays are
>much quicker than recordsets (or seem to be).
>
>In languages where Arrays can be Sparse (eg Javascript) this can result
>in amazing performance. We can number our row arrays according to the
>(numerical) primary key of the recordset, assuming such exists. Then
>Customers[7][10] gives us the value of the eleventh field of the
>customer whose ID (PK) is 7. Is this possible in VBA? Sure, but I think
>not nearly so effciently.[/color]

When I once mentioned that I used arrays for recordset processing on the client side in web pages I
got such a supercilious reply that I've been in hiding ever since!

[Lyle Fairfield]

Re: ordinal position of fields in ado recordsets

Life's a bitch isn't it? Got a link to the thread so we can enjoy it
again? ... or just understand more clearly what you are talking about!

[polite person]

Re: ordinal position of fields in ado recordsets

On 14 Feb 2006 11:27:55 -0800, "Lyle Fairfield" <lylefairfield@ aim.com> wrote:
[color=blue]
>Life's a bitch isn't it? Got a link to the thread so we can enjoy it
>again? ... or just understand more clearly what you are talking about![/color]

1. try

http://groups.google.co.uk/group/comp.databases.ms-access/browse_frm/thread/e13dec01c3a60e53/565eaf96306fd87d?tvc=1&q=%22polite+person%22+web+array#565eaf96306fd87d

2. I rather over-reacted to David Fenton's reply

3. I couldn't resist a dig at XML (Codasyl in disguise)

I have a 1% finished and abandoned browser version of Access, mainly the DAO forms
model including forms design mode. Of course you have to code in Javascript! I remember I spent a
lot of the time on combo-boxes with Access-like selection, autocomplete etc. The actual treatment
of tables was very simple. But I only dealt with the simplest queries.

[Lyle Fairfield]

Re: ordinal position of fields in ado recordsets

If you're content with Internet Explorer and have ADO installed on the
client machine you can use ADO recordsets within HTML. No need of ASP
or any Server Side application. Just declare the ADO objects as
ActiveXObjects and IE will handle them within Client Side Script.
Except for concerns about security (everything in the HTML is available
to anyone downloading the page) this is pretty fast and cool.
The arrays you mention are another way. I use them within ASP; so only
the HTML output is downloaded. But they are incredibly fast as I think,
you imply.

[Terry Kreft]

Re: ordinal position of fields in ado recordsets

"Perhaps you could teach a pig to sing, but I for one would not want to
listen to it. "

Oh that's classic DWF, thank you for bringing it up again, that gave me a
good laugh just when I needed it.

--

Terry Kreft


"polite person" <sitting@ease.c om> wrote in message
news:60e4v1579i fdepd6ki8op1a09 59tkb1tcr@4ax.c om...[color=blue]
> On 14 Feb 2006 11:27:55 -0800, "Lyle Fairfield" <lylefairfield@ aim.com>[/color]
wrote:[color=blue]
>[color=green]
> >Life's a bitch isn't it? Got a link to the thread so we can enjoy it
> >again? ... or just understand more clearly what you are talking about![/color]
>
> 1. try
>[/color]
http://groups.google.co.uk/group/com...5eaf96306fd87d[color=blue]
>[/color]
<SNIP>

[Donald Grove]

Re: ordinal position of fields in ado recordsets

Actually it is to move the records into another table. Perhaps my
method is a little roundabout, but I am a rookie at this.
I take all the new records from tableA populate the array, then use
the array to add the new records to tableB.

So you are saying just open the two recordsets and at the new data
from one to the other and skip using the array altogether?

Honestly, I have been working directly with tables and queries in
access for about 5 years. It is only in the last 6 months that I have
even learned what a recordset is, let alone what an array is. So some
of my methods are probably more complicated than they need to be.

I want to do things in the most efficient way I can, but I have to
confess that I was using an array partly because I wanted to learn
more about them... :)

On Tue, 14 Feb 2006 07:13:44 -0700, Tom van Stiphout
<no.spam.tom774 4@cox.net> wrote:
[color=blue]
>On Tue, 14 Feb 2006 12:18:54 GMT, Donald Grove
><donaldgrove@v erizon.net> wrote:
>
>And what are you going to do with that array? Iterate over it, find
>items in it, sort it? All operations you can do on the recordset
>itself. So keep the data in the rs, and don't create an unnecessary
>copy.
>
>-Tom.
>
>[color=green]
>>I want to populate an array with values from an ado recordset
>>(multiple rows)
>>
>>I use the absolute position of the cursor in the recordset to define
>>the row of my array to be populated. I have a workaround for lack of
>>a way to define the ordinal position of a field (incrementing a
>>counter variable), but it feels so primitive:
>>
>>dim Fld as Field
>>dim rst1 as new adodb.recordset
>>dim varArray()
>>dim intfieldmarker is integer
>>
>>Redim varArray(rst1.r ecordcount, rst1.Fields.cou nt)
>>
>>rst1.MoveFirs t
>>
>>Do While Not rst1.EOF
>>intfieldMarke r = 0
>>For Each Fld In rst1.Fields
>>varArray(rst1 .AbsolutePositi on, intfieldMarker) = Fld
>>intfieldMarke r = intfieldMarker + 1
>>Next Fld
>>rst1.MoveNe xt
>>Loop
>>
>>If I used DAO, I could call the ordinal position of each field, and
>>use that to indicate the column of my array. But that method isn't
>>available with ADO, it seems. Does anyone know something a little
>>less risky than incrementing a counter?
>>
>>Is it possible, for instance to store the field names in the first row
>>of the array, and then somehow use those fieldnames as pointers to the
>>column of the array? Just a thought...[/color][/color]

[Donald Grove]

Re: ordinal position of fields in ado recordsets

Thanks for this. I read about the getrows method, but I was scared to
try it. I should play around with it. And thanks for cluing me in
that the columns are the first dimension. Is that the standard style
for 2D arrays, columns as first dimension, rows as second dimension?'

everyone's responses are super helpful!

On Tue, 14 Feb 2006 12:49:54 -0000, "Terry Kreft"
<terry.kreft@mp s.co.uk> wrote:
[color=blue]
>The absolute simplest way is to use the GetRows method
>
>Dim varArray as variant
>
>varArray = rst1.GetRows
>
>You then have a two dimensional array the first dimension of which is the
>columns the second dimenstion is the rows. (the opposite way round to how
>you've defined your array).
>
>Alternativel y (to get rows by columns as you have now)
>
> dim Fld as Field
> dim rst1 as new adodb.recordset
> dim varArray()
> dim intfieldmarker as integer
> dim lngRowCount as long
>
>
>with rst1
> .MoveLast
> Redim varArray(0 to .RecordCount -1, 0 to .Fields.count -1)
>
> .MoveFirst
> lngRowCount = -1
> Do Until .EOF
> lngRowCount = lngRowCount + 1
> For intfieldmarker = 0 To .Fields.Count -1
> varArray(lngRow Count, intfieldMarker) = .fields(intfiel dMarker)
> Next Fld
> rst1.MoveNext
> Loop
>End With[/color]

[Anthony England]

Re: ordinal position of fields in ado recordsets

"Donald Grove" <donaldgrove@ve rizon.net> wrote in message
news:tqr4v197ut 2bdmg4di8l1nhkn vqsuklq7o@4ax.c om...[color=blue]
> Actually it is to move the records into another table. Perhaps my
> method is a little roundabout, but I am a rookie at this.
> I take all the new records from tableA populate the array, then use
> the array to add the new records to tableB.
>
> So you are saying just open the two recordsets and at the new data
> from one to the other and skip using the array altogether?
>
> Honestly, I have been working directly with tables and queries in
> access for about 5 years. It is only in the last 6 months that I have
> even learned what a recordset is, let alone what an array is. So some
> of my methods are probably more complicated than they need to be.
>
> I want to do things in the most efficient way I can, but I have to
> confess that I was using an array partly because I wanted to learn
> more about them... :)[/color]


Moving records from one table to another should generally be done with just
executing an sql statement of the form INSERT INTO. This is usually not
only the easiest, but the most efficient. There are times when there are so
many operations to be done on the records before they are put back, that it
may be easier to do this with two recordsets: one open as forward-only
read-only and the other as append-only.

[Donald Grove]

Re: ordinal position of fields in ado recordsets

Ordinarily I would agree. But, I have a multiuser data system using
linked tables. I have found that linked tables make everything run
more slowly. REALLY REALLY slowly, and that using ADO for certain
basic tasks is just faster.

On Wed, 15 Feb 2006 13:11:24 +0000 (UTC), "Anthony England"
<aengland@oops. co.uk> wrote:
[color=blue]
>"Donald Grove" <donaldgrove@ve rizon.net> wrote in message
>news:tqr4v197u t2bdmg4di8l1nhk nvqsuklq7o@4ax. com...[color=green]
>> Actually it is to move the records into another table. Perhaps my
>> method is a little roundabout, but I am a rookie at this.
>> I take all the new records from tableA populate the array, then use
>> the array to add the new records to tableB.
>>
>> So you are saying just open the two recordsets and at the new data
>> from one to the other and skip using the array altogether?
>>
>> Honestly, I have been working directly with tables and queries in
>> access for about 5 years. It is only in the last 6 months that I have
>> even learned what a recordset is, let alone what an array is. So some
>> of my methods are probably more complicated than they need to be.
>>
>> I want to do things in the most efficient way I can, but I have to
>> confess that I was using an array partly because I wanted to learn
>> more about them... :)[/color]
>
>
>Moving records from one table to another should generally be done with just
>executing an sql statement of the form INSERT INTO. This is usually not
>only the easiest, but the most efficient. There are times when there are so
>many operations to be done on the records before they are put back, that it
>may be easier to do this with two recordsets: one open as forward-only
>read-only and the other as append-only.
>[/color]