Access 2000 RND with Microsoft.Jet.OLEDB.4.0

[Matt_T_hat]

I have a M$ Access 2K DB which has a single table and a query saved in it.

The Query Starts SELECT * FROM MyTable but goes on to add a "new" field using RND() seeded with the Now() and [id].

The same SQL is used directly in my ASP page.

If I access the Query or create a connection to the table using the SQL the result is the same. If I connect again and do the same the result is still the same.

It should be different as I am ordering by the RND col.

I'm useing Microsoft.Jet.OLEDB.4.0 (if it makes any difference) and I want to know if anyone knows a way to slap a RANDOMIZE instruction on the DB to "refresh" the RND order of rows.

[Ecniv]

hi

try something like this:

SELECT Table1.*, Random([id]) AS OrderByMe
FROM Table1
ORDER BY Random([id]);

I had to make a function Random because I stupidly made a project called rnd and it didn't like that confused Access.

Change Random to Rnd and make sure it uses the unique ID as this forces the call each time rather than calcing a value once and reusing... which is what yours is probably doing and what my first attempt did

Good luck

[Matt_T_hat]

I've gotten pritty excessive in my search for a random number

I use the following:

rnd([id]) rnd(second(now()) and other nested requests

I'll explain now that there are 3 connection coditions that are used the random is just the most complex...

I connect useing a single sub (below)

the Random is called when spec = "showad" and at the moment I've taken to randomly selecting one of 9 possible randomising methods the result is that each user now has a total of 9 results and no way of knowing which they will get... it's not quite random... but it is different for each user.

sub openconn
 
    DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & Server.Mappath("./../../private/refads.mdb") & ";"
 
    Set objRecordset = Server.CreateObject("ADODB.Recordset")
    connstr = "refads"
    if spec = "showref" then connstr = "SELECT * FROM refref where [url] = '" & URL & "';" 
    if spec = "showad" then 
        dim harry, freddy, jonny
            harry = rnd(Second(Now())*100)
 
            freddy = round(rnd(20)+1)
 
        select case freddy
            case 1
                Jonny = "max(Rnd(-(Second(rnd([id]*100))*" & harry & "*rnd([id]*100))))"
            case 2
                Jonny = "max(Rnd(-(Hour(rnd([id]*100))*[Date]*rnd([id]*100))))"
            case 3
                Jonny = "max(Rnd((Second(rnd([id]*100))*rnd([Date]))))"
            case 4
                Jonny = "max(Rnd(-(Second(rnd([id]*-1000))*" & harry & "*rnd([id]*100))))"
            case 5
                Jonny = "max(Rnd((Second(rnd([id]*-500))*" & harry & "*rnd([id]*100))-rnd(now())))"
            case 6
                Jonny = "max(Rnd(-(Second(rnd([id]*100))-rnd(now()+[id])*" & harry & "*rnd([id]*100))))"
            case 7
                Jonny = "max(rnd([Date]))"
            case 8
                Jonny = "max(rnd([Date])*rnd(now()+[id]))"
            case else
                Jonny = "max(iif(round(rnd(3)))>1, " & _
                "Rnd(-(Second(rnd([id]*100))-" & harry & ")), " & _
                "iif(rnd(id*2)>id),rnd(rnd([id])+rnd(1)+rnd(2)-rnd(3)+rnd(4)), 5+rnd([Date])+rnd(2)-rnd(3)+rnd(4)+rnd(1)+rnd(2)-rnd(3)+rnd(4)+rnd(1)+rnd(2)-rnd(3)+rnd(4))"
        end select
 
 
 
        connstr = "SELECT TOP 3 * FROM (SELECT TOP 20 * FROM (" & _
        "SELECT Max(refads.Date) AS MaxOfDate, refads.URL, refads.CallMe, refads.title, " & _
        Jonny & " AS bob " & _
        "FROM refads " & _
        "GROUP BY refads.URL, refads.CallMe, refads.title " & _
        "HAVING (((Month(Max([refads].[Date])))>=Month(Now())-1)) " & _     
        "ORDER BY " & Jonny & " DESC  " & _
        ") as RNdADS where [url] <> '" & URL & "' ORDER BY bob) as sumit ORDER BY bob DESC;" 
        if code = "999" then connstr = "refads"
    end if
    objRecordset.Source           = connstr
    objRecordset.ActiveConnection = DB_CONNECTIONSTRING
    objRecordset.CursorType       = adOpenKeyset
    objRecordset.LockType         = adLockOptimistic
    objRecordset.Open
 
end sub