Recordset confusion

[Gerry Abbott]

Hi all,

I having some confusing effects with recordsets in a recent project.

I created several recordsets, each set with the same number of records, and
related with an index value.
I create a table and add the index value and a value/s from each recordset
in turn, into a temporary table, which I used to create a report. I created
this with DAO objects, and it worked fine. I use iteration, and the
..movenext command to get the next values from the recordsets.

I then deployed into a users machine with access 2002, (which had dao 3.6
referenced, along with VB for applications, and Access 10 object library)
Now the results of the recordset appeared be in incorrect order, as if the
recordsets were not ordered in the same way as they had been on the
developer machine.
I then then removed the DAO references in all the objects and found that all
the record sets were then sorted correctly again!

I expected that by making an explicit reference to the DAO objects, and
assuming that the library is referenced on the user machine, that the
results would at least be consistent?
Is there another factor which I might have missed? Both developer and user
machines have up to date service packs.

Since I generally develop on older versions and deploy sometimes in later
versions of Access, its very important that I understand the factors which
might lead to inconsistencies at deployment stage.

Im sure there are threads on this topic, and if anyone has any comments, or
references to relevant threads, they would be most welcome.


Gerry Abbott

[Allen Browne]

Re: Recordset confusion

No, the order cannot be assumed to be consistent.

Think of a table as a bucket. You throw records in, but they are not ordered
unless you specify the order you want to retrieve them. Things like
compacting the database or using the table as a linked table are likely to
change the retrieval order if you do not specify the order you want. (In
practice, Access tends to use the primary key as the default order for
queries, but anything can happen in reports.)

So, add an AutoNumber field, or a date/time field or something that lets you
specify the order you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <please@ask.i e> wrote in message
news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...[color=blue]
> Hi all,
>
> I having some confusing effects with recordsets in a recent project.
>
> I created several recordsets, each set with the same number of records,[/color]
and[color=blue]
> related with an index value.
> I create a table and add the index value and a value/s from each recordset
> in turn, into a temporary table, which I used to create a report. I[/color]
created[color=blue]
> this with DAO objects, and it worked fine. I use iteration, and the
> .movenext command to get the next values from the recordsets.
>
> I then deployed into a users machine with access 2002, (which had dao 3.6
> referenced, along with VB for applications, and Access 10 object library)
> Now the results of the recordset appeared be in incorrect order, as if the
> recordsets were not ordered in the same way as they had been on the
> developer machine.
> I then then removed the DAO references in all the objects and found that[/color]
all[color=blue]
> the record sets were then sorted correctly again!
>
> I expected that by making an explicit reference to the DAO objects, and
> assuming that the library is referenced on the user machine, that the
> results would at least be consistent?
> Is there another factor which I might have missed? Both developer and user
> machines have up to date service packs.
>
> Since I generally develop on older versions and deploy sometimes in later
> versions of Access, its very important that I understand the factors which
> might lead to inconsistencies at deployment stage.
>
> Im sure there are threads on this topic, and if anyone has any comments,[/color]
or[color=blue]
> references to relevant threads, they would be most welcome.[/color]

[Gerry Abbott]

Re: Recordset confusion

Thanks Allen,

What I'm doing is creating a qryDef from a parameter query (which includes
my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

Then I'm moving through the recordset with the move statement.

Is there anywhere in this process where the records can become un'ordered,
and if so,
how should I go about getting it back into order. ?




example
----------------------------------------------------------------------------
-----------------
set db = CurrentDb
set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY qryDepts.deptId ")

with myQryDef
.parameter(0) = something
.parameter(1)= something else
Set myRecordSet= .OpenRecordSet
end with

with myRecordset.
.movefirst
for i =1 to .recordCount
......get the record values and do something with
them........... ...
.moveNext
next i
end with
----------------------------------------------------------------------------
-----------------



Gerry Abbott








"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> No, the order cannot be assumed to be consistent.
>
> Think of a table as a bucket. You throw records in, but they are not[/color]
ordered[color=blue]
> unless you specify the order you want to retrieve them. Things like
> compacting the database or using the table as a linked table are likely to
> change the retrieval order if you do not specify the order you want. (In
> practice, Access tends to use the primary key as the default order for
> queries, but anything can happen in reports.)
>
> So, add an AutoNumber field, or a date/time field or something that lets[/color]
you[color=blue]
> specify the order you want.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Gerry Abbott" <please@ask.i e> wrote in message
> news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...[color=green]
> > Hi all,
> >
> > I having some confusing effects with recordsets in a recent project.
> >
> > I created several recordsets, each set with the same number of records,[/color]
> and[color=green]
> > related with an index value.
> > I create a table and add the index value and a value/s from each[/color][/color]
recordset[color=blue][color=green]
> > in turn, into a temporary table, which I used to create a report. I[/color]
> created[color=green]
> > this with DAO objects, and it worked fine. I use iteration, and the
> > .movenext command to get the next values from the recordsets.
> >
> > I then deployed into a users machine with access 2002, (which had dao[/color][/color]
3.6[color=blue][color=green]
> > referenced, along with VB for applications, and Access 10 object[/color][/color]
library)[color=blue][color=green]
> > Now the results of the recordset appeared be in incorrect order, as if[/color][/color]
the[color=blue][color=green]
> > recordsets were not ordered in the same way as they had been on the
> > developer machine.
> > I then then removed the DAO references in all the objects and found that[/color]
> all[color=green]
> > the record sets were then sorted correctly again!
> >
> > I expected that by making an explicit reference to the DAO objects, and
> > assuming that the library is referenced on the user machine, that the
> > results would at least be consistent?
> > Is there another factor which I might have missed? Both developer and[/color][/color]
user[color=blue][color=green]
> > machines have up to date service packs.
> >
> > Since I generally develop on older versions and deploy sometimes in[/color][/color]
later[color=blue][color=green]
> > versions of Access, its very important that I understand the factors[/color][/color]
which[color=blue][color=green]
> > might lead to inconsistencies at deployment stage.
> >
> > Im sure there are threads on this topic, and if anyone has any comments,[/color]
> or[color=green]
> > references to relevant threads, they would be most welcome.[/color]
>
>[/color]

[Allen Browne]

Re: Recordset confusion

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <please@ask.i e> wrote in message
news:GBeGc.3921 $Z14.4954@news. indigo.ie...[color=blue]
> Thanks Allen,
>
> What I'm doing is creating a qryDef from a parameter query (which includes
> my ORDER BY index)
> Im setting my qryDef parameter values,
> Then I'm setting my recordset to the qryDef openRecordset object.
>
> Then I'm moving through the recordset with the move statement.
>
> Is there anywhere in this process where the records can become un'ordered,
> and if so,
> how should I go about getting it back into order. ?
>
>
>
>
> example
> --------------------------------------------------------------------------[/color]
--[color=blue]
> -----------------
> set db = CurrentDb
> set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY[/color]
qryDepts.deptId ")[color=blue]
>
> with myQryDef
> .parameter(0) = something
> .parameter(1)= something else
> Set myRecordSet= .OpenRecordSet
> end with
>
> with myRecordset.
> .movefirst
> for i =1 to .recordCount
> ......get the record values and do something with
> them........... ...
> .moveNext
> next i
> end with
> --------------------------------------------------------------------------[/color]
--[color=blue]
> -----------------
>
>
>
> Gerry Abbott
>
>
>
>
>
>
>
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
> > No, the order cannot be assumed to be consistent.
> >
> > Think of a table as a bucket. You throw records in, but they are not[/color]
> ordered[color=green]
> > unless you specify the order you want to retrieve them. Things like
> > compacting the database or using the table as a linked table are likely[/color][/color]
to[color=blue][color=green]
> > change the retrieval order if you do not specify the order you want. (In
> > practice, Access tends to use the primary key as the default order for
> > queries, but anything can happen in reports.)
> >
> > So, add an AutoNumber field, or a date/time field or something that lets[/color]
> you[color=green]
> > specify the order you want.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Gerry Abbott" <please@ask.i e> wrote in message
> > news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...[color=darkred]
> > > Hi all,
> > >
> > > I having some confusing effects with recordsets in a recent project.
> > >
> > > I created several recordsets, each set with the same number of[/color][/color][/color]
records,[color=blue][color=green]
> > and[color=darkred]
> > > related with an index value.
> > > I create a table and add the index value and a value/s from each[/color][/color]
> recordset[color=green][color=darkred]
> > > in turn, into a temporary table, which I used to create a report. I[/color]
> > created[color=darkred]
> > > this with DAO objects, and it worked fine. I use iteration, and the
> > > .movenext command to get the next values from the recordsets.
> > >
> > > I then deployed into a users machine with access 2002, (which had dao[/color][/color]
> 3.6[color=green][color=darkred]
> > > referenced, along with VB for applications, and Access 10 object[/color][/color]
> library)[color=green][color=darkred]
> > > Now the results of the recordset appeared be in incorrect order, as if[/color][/color]
> the[color=green][color=darkred]
> > > recordsets were not ordered in the same way as they had been on the
> > > developer machine.
> > > I then then removed the DAO references in all the objects and found[/color][/color][/color]
that[color=blue][color=green]
> > all[color=darkred]
> > > the record sets were then sorted correctly again!
> > >
> > > I expected that by making an explicit reference to the DAO objects,[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > assuming that the library is referenced on the user machine, that the
> > > results would at least be consistent?
> > > Is there another factor which I might have missed? Both developer and[/color][/color]
> user[color=green][color=darkred]
> > > machines have up to date service packs.
> > >
> > > Since I generally develop on older versions and deploy sometimes in[/color][/color]
> later[color=green][color=darkred]
> > > versions of Access, its very important that I understand the factors[/color][/color]
> which[color=green][color=darkred]
> > > might lead to inconsistencies at deployment stage.
> > >
> > > Im sure there are threads on this topic, and if anyone has any[/color][/color][/color]
comments,[color=blue][color=green]
> > or[color=darkred]
> > > references to relevant threads, they would be most welcome.[/color][/color][/color]

[Gerry Abbott]

Re: Recordset confusion

Thanks again,
The report is not a problem, since I populate a temporary table with the
records from several (ordered) recordsets, then run the report from a query
based on the table.
The important point is that the records I put into the table must all match
the same Key Index (deptId) value.


"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:40e97af9$0 $24750$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> The records will retain their order while they are open.
>
> The report is a different story.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Gerry Abbott" <please@ask.i e> wrote in message
> news:GBeGc.3921 $Z14.4954@news. indigo.ie...[color=green]
> > Thanks Allen,
> >
> > What I'm doing is creating a qryDef from a parameter query (which[/color][/color]
includes[color=blue][color=green]
> > my ORDER BY index)
> > Im setting my qryDef parameter values,
> > Then I'm setting my recordset to the qryDef openRecordset object.
> >
> > Then I'm moving through the recordset with the move statement.
> >
> > Is there anywhere in this process where the records can become[/color][/color]
un'ordered,[color=blue][color=green]
> > and if so,
> > how should I go about getting it back into order. ?
> >
> >
> >
> >
> > example[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > -----------------
> > set db = CurrentDb
> > set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY[/color]
> qryDepts.deptId ")[color=green]
> >
> > with myQryDef
> > .parameter(0) = something
> > .parameter(1)= something else
> > Set myRecordSet= .OpenRecordSet
> > end with
> >
> > with myRecordset.
> > .movefirst
> > for i =1 to .recordCount
> > ......get the record values and do something with
> > them........... ...
> > .moveNext
> > next i
> > end with[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > -----------------
> >
> >
> >
> > Gerry Abbott
> >
> >
> >
> >
> >
> >
> >
> >
> > "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> > news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
> > > No, the order cannot be assumed to be consistent.
> > >
> > > Think of a table as a bucket. You throw records in, but they are not[/color]
> > ordered[color=darkred]
> > > unless you specify the order you want to retrieve them. Things like
> > > compacting the database or using the table as a linked table are[/color][/color][/color]
likely[color=blue]
> to[color=green][color=darkred]
> > > change the retrieval order if you do not specify the order you want.[/color][/color][/color]
(In[color=blue][color=green][color=darkred]
> > > practice, Access tends to use the primary key as the default order for
> > > queries, but anything can happen in reports.)
> > >
> > > So, add an AutoNumber field, or a date/time field or something that[/color][/color][/color]
lets[color=blue][color=green]
> > you[color=darkred]
> > > specify the order you want.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "Gerry Abbott" <please@ask.i e> wrote in message
> > > news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...
> > > > Hi all,
> > > >
> > > > I having some confusing effects with recordsets in a recent project.
> > > >
> > > > I created several recordsets, each set with the same number of[/color][/color]
> records,[color=green][color=darkred]
> > > and
> > > > related with an index value.
> > > > I create a table and add the index value and a value/s from each[/color]
> > recordset[color=darkred]
> > > > in turn, into a temporary table, which I used to create a report. I
> > > created
> > > > this with DAO objects, and it worked fine. I use iteration, and the
> > > > .movenext command to get the next values from the recordsets.
> > > >
> > > > I then deployed into a users machine with access 2002, (which had[/color][/color][/color]
dao[color=blue][color=green]
> > 3.6[color=darkred]
> > > > referenced, along with VB for applications, and Access 10 object[/color]
> > library)[color=darkred]
> > > > Now the results of the recordset appeared be in incorrect order, as[/color][/color][/color]
if[color=blue][color=green]
> > the[color=darkred]
> > > > recordsets were not ordered in the same way as they had been on the
> > > > developer machine.
> > > > I then then removed the DAO references in all the objects and found[/color][/color]
> that[color=green][color=darkred]
> > > all
> > > > the record sets were then sorted correctly again!
> > > >
> > > > I expected that by making an explicit reference to the DAO objects,[/color][/color]
> and[color=green][color=darkred]
> > > > assuming that the library is referenced on the user machine, that[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > results would at least be consistent?
> > > > Is there another factor which I might have missed? Both developer[/color][/color][/color]
and[color=blue][color=green]
> > user[color=darkred]
> > > > machines have up to date service packs.
> > > >
> > > > Since I generally develop on older versions and deploy sometimes in[/color]
> > later[color=darkred]
> > > > versions of Access, its very important that I understand the factors[/color]
> > which[color=darkred]
> > > > might lead to inconsistencies at deployment stage.
> > > >
> > > > Im sure there are threads on this topic, and if anyone has any[/color][/color]
> comments,[color=green][color=darkred]
> > > or
> > > > references to relevant threads, they would be most welcome.[/color][/color]
>
>[/color]

[David W. Fenton]

Re: Recordset confusion

"Gerry Abbott" <please@ask.i e> wrote in
news:GBeGc.3921 $Z14.4954@news. indigo.ie:
[color=blue]
> What I'm doing is creating a qryDef from a parameter query (which
> includes my ORDER BY index)
> Im setting my qryDef parameter values,
> Then I'm setting my recordset to the qryDef openRecordset object.[/color]

The ORDER BY of the recordsource of a report is not necessarily
honored in the report. To be sure the report comes out in the
expected order, you must specify sorting and grouping *in* the
report -- you simply can't depend on the order of the recordsource.

I don't really know why this should be, and I've found it
frustrating myself, but it's the way things are. The order of a
report will be reliable only when you set the sorting in the report
definition. This means that it may be a waste of resources to use a
recordsource with its own ORDER BY clause.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Re: Recordset confusion

"Gerry Abbott" <please@ask.i e> wrote in
news:OlfGc.3927 $Z14.4994@news. indigo.ie:
[color=blue]
> The report is not a problem, since I populate a temporary table
> with the records from several (ordered) recordsets, then run the
> report from a query based on the table.
> The important point is that the records I put into the table must
> all match the same Key Index (deptId) value.[/color]

As I said in another post, you can never depend on the order of
printing in a report unless you've explicitly set ordering and/or
grouping options.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tom van Stiphout]

Re: Recordset confusion

On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <please@ask.i e>
wrote:

I understand you have an empty temp table. You fill some columns with
recordset 1. You then update other columns with recordset 2, other
columns with recordset 3, etc. You claim all recordsets have same
number of records and are ordered the same. Still you see mis-matches
so it appears the order was lost.

I would debug this by checking the matching key values myself.
Pseudo-code:
while not rsFrom.eof
debug.assert rsTo.DeptID = rsFrom.DeptID
' Update rsTo with values from rsFrom
rsFrom.MoveNext
rsTo.MoveNext
wend

If your code is indeed this simple, you can execute the whole thing in
SQL using an append query and multiple update queries. This would be
much faster.

-Tom.


[color=blue]
>Thanks again,
>The report is not a problem, since I populate a temporary table with the
>records from several (ordered) recordsets, then run the report from a query
>based on the table.
>The important point is that the records I put into the table must all match
>the same Key Index (deptId) value.
>
>
>"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>news:40e97af9$ 0$24750$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> The records will retain their order while they are open.
>>
>> The report is a different story.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Gerry Abbott" <please@ask.i e> wrote in message
>> news:GBeGc.3921 $Z14.4954@news. indigo.ie...[color=darkred]
>> > Thanks Allen,
>> >
>> > What I'm doing is creating a qryDef from a parameter query (which[/color][/color]
>includes[color=green][color=darkred]
>> > my ORDER BY index)
>> > Im setting my qryDef parameter values,
>> > Then I'm setting my recordset to the qryDef openRecordset object.
>> >
>> > Then I'm moving through the recordset with the move statement.
>> >
>> > Is there anywhere in this process where the records can become[/color][/color]
>un'ordered,[color=green][color=darkred]
>> > and if so,
>> > how should I go about getting it back into order. ?
>> >
>> >
>> >
>> >
>> > example[/color]
>>
>> --------------------------------------------------------------------------
>> --[color=darkred]
>> > -----------------
>> > set db = CurrentDb
>> > set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY[/color]
>> qryDepts.deptId ")[color=darkred]
>> >
>> > with myQryDef
>> > .parameter(0) = something
>> > .parameter(1)= something else
>> > Set myRecordSet= .OpenRecordSet
>> > end with
>> >
>> > with myRecordset.
>> > .movefirst
>> > for i =1 to .recordCount
>> > ......get the record values and do something with
>> > them........... ...
>> > .moveNext
>> > next i
>> > end with[/color]
>>
>> --------------------------------------------------------------------------
>> --[color=darkred]
>> > -----------------
>> >
>> >
>> >
>> > Gerry Abbott
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>> > news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>> > > No, the order cannot be assumed to be consistent.
>> > >
>> > > Think of a table as a bucket. You throw records in, but they are not
>> > ordered
>> > > unless you specify the order you want to retrieve them. Things like
>> > > compacting the database or using the table as a linked table are[/color][/color]
>likely[color=green]
>> to[color=darkred]
>> > > change the retrieval order if you do not specify the order you want.[/color][/color]
>(In[color=green][color=darkred]
>> > > practice, Access tends to use the primary key as the default order for
>> > > queries, but anything can happen in reports.)
>> > >
>> > > So, add an AutoNumber field, or a date/time field or something that[/color][/color]
>lets[color=green][color=darkred]
>> > you
>> > > specify the order you want.
>> > >
>> > > --
>> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
>> > > Tips for Access users - http://allenbrowne.com/tips.html
>> > > Reply to group, rather than allenbrowne at mvps dot org.
>> > >
>> > > "Gerry Abbott" <please@ask.i e> wrote in message
>> > > news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...
>> > > > Hi all,
>> > > >
>> > > > I having some confusing effects with recordsets in a recent project.
>> > > >
>> > > > I created several recordsets, each set with the same number of[/color]
>> records,[color=darkred]
>> > > and
>> > > > related with an index value.
>> > > > I create a table and add the index value and a value/s from each
>> > recordset
>> > > > in turn, into a temporary table, which I used to create a report. I
>> > > created
>> > > > this with DAO objects, and it worked fine. I use iteration, and the
>> > > > .movenext command to get the next values from the recordsets.
>> > > >
>> > > > I then deployed into a users machine with access 2002, (which had[/color][/color]
>dao[color=green][color=darkred]
>> > 3.6
>> > > > referenced, along with VB for applications, and Access 10 object
>> > library)
>> > > > Now the results of the recordset appeared be in incorrect order, as[/color][/color]
>if[color=green][color=darkred]
>> > the
>> > > > recordsets were not ordered in the same way as they had been on the
>> > > > developer machine.
>> > > > I then then removed the DAO references in all the objects and found[/color]
>> that[color=darkred]
>> > > all
>> > > > the record sets were then sorted correctly again!
>> > > >
>> > > > I expected that by making an explicit reference to the DAO objects,[/color]
>> and[color=darkred]
>> > > > assuming that the library is referenced on the user machine, that[/color][/color]
>the[color=green][color=darkred]
>> > > > results would at least be consistent?
>> > > > Is there another factor which I might have missed? Both developer[/color][/color]
>and[color=green][color=darkred]
>> > user
>> > > > machines have up to date service packs.
>> > > >
>> > > > Since I generally develop on older versions and deploy sometimes in
>> > later
>> > > > versions of Access, its very important that I understand the factors
>> > which
>> > > > might lead to inconsistencies at deployment stage.
>> > > >
>> > > > Im sure there are threads on this topic, and if anyone has any[/color]
>> comments,[color=darkred]
>> > > or
>> > > > references to relevant threads, they would be most welcome.[/color]
>>
>>[/color]
>[/color]

[Gerry Abbott]

Re: Recordset confusion

Thanks Tom,
Yes you understand what i'm doing precisely. Same number of records etc
(only 13) . I actually have a number of recordsets open concurrently, and I
populate the table one record at a time (see code below. What I do is a
little more complicated, since I run the code a second time, with different
parameter values, to get year to date figures, which I use to populate a
further set of fields in the same set of records, using the 'edit' method in
place of the addnew )

I have a basic checking code, but this is not fool proof. (your suggested
debug.assert method does not exist in my Acc97)

I have not used append queries before, but what you suggest does sound a lot
simpler. I assumed that append queries can only add records, but not change
fields in existing records, which is what I want to do!

You might help me out here.


Gerry


Sample existing code
-------------------------------------
For i = 1 To rsOpen.RecordCo unt
SumDeptId = rsOpen!deptId + rsClosed!deptId + rsPast!deptId
'CHECKS FOR
If Int(SumDeptId / 3) <> SumDeptId / 3 Then
MsgBox "Records are not being calculated correctly"
Exit Sub
End If
.AddNew
!deptId = rsOpen!deptId
!Open = rsOpen!Open
!Closed = rsClosed!Closed
!Past = rsPast!Past
!Days = rsClosed!totald ays
.Update
rsOpen.MoveNext
rsClosed.MoveNe xt
rsPast.MoveNext
Next i
---------------------------------------




"Tom van Stiphout" <no.spam.tom774 4@cox.net> wrote in message
news:f24je01vo3 gptmkjihok34825 c0geontgd@4ax.c om...[color=blue]
> On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <please@ask.i e>
> wrote:
>
> I understand you have an empty temp table. You fill some columns with
> recordset 1. You then update other columns with recordset 2, other
> columns with recordset 3, etc. You claim all recordsets have same
> number of records and are ordered the same. Still you see mis-matches
> so it appears the order was lost.
>
> I would debug this by checking the matching key values myself.
> Pseudo-code:
> while not rsFrom.eof
> debug.assert rsTo.DeptID = rsFrom.DeptID
> ' Update rsTo with values from rsFrom
> rsFrom.MoveNext
> rsTo.MoveNext
> wend
>
> If your code is indeed this simple, you can execute the whole thing in
> SQL using an append query and multiple update queries. This would be
> much faster.
>
> -Tom.
>
>
>[color=green]
> >Thanks again,
> >The report is not a problem, since I populate a temporary table with the
> >records from several (ordered) recordsets, then run the report from a[/color][/color]
query[color=blue][color=green]
> >based on the table.
> >The important point is that the records I put into the table must all[/color][/color]
match[color=blue][color=green]
> >the same Key Index (deptId) value.
> >
> >
> >"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> >news:40e97af9$ 0$24750$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
> >> The records will retain their order while they are open.
> >>
> >> The report is a different story.
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Gerry Abbott" <please@ask.i e> wrote in message
> >> news:GBeGc.3921 $Z14.4954@news. indigo.ie...
> >> > Thanks Allen,
> >> >
> >> > What I'm doing is creating a qryDef from a parameter query (which[/color]
> >includes[color=darkred]
> >> > my ORDER BY index)
> >> > Im setting my qryDef parameter values,
> >> > Then I'm setting my recordset to the qryDef openRecordset object.
> >> >
> >> > Then I'm moving through the recordset with the move statement.
> >> >
> >> > Is there anywhere in this process where the records can become[/color]
> >un'ordered,[color=darkred]
> >> > and if so,
> >> > how should I go about getting it back into order. ?
> >> >
> >> >
> >> >
> >> >
> >> > example
> >>[/color][/color]
>[color=green]
>> -------------------------------------------------------------------------[/color][/color]
-[color=blue][color=green][color=darkred]
> >> --
> >> > -----------------
> >> > set db = CurrentDb
> >> > set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY
> >> qryDepts.deptId ")
> >> >
> >> > with myQryDef
> >> > .parameter(0) = something
> >> > .parameter(1)= something else
> >> > Set myRecordSet= .OpenRecordSet
> >> > end with
> >> >
> >> > with myRecordset.
> >> > .movefirst
> >> > for i =1 to .recordCount
> >> > ......get the record values and do something with
> >> > them........... ...
> >> > .moveNext
> >> > next i
> >> > end with
> >>[/color][/color]
>[color=green]
>> -------------------------------------------------------------------------[/color][/color]
-[color=blue][color=green][color=darkred]
> >> --
> >> > -----------------
> >> >
> >> >
> >> >
> >> > Gerry Abbott
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> >> > news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
> >> > > No, the order cannot be assumed to be consistent.
> >> > >
> >> > > Think of a table as a bucket. You throw records in, but they are[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> >> > ordered
> >> > > unless you specify the order you want to retrieve them. Things like
> >> > > compacting the database or using the table as a linked table are[/color]
> >likely[color=darkred]
> >> to
> >> > > change the retrieval order if you do not specify the order you[/color][/color][/color]
want.[color=blue][color=green]
> >(In[color=darkred]
> >> > > practice, Access tends to use the primary key as the default order[/color][/color][/color]
for[color=blue][color=green][color=darkred]
> >> > > queries, but anything can happen in reports.)
> >> > >
> >> > > So, add an AutoNumber field, or a date/time field or something that[/color]
> >lets[color=darkred]
> >> > you
> >> > > specify the order you want.
> >> > >
> >> > > --
> >> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> > > Tips for Access users - http://allenbrowne.com/tips.html
> >> > > Reply to group, rather than allenbrowne at mvps dot org.
> >> > >
> >> > > "Gerry Abbott" <please@ask.i e> wrote in message
> >> > > news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...
> >> > > > Hi all,
> >> > > >
> >> > > > I having some confusing effects with recordsets in a recent[/color][/color][/color]
project.[color=blue][color=green][color=darkred]
> >> > > >
> >> > > > I created several recordsets, each set with the same number of
> >> records,
> >> > > and
> >> > > > related with an index value.
> >> > > > I create a table and add the index value and a value/s from each
> >> > recordset
> >> > > > in turn, into a temporary table, which I used to create a report.[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> >> > > created
> >> > > > this with DAO objects, and it worked fine. I use iteration, and[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> > > > .movenext command to get the next values from the recordsets.
> >> > > >
> >> > > > I then deployed into a users machine with access 2002, (which had[/color]
> >dao[color=darkred]
> >> > 3.6
> >> > > > referenced, along with VB for applications, and Access 10 object
> >> > library)
> >> > > > Now the results of the recordset appeared be in incorrect order,[/color][/color][/color]
as[color=blue][color=green]
> >if[color=darkred]
> >> > the
> >> > > > recordsets were not ordered in the same way as they had been on[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> > > > developer machine.
> >> > > > I then then removed the DAO references in all the objects and[/color][/color][/color]
found[color=blue][color=green][color=darkred]
> >> that
> >> > > all
> >> > > > the record sets were then sorted correctly again!
> >> > > >
> >> > > > I expected that by making an explicit reference to the DAO[/color][/color][/color]
objects,[color=blue][color=green][color=darkred]
> >> and
> >> > > > assuming that the library is referenced on the user machine, that[/color]
> >the[color=darkred]
> >> > > > results would at least be consistent?
> >> > > > Is there another factor which I might have missed? Both developer[/color]
> >and[color=darkred]
> >> > user
> >> > > > machines have up to date service packs.
> >> > > >
> >> > > > Since I generally develop on older versions and deploy sometimes[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> >> > later
> >> > > > versions of Access, its very important that I understand the[/color][/color][/color]
factors[color=blue][color=green][color=darkred]
> >> > which
> >> > > > might lead to inconsistencies at deployment stage.
> >> > > >
> >> > > > Im sure there are threads on this topic, and if anyone has any
> >> comments,
> >> > > or
> >> > > > references to relevant threads, they would be most welcome.
> >>
> >>[/color]
> >[/color]
>[/color]

[Gerry Abbott]

Re: Recordset confusion

Thanks David,
Once i've got the records in the temporary table correct, the order on the
printed report is trivial.

Gerry

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns951D87A 7ADC31dfentonbw aynetinvali@24. 168.128.86...[color=blue]
> "Gerry Abbott" <please@ask.i e> wrote in
> news:OlfGc.3927 $Z14.4994@news. indigo.ie:
>[color=green]
> > The report is not a problem, since I populate a temporary table
> > with the records from several (ordered) recordsets, then run the
> > report from a query based on the table.
> > The important point is that the records I put into the table must
> > all match the same Key Index (deptId) value.[/color]
>
> As I said in another post, you can never depend on the order of
> printing in a report unless you've explicitly set ordering and/or
> grouping options.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

[David W. Fenton]

Re: Recordset confusion

"Gerry Abbott" <please@ask.i e> wrote in
news:XjkGc.3963 $Z14.4996@news. indigo.ie:
[color=blue]
> I actually have a number of recordsets open concurrently. . .[/color]

What, exactly, are you doing that can't be done without a temp table
by using SQL?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Gerry Abbott]

Re: Recordset confusion

Sorry David, I just replied to your last previous post, and should serve as
a reply here.

Regards,

Gerry



"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns951D878 96FDC2dfentonbw aynetinvali@24. 168.128.86...[color=blue]
> "Gerry Abbott" <please@ask.i e> wrote in
> news:GBeGc.3921 $Z14.4954@news. indigo.ie:
>[color=green]
> > What I'm doing is creating a qryDef from a parameter query (which
> > includes my ORDER BY index)
> > Im setting my qryDef parameter values,
> > Then I'm setting my recordset to the qryDef openRecordset object.[/color]
>
> The ORDER BY of the recordsource of a report is not necessarily
> honored in the report. To be sure the report comes out in the
> expected order, you must specify sorting and grouping *in* the
> report -- you simply can't depend on the order of the recordsource.
>
> I don't really know why this should be, and I've found it
> frustrating myself, but it's the way things are. The order of a
> report will be reliable only when you set the sorting in the report
> definition. This means that it may be a waste of resources to use a
> recordsource with its own ORDER BY clause.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

[Tom van Stiphout]

Re: Recordset confusion

On Mon, 5 Jul 2004 23:08:55 +0100, "Gerry Abbott" <please@ask.i e>
wrote:

Rather than debug.assert you can use:
if rsTo.DeptID <> rsFrom.DeptID then Msgbox "Very bad condition
encountered!"

To work with queries, this pseudo-code would work:
RunQuery "delete * from mytemptable" ' clear out temp table.
RunQuery "insert into mytemptable select DeptID, Field2, Field3 from
Table1" ' insert rows, and first few columns.
RunQuery "update mytemptable inner join Table2 on
Table2.DeptID=m ytemptable.Dept ID set Field4=Table2.F ield2,
Field5=Table2.F ield3" ' update some other columns

If you have a simple situation, you can create a query that pulls all
columns for the temp table, and add the data with one INSERT query:
myquery looks like this:
select Table1.DeptID, Table1.Field2, Table1.Field3, Table2.Field4,
Table2.Field5 from Table1 inner join Table2 on
Table1.DeptID=T able2.DeptID
and the insert query:
insert into mytemptable select * from myquery

David's point is well taken: if things are that simple, you don't need
a temp table:
myreport.record source = myquery

Typically the temp table route is only taken when it's just too
complicated to create a single query to pull the data.

-Tom.

[color=blue]
>Thanks Tom,
>Yes you understand what i'm doing precisely. Same number of records etc
>(only 13) . I actually have a number of recordsets open concurrently, and I
>populate the table one record at a time (see code below. What I do is a
>little more complicated, since I run the code a second time, with different
>parameter values, to get year to date figures, which I use to populate a
>further set of fields in the same set of records, using the 'edit' method in
>place of the addnew )
>
>I have a basic checking code, but this is not fool proof. (your suggested
>debug.assert method does not exist in my Acc97)
>
>I have not used append queries before, but what you suggest does sound a lot
>simpler. I assumed that append queries can only add records, but not change
>fields in existing records, which is what I want to do!
>
>You might help me out here.
>
>
>Gerry
>
>
>Sample existing code
>-------------------------------------
>For i = 1 To rsOpen.RecordCo unt
> SumDeptId = rsOpen!deptId + rsClosed!deptId + rsPast!deptId
> 'CHECKS FOR
> If Int(SumDeptId / 3) <> SumDeptId / 3 Then
> MsgBox "Records are not being calculated correctly"
> Exit Sub
> End If
> .AddNew
> !deptId = rsOpen!deptId
> !Open = rsOpen!Open
> !Closed = rsClosed!Closed
> !Past = rsPast!Past
> !Days = rsClosed!totald ays
> .Update
> rsOpen.MoveNext
> rsClosed.MoveNe xt
> rsPast.MoveNext
> Next i
>---------------------------------------
>
>
>
>
>"Tom van Stiphout" <no.spam.tom774 4@cox.net> wrote in message
>news:f24je01vo 3gptmkjihok3482 5c0geontgd@4ax. com...[color=green]
>> On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <please@ask.i e>
>> wrote:
>>
>> I understand you have an empty temp table. You fill some columns with
>> recordset 1. You then update other columns with recordset 2, other
>> columns with recordset 3, etc. You claim all recordsets have same
>> number of records and are ordered the same. Still you see mis-matches
>> so it appears the order was lost.
>>
>> I would debug this by checking the matching key values myself.
>> Pseudo-code:
>> while not rsFrom.eof
>> debug.assert rsTo.DeptID = rsFrom.DeptID
>> ' Update rsTo with values from rsFrom
>> rsFrom.MoveNext
>> rsTo.MoveNext
>> wend
>>
>> If your code is indeed this simple, you can execute the whole thing in
>> SQL using an append query and multiple update queries. This would be
>> much faster.
>>
>> -Tom.
>>
>>
>>[color=darkred]
>> >Thanks again,
>> >The report is not a problem, since I populate a temporary table with the
>> >records from several (ordered) recordsets, then run the report from a[/color][/color]
>query[color=green][color=darkred]
>> >based on the table.
>> >The important point is that the records I put into the table must all[/color][/color]
>match[color=green][color=darkred]
>> >the same Key Index (deptId) value.
>> >
>> >
>> >"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>> >news:40e97af9$ 0$24750$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au ...
>> >> The records will retain their order while they are open.
>> >>
>> >> The report is a different story.
>> >>
>> >> --
>> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Gerry Abbott" <please@ask.i e> wrote in message
>> >> news:GBeGc.3921 $Z14.4954@news. indigo.ie...
>> >> > Thanks Allen,
>> >> >
>> >> > What I'm doing is creating a qryDef from a parameter query (which
>> >includes
>> >> > my ORDER BY index)
>> >> > Im setting my qryDef parameter values,
>> >> > Then I'm setting my recordset to the qryDef openRecordset object.
>> >> >
>> >> > Then I'm moving through the recordset with the move statement.
>> >> >
>> >> > Is there anywhere in this process where the records can become
>> >un'ordered,
>> >> > and if so,
>> >> > how should I go about getting it back into order. ?
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > example
>> >>[/color]
>>[color=darkred]
>>> -------------------------------------------------------------------------[/color][/color]
>-[color=green][color=darkred]
>> >> --
>> >> > -----------------
>> >> > set db = CurrentDb
>> >> > set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY
>> >> qryDepts.deptId ")
>> >> >
>> >> > with myQryDef
>> >> > .parameter(0) = something
>> >> > .parameter(1)= something else
>> >> > Set myRecordSet= .OpenRecordSet
>> >> > end with
>> >> >
>> >> > with myRecordset.
>> >> > .movefirst
>> >> > for i =1 to .recordCount
>> >> > ......get the record values and do something with
>> >> > them........... ...
>> >> > .moveNext
>> >> > next i
>> >> > end with
>> >>[/color]
>>[color=darkred]
>>> -------------------------------------------------------------------------[/color][/color]
>-[color=green][color=darkred]
>> >> --
>> >> > -----------------
>> >> >
>> >> >
>> >> >
>> >> > Gerry Abbott
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>> >> > news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>> >> > > No, the order cannot be assumed to be consistent.
>> >> > >
>> >> > > Think of a table as a bucket. You throw records in, but they are[/color][/color]
>not[color=green][color=darkred]
>> >> > ordered
>> >> > > unless you specify the order you want to retrieve them. Things like
>> >> > > compacting the database or using the table as a linked table are
>> >likely
>> >> to
>> >> > > change the retrieval order if you do not specify the order you[/color][/color]
>want.[color=green][color=darkred]
>> >(In
>> >> > > practice, Access tends to use the primary key as the default order[/color][/color]
>for[color=green][color=darkred]
>> >> > > queries, but anything can happen in reports.)
>> >> > >
>> >> > > So, add an AutoNumber field, or a date/time field or something that
>> >lets
>> >> > you
>> >> > > specify the order you want.
>> >> > >
>> >> > > --
>> >> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
>> >> > > Tips for Access users - http://allenbrowne.com/tips.html
>> >> > > Reply to group, rather than allenbrowne at mvps dot org.
>> >> > >
>> >> > > "Gerry Abbott" <please@ask.i e> wrote in message
>> >> > > news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...
>> >> > > > Hi all,
>> >> > > >
>> >> > > > I having some confusing effects with recordsets in a recent[/color][/color]
>project.[color=green][color=darkred]
>> >> > > >
>> >> > > > I created several recordsets, each set with the same number of
>> >> records,
>> >> > > and
>> >> > > > related with an index value.
>> >> > > > I create a table and add the index value and a value/s from each
>> >> > recordset
>> >> > > > in turn, into a temporary table, which I used to create a report.[/color][/color]
>I[color=green][color=darkred]
>> >> > > created
>> >> > > > this with DAO objects, and it worked fine. I use iteration, and[/color][/color]
>the[color=green][color=darkred]
>> >> > > > .movenext command to get the next values from the recordsets.
>> >> > > >
>> >> > > > I then deployed into a users machine with access 2002, (which had
>> >dao
>> >> > 3.6
>> >> > > > referenced, along with VB for applications, and Access 10 object
>> >> > library)
>> >> > > > Now the results of the recordset appeared be in incorrect order,[/color][/color]
>as[color=green][color=darkred]
>> >if
>> >> > the
>> >> > > > recordsets were not ordered in the same way as they had been on[/color][/color]
>the[color=green][color=darkred]
>> >> > > > developer machine.
>> >> > > > I then then removed the DAO references in all the objects and[/color][/color]
>found[color=green][color=darkred]
>> >> that
>> >> > > all
>> >> > > > the record sets were then sorted correctly again!
>> >> > > >
>> >> > > > I expected that by making an explicit reference to the DAO[/color][/color]
>objects,[color=green][color=darkred]
>> >> and
>> >> > > > assuming that the library is referenced on the user machine, that
>> >the
>> >> > > > results would at least be consistent?
>> >> > > > Is there another factor which I might have missed? Both developer
>> >and
>> >> > user
>> >> > > > machines have up to date service packs.
>> >> > > >
>> >> > > > Since I generally develop on older versions and deploy sometimes[/color][/color]
>in[color=green][color=darkred]
>> >> > later
>> >> > > > versions of Access, its very important that I understand the[/color][/color]
>factors[color=green][color=darkred]
>> >> > which
>> >> > > > might lead to inconsistencies at deployment stage.
>> >> > > >
>> >> > > > Im sure there are threads on this topic, and if anyone has any
>> >> comments,
>> >> > > or
>> >> > > > references to relevant threads, they would be most welcome.
>> >>
>> >>
>> >[/color]
>>[/color]
>[/color]

[Gerry Abbott]

Re: Recordset confusion

David,
I diden't say that what i'm doing cannot be done with sql. Its just not the
way i've done it
My reply to Tom VanStiphout, is a reasonable description of what i'm doing.


Gerry Abbott


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns951DB96 4BE9E4dfentonbw aynetinvali@24. 168.128.86...[color=blue]
> "Gerry Abbott" <please@ask.i e> wrote in
> news:XjkGc.3963 $Z14.4996@news. indigo.ie:
>[color=green]
> > I actually have a number of recordsets open concurrently. . .[/color]
>
> What, exactly, are you doing that can't be done without a temp table
> by using SQL?
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

[Allen Browne]

Re: Recordset confusion

Okay: you populate a temp table by using AddNew on several different
recordsets. All records in the temp table have the same deptId value,
presumably some kind of foreign key.

You then close the orignal recordsets(?), and open another one based on a
parameter query. If this one sorts by deptId (the same for all values), then
you have not specified any sort order for this recordset.

You talk about setting an index as well. I think you can only specify an
Index for a dbOpenTable type recordset, so that should not be possible for
the dbOpenDynaset type recordset you get when you OpenRecordset using a
QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Gerry Abbott" <please@ask.i e> wrote in message
news:OlfGc.3927 $Z14.4994@news. indigo.ie...[color=blue]
> Thanks again,
> The report is not a problem, since I populate a temporary table with the
> records from several (ordered) recordsets, then run the report from a[/color]
query[color=blue]
> based on the table.
> The important point is that the records I put into the table must all[/color]
match[color=blue]
> the same Key Index (deptId) value.
>
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:40e97af9$0 $24750$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
> > The records will retain their order while they are open.
> >
> > The report is a different story.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Gerry Abbott" <please@ask.i e> wrote in message
> > news:GBeGc.3921 $Z14.4954@news. indigo.ie...[color=darkred]
> > > Thanks Allen,
> > >
> > > What I'm doing is creating a qryDef from a parameter query (which[/color][/color]
> includes[color=green][color=darkred]
> > > my ORDER BY index)
> > > Im setting my qryDef parameter values,
> > > Then I'm setting my recordset to the qryDef openRecordset object.
> > >
> > > Then I'm moving through the recordset with the move statement.
> > >
> > > Is there anywhere in this process where the records can become[/color][/color]
> un'ordered,[color=green][color=darkred]
> > > and if so,
> > > how should I go about getting it back into order. ?
> > >
> > >
> > >
> > >
> > > example[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > --[color=darkred]
> > > -----------------
> > > set db = CurrentDb
> > > set myQryDef = db.qryDefs("SEL ECT * FROM qryDepts ORDER BY[/color]
> > qryDepts.deptId ")[color=darkred]
> > >
> > > with myQryDef
> > > .parameter(0) = something
> > > .parameter(1)= something else
> > > Set myRecordSet= .OpenRecordSet
> > > end with
> > >
> > > with myRecordset.
> > > .movefirst
> > > for i =1 to .recordCount
> > > ......get the record values and do something with
> > > them........... ...
> > > .moveNext
> > > next i
> > > end with[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > --[color=darkred]
> > > -----------------
> > >
> > >
> > >
> > > Gerry Abbott
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> > > news:40e8bb93$0 $24755$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
> > > > No, the order cannot be assumed to be consistent.
> > > >
> > > > Think of a table as a bucket. You throw records in, but they are not
> > > ordered
> > > > unless you specify the order you want to retrieve them. Things like
> > > > compacting the database or using the table as a linked table are[/color][/color]
> likely[color=green]
> > to[color=darkred]
> > > > change the retrieval order if you do not specify the order you want.[/color][/color]
> (In[color=green][color=darkred]
> > > > practice, Access tends to use the primary key as the default order[/color][/color][/color]
for[color=blue][color=green][color=darkred]
> > > > queries, but anything can happen in reports.)
> > > >
> > > > So, add an AutoNumber field, or a date/time field or something that[/color][/color]
> lets[color=green][color=darkred]
> > > you
> > > > specify the order you want.
> > > >
> > > > --
> > > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > > Tips for Access users - http://allenbrowne.com/tips.html
> > > > Reply to group, rather than allenbrowne at mvps dot org.
> > > >
> > > > "Gerry Abbott" <please@ask.i e> wrote in message
> > > > news:Ft_Fc.3853 $Z14.4937@news. indigo.ie...
> > > > > Hi all,
> > > > >
> > > > > I having some confusing effects with recordsets in a recent[/color][/color][/color]
project.[color=blue][color=green][color=darkred]
> > > > >
> > > > > I created several recordsets, each set with the same number of[/color]
> > records,[color=darkred]
> > > > and
> > > > > related with an index value.
> > > > > I create a table and add the index value and a value/s from each
> > > recordset
> > > > > in turn, into a temporary table, which I used to create a report.[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > > created
> > > > > this with DAO objects, and it worked fine. I use iteration, and[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > .movenext command to get the next values from the recordsets.
> > > > >
> > > > > I then deployed into a users machine with access 2002, (which had[/color][/color]
> dao[color=green][color=darkred]
> > > 3.6
> > > > > referenced, along with VB for applications, and Access 10 object
> > > library)
> > > > > Now the results of the recordset appeared be in incorrect order,[/color][/color][/color]
as[color=blue]
> if[color=green][color=darkred]
> > > the
> > > > > recordsets were not ordered in the same way as they had been on[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > developer machine.
> > > > > I then then removed the DAO references in all the objects and[/color][/color][/color]
found[color=blue][color=green]
> > that[color=darkred]
> > > > all
> > > > > the record sets were then sorted correctly again!
> > > > >
> > > > > I expected that by making an explicit reference to the DAO[/color][/color][/color]
objects,[color=blue][color=green]
> > and[color=darkred]
> > > > > assuming that the library is referenced on the user machine, that[/color][/color]
> the[color=green][color=darkred]
> > > > > results would at least be consistent?
> > > > > Is there another factor which I might have missed? Both developer[/color][/color]
> and[color=green][color=darkred]
> > > user
> > > > > machines have up to date service packs.
> > > > >
> > > > > Since I generally develop on older versions and deploy sometimes[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > later
> > > > > versions of Access, its very important that I understand the[/color][/color][/color]
factors[color=blue][color=green][color=darkred]
> > > which
> > > > > might lead to inconsistencies at deployment stage.
> > > > >
> > > > > Im sure there are threads on this topic, and if anyone has any[/color]
> > comments,[color=darkred]
> > > > or
> > > > > references to relevant threads, they would be most welcome.[/color][/color][/color]