classic asp - returning records from access in a random order

[InnoCreate]

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

 

[Mike Brind]

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.

 

[InnoCreate]

Hi mike thanks for the suggestion but unfortunatly i need to be able to
query the access query using an sql statement as the SQL parameters
could change with each query and there are an infinite number of
possibilities so i can't just save them as multiple access queries etc.

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]

I'm confused. Where do parameters fit in your original SQL? There's no
"Where" clause? And you don't need to save mutliple hard-coded queries. If
you are using parameters, you can create a saved parameter query.

--
Mike Brind

Hi mike thanks for the suggestion but unfortunatly i need to be able to
query the access query using an sql statement as the SQL parameters
could change with each query and there are an infinite number of
possibilities so i can't just save them as multiple access queries etc.

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.

 

[InnoCreate]

Sorry in the above query there are no parameters - this is the access
query SQL. I am however then using an sql statement which is
dynamically created to query the MS Query.
It's the dynamic sql statement which is changing everytime.
For example "SELECT * FROM access_query1 WHERE acol = ? AND bcol = ?"
Etc etc
I know this is a bit of a long winded way of doing things but i tried
to run the access query SQL with the matching WHERE clause and the
ADODB just returned an error saying invalid function. Persumably access
doesnt allow the ADODB object to access it's custom functions.
I think i might have to use Jons suggestion. It's not really how i want
to do it though and will mean changing a ton of code.
Regards

I'm confused. Where do parameters fit in your original SQL? There's no
"Where" clause? And you don't need to save mutliple hard-coded queries. If
you are using parameters, you can create a saved parameter query.

--
Mike Brind

Hi mike thanks for the suggestion but unfortunatly i need to be able to
query the access query using an sql statement as the SQL parameters
could change with each query and there are an infinite number of
possibilities so i can't just save them as multiple access queries etc.

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.

 

[Bob Barrows [MVP]]

Sorry in the above query there are no parameters - this is the access
query SQL. I am however then using an sql statement which is
dynamically created to query the MS Query.
It's the dynamic sql statement which is changing everytime.
For example "SELECT * FROM access_query1 WHERE acol = ? AND bcol = ?"

See those ?s - those are parameters. Take a look at this:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Etc etc
I know this is a bit of a long winded way of doing things but i tried
to run the access query SQL with the matching WHERE clause and the
ADODB just returned an error saying invalid function. Persumably
access doesnt allow the ADODB object to access it's custom functions.
I think i might have to use Jons suggestion. It's not really how i
want to do it though and will mean changing a ton of code.

See these as well if you wish to use saved parameter queries:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]