Trouble with Access

April 25, 2004 7:44 PM

Trouble with Access driving me insane...{more internally}

I have an Access 2000 database with a table called Taglines. It is being used in a FrontPage-driven ASP website. What I want to do is make it so that each time the page loads, a record from that table is automatically chosen at random at displayed on the ASP page, so that a new, random tagline appears each time the page is refreshed (sort of like metafilter). I made a query with the following SQL statement in Access:

SELECT Taglines.Text, Taglines.ID, TaglineCount.CountOfID
FROM Taglines, TaglineCount
GROUP BY Taglines.Text, Taglines.ID, TaglineCount.CountOfID
HAVING (((Taglines.ID)=Int(([TaglineCount]![CountOfID]-1+1)*Rnd()+1)));

When I run the query in Access, it works perfectly, returning a random tagline each time. However, when I put those database results into the page with FrontPage, it ***ALWAYS*** displays the tagline text that has ID number 5. There are currently 6 total records. I have tried absolutely everything, it is driving me insane, I only use Access/Frontpage because it is quick and easy. Thanks!

posted by ac to Computers & Internet (6 answers total)

ac, you need to use the "Randomize" statement when you open your db, to initialize the random number generator. You can just stick it at the top of your query, like so:

Randomize
SELECT Taglines.Text, Taglines.ID, TaglineCount.CountOfID
FROM Taglines, TaglineCount
GROUP BY Taglines.Text, Taglines.ID, TaglineCount.CountOfID
HAVING (((Taglines.ID)=Int(([TaglineCount]![CountOfID]-1+1)*Rnd()+1)));
posted by mr_crash_davis at 8:32 PM on April 25, 2004

Response by poster: Where do I paste this? If I write the SQL statement directly in FrontPage and try to run it, it tells me:

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

If I put it right into the SQL in Access.. I get:

Invalid SQL statement; expected DELETE< insert, procedure, select, or updatebr>

I couldn't find anywhere else to type in "Randomize". I am not programming this manually, I am using FrontPage's feature where it automatically inserts data from Access into a page dynamically, with no programming required. Thanks for quick answer though, I didn't think anyone would want to tackle such an obscure thing.
posted by ac at 9:36 PM on April 25, 2004

My solution is code-based, unfortunately, but (at least for me) it's cleaner than dealing with macros or any of that other garbage in Access. You should be able to edit and view your ASP source code from FrontPage.

<%
Randomize
dim sql

sql1 = "SELECT Taglines.Text, Taglines.ID, TaglineCount.CountOfID FROM Taglines, TaglineCount GROUP BY Taglines.Text, Taglines.ID, TaglineCount.CountOfID HAVING (((Taglines.ID)=Int(([TaglineCount]![CountOfID]-1+1)*"

sql2 = trim(Rnd())

sql3 = "+1)));"

sqltext = sql1 & sql2 & sql3
%>

This creates a SQL string which contains the literal random number, which it can then pass off to Access.

Next, you'll need to find the portion of the code where the query is called, and substitute sqltext for the query name. This is where it gets tricky - post the code here if you aren't sure where this substitution should take place.
posted by PrinceValium at 10:39 PM on April 25, 2004

Response by poster: Crap... When I try to edit the frontpage-generated code and insert that in there, it tells me I can't edit autogenerated stuff and undos my changes. All I am trying to do is run a simple query from the database... how do I manually write ASP code to connect to the database and run that query? Thanks!
posted by ac at 1:15 PM on April 26, 2004

Try this:

<%
Dim intCount, intRandom
Dim objConn, objRS, strSQL

'Create a recordset object called objRS with a connection
'that points to your database

Randomize
strSQL = "SELECT Count(*) as foo From Taglines"
objRS.Open strSQL
intCount = objRS("foo")
intRandom = Round(Rnd() * intCount), 0)
strSQL = "SELECT * FROM Taglines WHERE Taglines.ID = " & intRandom
objRS.Open strSQL
...
%>

That ought to do the trick.
posted by vraxoin at 1:45 PM on April 26, 2004

Response by poster: YES! Thank you all! I LOVE ask mefi.. it finally worked after I messed with it, thanks to all you guys' help. Ahhh.. random taglines are so cool. I have hundreds of witty ones for my site.
posted by ac at 7:25 PM on April 26, 2004