since you are using an ADP, you should be using a stored procedure
that will take all the parameters you are developing and then
displaying the data based on it. There is no DAO.QueryDef in an ADP
project. All of the queries are SQL Server views.
If you are saving the entire WHERE clause in this table for use later,
then you could do a stored procedure something like this:
CREATE PROCEDURE dbo.pQueryName
@WhereClause VARCHAR(1000),
@OrderByClause VARCHAR(500)
AS
BEGIN
DECLARE @Sql NVARCHAR(4000)
SET @Sql = 'SELECT * FROM tblNames ' + @WhereClause + @OrderByClause
EXEC sp_executesql @Sql
END
If you implement this, you should also be checking for a ; in either
the where clause or the order by clause and default them to an empty
string if either one is found so you do not get an injection attack.
Robert
On Fri, Jan 16, 2009 at 2:17 AM, microsoft.public.access.adp.sqlserver
group <(E-Mail Removed)> wrote:
== 1 of 2 ==
Date: Thurs, Jan 15 2009 10:08 am
From: Guy Kerr
This sounded like something that wouldn't be too challenging but for
some
reason I can't figure out the best way to do this.
I have an unbound form that contains Listboxes, radio buttons, unbound
controls, checkboxes etc. that correspond to fields in my database.
This
form is designed to build a query - or at least the WHERE CLAUSE.
Once the
user checks the boxes, fills in the unbound controls they click a
button to
generate a query. For example if the user fills in Kerr* in LastName
field,
checks off the >18 checkbox, select 'CA' from the State listbox then
clicks
the GENERATE QUERY button a record is created in a table that is
essentially
the Select Statement based on what the user selected on the form. So
the
text generated would be "Lastname like '%Kerr' and Age > 18 and State
= 'CA'".
Then when a user opens a form it does a DLookup based on the user name
to
search the table for the latest query to correspond to that user. It
then
uses this to apply the ServerFilter to the form which limits the
dataset to
what the defined query is. This is not relevant to what I'm trying to
accomplish, I just wanted to explain the function of the form and the
table
storing the queries.
What I WANT to do is export data to TAB Delim or CSV from the same
table
based on any of the queries stored in this table. I was looking at
DoCmd.TransferText but it looked to me that you had to refer to a
named
query. I was also considering using a report then export from that.
This
way the user can see the data before choosing to Export.
If anyone has any ideas on how best to do this I would be very much
appreciative.
Guy
== 2 of 2 ==
Date: Thurs, Jan 15 2009 10:33 am
From: "Paul Shapiro"
You can use a named query and just update its sql before exporting.
Open a
DAO.QueryDef, and then I believe the sql is one of the properties. Set
it to
your new sql string, save the querydef, and then export it.
"Guy Kerr" <(E-Mail Removed)> wrote in message
news:396D5238-2109-4CFD-ACEC-(E-Mail Removed)...
> This sounded like something that wouldn't be too challenging but for some
> reason I can't figure out the best way to do this.
>
> I have an unbound form that contains Listboxes, radio buttons, unbound
> controls, checkboxes etc. that correspond to fields in my database. This
> form is designed to build a query - or at least the WHERE CLAUSE. Once
> the
> user checks the boxes, fills in the unbound controls they click a button
> to
> generate a query. For example if the user fills in Kerr* in LastName
> field,
> checks off the >18 checkbox, select 'CA' from the State listbox then
> clicks
> the GENERATE QUERY button a record is created in a table that is
> essentially
> the Select Statement based on what the user selected on the form. So the
> text generated would be "Lastname like '%Kerr' and Age > 18 and State =
> 'CA'".
>
> Then when a user opens a form it does a DLookup based on the user name to
> search the table for the latest query to correspond to that user. It then
> uses this to apply the ServerFilter to the form which limits the dataset
> to
> what the defined query is. This is not relevant to what I'm trying to
> accomplish, I just wanted to explain the function of the form and the
> table
> storing the queries.
>
> What I WANT to do is export data to TAB Delim or CSV from the same table
> based on any of the queries stored in this table. I was looking at
> DoCmd.TransferText but it looked to me that you had to refer to a named
> query. I was also considering using a report then export from that. This
> way the user can see the data before choosing to Export.
>
> If anyone has any ideas on how best to do this I would be very much
> appreciative.
>
> Guy
|
Similar Threads
