classic asp - returning records from access in a random order

Quote:

Quote:

Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function
>
Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);
>
When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand
>

Quote:

"InnoCreate" <James@InnoCreate.comwrote in message
news:1161620424.791479.37650@m7g2000cwm.googlegrou ps.com...
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);

When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand

>
If you can get the result you want from running that statement within
Access, then save it as a query (eg qryRandomOrder) and call that using
ADO.
>
Set rs = Server.CreateObject("ADODB.Recordset")
conn.qryRandomOrder rs
>
See if that works.
>
--
Mike Brind