Recordset's RowID as part of Recordset

Can someone please provide some direction...

Using ADO against Acccess 2000

I need the Recordset's rowID to be part of the recordset itself so that
I may use it as an ORDER BY subquery in an INNER JOIN.

I would think it would be something like this...
SQLStmnt = "SELECT CustomerMarket.AccountNumber, RowID FROM
CustomerMarket WHERE MarketName = ""FV"" ORDER BY ""QuantityTraded"""

Where RowID is some sort of recordset property or dB engin query.

Any ideas????

Thanks!!!

Trip

What do you mean by RowID? If it is not part of your database in Access it is
not part of the recordset, and there is no recordset property like a RowID.
If you mean a sequential number in the order that the records are entered in
the table: if you do not specify any other order the recordset will come in
the order of database entry. You could potentially disconnect the recordset
from the source data (close the connection) and add a field, iterate through
the recordset and populate the new field with the record number, then use
this as your subquery recordset - but that is pretty cumbersome.

The best solution is to add a RowID that meets your specs within the Access
database, if that is possible. Or, if you don't have the ability to modify
the database, link the table into a new Access db and use a query to create a
RowID.
--
- K Dales


"Trip" wrote:

> Can someone please provide some direction...
>
> Using ADO against Acccess 2000
>
> I need the Recordset's rowID to be part of the recordset itself so that
> I may use it as an ORDER BY subquery in an INNER JOIN.
>
> I would think it would be something like this...
> SQLStmnt = "SELECT CustomerMarket.AccountNumber, RowID FROM
> CustomerMarket WHERE MarketName = ""FV"" ORDER BY ""QuantityTraded"""
>
> Where RowID is some sort of recordset property or dB engin query.
>
> Any ideas????
>
> Thanks!!!
>
> Trip
>
>

AFAIK, Access data provider doesn't support RowID. I believe that Oracle
does, and Sybase has RowNum, but no luck with Access.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Trip" <[email protected]> wrote in message
news:[email protected]...
> Can someone please provide some direction...
>
> Using ADO against Acccess 2000
>
> I need the Recordset's rowID to be part of the recordset itself so that
> I may use it as an ORDER BY subquery in an INNER JOIN.
>
> I would think it would be something like this...
> SQLStmnt = "SELECT CustomerMarket.AccountNumber, RowID FROM
> CustomerMarket WHERE MarketName = ""FV"" ORDER BY ""QuantityTraded"""
>
> Where RowID is some sort of recordset property or dB engin query.
>
> Any ideas????
>
> Thanks!!!
>
> Trip
>

Thanks Guys!

Bob, you've hit it. I finally created a temp table with an auto number
as an intermediate step and then used the auto number as a sort
sequence in an inner join. Even though it's a bit clugey it works
well.

Thanks again!

Trip