Random Query with Access

[UpstateWeb]

I am using MS Access and since upgrading to MX6.1 "Order BY Rnd(RecordID)" no longer works in the query. I have searched and tried everything I have found with no success. I realize this is a an issue with the new drivers that area assigned through the CF Admin interface. I have tried the jodbc technique instead of using the default MS Access with Unicode, but my websites fail whenever I do so I have to switch back.

I have tried the various coding solutions and the tags. Nothing works. It used to be so simple. Why someone at Macromedia/Adobe will not put out a simple patch to fix this I do not know. They even know it's an issue. Can anyone tell me what to do. This is very frustrating. Moving to another database platform is not a solution either.

I need to simply output 3 to 5 random records from a query and what used to be very easy has been a thorn in my side for over a year now. Crazy....

Thanks in advance to anyone who can help....

David G. Moore, Jr.
UpstateWeb LLC

[hlevin]

How big is the recordset if you were to retrieve all rows and not just random 3 to 5? If it's relatively small, retreive them all and choose 3 to 5 at random in coldfusion.

[UpstateWeb]

It could be 3 or 300o records. The number is unknown to me. I need to pick a random amount from an unknown amount. I am using this in association with a Content Management Solution.

Let's say there are 50 FAQ in the database and we want to display 3 of them randomly on the front page. It used to be very easy. The following query worked with MS Access and Cold Fusion 4.0 to 5.0:

<cfquery name="getFAQs" datasource="#DSN#" maxrows=3>
SELECT *
FROM FAQs
WHERE Active = yes
ORDER BY Rnd(FAQid)
</cfquery>

Ever since 6.1 it does not. All I get from Macromedia/Adobe is it's the ODBC drivers that were supplied to them are the issue and I need to use the 'other' setting when setting up the database mapping. I have followed their directions exactly with no success. I think that is a lame excuse. The same reasoning is used for why cfupdate and cfinsert don't work like they used to .

There has to be a simple solution now.

I appreciate your response and hope maybe some more explaination will help you understand what I am looking for and how you can help?

[Dan_Bracuk]

If you want adobe to do something, you have to tell them. http://www.adobe.com/misc/comments.html

[UpstateWeb]

Dan,

I did as you suggested and reported the bug again, but have not heard back. Interestingly enough, when you go to report a bug using the link you suggested, there is no place to tell them the issue is with ColdFusion. They require you select a product and it is not even listed. I used GoLive to report the bug becuase there wasn't anything else. I haven't heard anything so I wonder if that messed things up. Do you know of another way to contact them about this issue or do you know someone to send an email to. You seem to know more than most and have an inside track.

I always appreciate your feedback and regard your advice very highly as you are constantly ingaged in these forums. If you could provide me with some more direction I would appreciate it. I just don't know why they don't seem to want to fix the issue with the MS Access datasource drivers. As I have searched on the issue ever since upgrading from 5.0 to 6.1, I have found a lot of unhappy people. Is it really that big of a deal. I mean, it is broken. It does not work. Wouldn't they want to fix it?

Again, I would appreciate your feedback!

[UpstateWeb]

Dan,

As always you are a voice of reason. But I have read through the forums and talked with someone at Macromedia about this issue a little over a year ago. They are aware of the problems with MS Access and have the following solution:

http://sdc.shockwave.com/cfusion/knowledgebase/index.cfm?id=2d2a7a19

I have tried this and all that happens is my websites go down and do not work. I am sure it is something I am doing, but I just can't get this to work no many how many times I have triend (and I have tried and triend and....)

I was hoping by now someone would have come up with an easier or other solution. I have posted this several times over the last year and was just hoping I might find something new.

Thanks again!

[Newsgroup_User]

It doesn't work anyway. I tried EVERY DSN type for Access (w & w/o
Unicode, Jadozoom, ODBC)...they plain and simple broke this on CFMX.
Your only solution might be a UDF:
http://www.cflib.org/udf.cfm?id=524

HTH
--
Tim Carley
www.recfusion.com
info@NOSPAMINGrecfusion.com

[UpstateWeb]

How hard would it be for them to work to fix this. I can't imagine it would be that hard and they could put out a patch. The reason I use ColdFusion is because it is supposed to be more 'Plug & Play" than open source. If the datasource doesn't work they should fix it.