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
Mike Brind wrote:
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
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
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.
Mike Brind wrote:
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11*********************@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
