ASP recordset as SQL table

[Ennex]

Hi experts,

I'm porting a database developed in Access to a Web site. In Access, I
define a number of queries that are then used in other queries just as if
they were tables. How do I do the same thing in ASP?

I am thinking that what I want to do is something like the following. I
would open a recordset that holds the results of a query:

oRS1.Open('SELECT fields FROM table WHERE criterion', ...)

and then I want to open another recordset that performs a SQL operation on
the first recordset

oRS2.Open('SELECT fields FROM oRS1 WHERE criterion', ...)

But I don't know how to do that. How do I reference an established recordset
in a new SQL statement? I can't just put the name of the recordset there
because the table name that goes into a SQL statement is a string, and the
recordset is an object, not a string.

Am I making sense? I'm sure there must be a way to do this, but I can't find
any documentaiton of such a procedure.

Thanks for your help.

Regards,
Marshall Burns
www.Ennex.com

 

[Brendan Reynolds]

You can continue to do the same thing in ASP that you do in Access. Although
most ASP examples build SQL strings in code, there's nothing to prevent you
from using saved queries.

The alternative would be to use the Filter method of the ADO recordset.

 

[Ennex]

You can continue to do the same thing in ASP that you do in Access. Although
most ASP examples build SQL strings in code, there's nothing to prevent you
from using saved queries.

If you mean the queries saved in Access, that's what I'm doing on my
development machine, but when I run this on the Web, my server will not be
running Access, so I need to do all the DB processing in SQL. Basically, what
I'm asking is, is there a way to make a saved query in SQL outside of Access?

The alternative would be to use the Filter method of the ADO recordset.

Filtering does not do what I need because I need to make joins between
tables and the queries.

MB

 

[Brendan Reynolds]

There is no need for the server to be running Access. Queries are processed
by the Jet database engine. There are some things you can do with queries in
Access that you can't do outside of Access (such as calling custom VBA
functions) but in general you can use a query in your ASP code in exactly
the same way that you would a table.

In order to be able to create new queries and modify existing ones without
taking down the database, you'll need to be familiar with JET DDL. If you're
not already familiar with this, start with the Access help topic 'CREATE
VIEW', and follow the links to related topics from there.