If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Query/Module Won't Transfer to FrontPage
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. -- Thank You, mobilecs |
#2
|
|||
|
|||
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. -- Thank You, mobilecs |
#3
|
|||
|
|||
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. "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. -- Thank You, mobilecs |
#4
|
|||
|
|||
Afraid I can't think of any.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. -- Thank You, mobilecs |
#5
|
|||
|
|||
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. On Sat, 5 Feb 2005 19:09:01 -0800, "mobilecs" wrote: 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. "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. -- Thank You, mobilecs -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#6
|
|||
|
|||
Thank you, John. I will give it a try. Can't lose anything at this point.
"John Nurick" wrote: 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. On Sat, 5 Feb 2005 19:09:01 -0800, "mobilecs" wrote: 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. "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. -- Thank You, mobilecs -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#7
|
|||
|
|||
Thanks for trying.
"Douglas J. Steele" wrote: Afraid I can't think of any. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. "Douglas J. Steele" wrote: 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "mobilecs" wrote in message ... 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. -- Thank You, mobilecs |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to stop opening of multiple instance of Access 2000 after upd. | Taika Bilbo | Database Design | 9 | January 12th, 2005 08:34 PM |
How to get data from Analysis Services cubes into Access | General Discussion | 2 | December 4th, 2004 02:21 PM | |
WORD XP mail-merge FAILS using ACCESS query | SueMackay | Running & Setting Up Queries | 4 | November 29th, 2004 04:11 PM |
is Access 2003 any better than XP? | Gorb | Using Forms | 2 | November 11th, 2004 09:20 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |