Access Query/Module Won't Transfer to FrontPage

[mobilecs]

Hello,

I wrote a query to select a random record from my database, along with a
Randomize module to initialize the random-number generator. It works
perfectly in Access. But when I import the database to FrontPage, the
database results act as though the Randomize module isn't working. I would
appreciate any help trying to figure out how to get my Randomize module to
work, or where to put a new one.

 

[Douglas J. Steele]

Sorry, but whenever you run queries from outside of Access, you're only
using Jet, which doesn't know anything about user-defined functions (nor,
for that matter, most of the VBA functions). There's no way around this.

 

[mobilecs]

Thank you for the prompt reply, and for the help. I guess that it is back to
the drawing board. Is there a way to structure a SQL Query to randomly
retrieve a record? I could not find one, but my knowledge of SQL is limited.

Sorry, but whenever you run queries from outside of Access, you're only
using Jet, which doesn't know anything about user-defined functions (nor,
for that matter, most of the VBA functions). There's no way around this.

 

[Douglas J. Steele]

Afraid I can't think of any.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you for the prompt reply, and for the help. I guess that it is back
to
the drawing board. Is there a way to structure a SQL Query to randomly
retrieve a record? I could not find one, but my knowledge of SQL is
limited.

 

[John Nurick]

I guess you could do something like this, assuming FrontPage can run VBA
code:

1) In the database, set up a query that returns all the records from
which you want to select one at random, plus a calculated field that
numbers the records sequentially. E.g., if the primary key field is
[ID], the query would be something like this:

SELECT
(SELECT Count(ID) As IDCount
FROM TheTable As B
WHERE B.ID <= A.ID) AS SeqNum,
*
FROM TheTable AS A
ORDER BY A.ID;

2) In FrontPage, open a recordset on this query. Use Randomize() and
Rnd() to generate a random integer between 1 and the number of records
in the recordset (inclusive), and use the record whose SeqNum matches
the random number.

If the recordset could be large, the load on the network would be
smaller if you modify the query above so it only returned the primary
key field(s), (e.g. replace the * above with ID). Then select one record
from the recordset as above, but get its ID value and use it to
construct a SQL statement that you can execute in your code to return
the single record you want.

Thank you for the prompt reply, and for the help. I guess that it is back to
the drawing board. Is there a way to structure a SQL Query to randomly
retrieve a record? I could not find one, but my knowledge of SQL is limited.

 

[mobilecs]

Thank you, John. I will give it a try. Can't lose anything at this point.

I guess you could do something like this, assuming FrontPage can run VBA
code:

1) In the database, set up a query that returns all the records from
which you want to select one at random, plus a calculated field that
numbers the records sequentially. E.g., if the primary key field is
[ID], the query would be something like this:

SELECT
(SELECT Count(ID) As IDCount
FROM TheTable As B
WHERE B.ID <= A.ID) AS SeqNum,
*
FROM TheTable AS A
ORDER BY A.ID;

2) In FrontPage, open a recordset on this query. Use Randomize() and
Rnd() to generate a random integer between 1 and the number of records
in the recordset (inclusive), and use the record whose SeqNum matches
the random number.

If the recordset could be large, the load on the network would be
smaller if you modify the query above so it only returned the primary
key field(s), (e.g. replace the * above with ID). Then select one record
from the recordset as above, but get its ID value and use it to
construct a SQL statement that you can execute in your code to return
the single record you want.

Thank you for the prompt reply, and for the help. I guess that it is back to
the drawing board. Is there a way to structure a SQL Query to randomly
retrieve a record? I could not find one, but my knowledge of SQL is limited.

 

[mobilecs]

Thanks for trying.

Afraid I can't think of any.