Access 2000 RND with Microsoft.Jet.OLEDB.4.0

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.

hi

try something like this:

Code:

---

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

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.

VB Code:

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