Exporting data based on dynamic query

  • Thread starter Thread starter Guy Kerr
  • Start date Start date
G

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
 
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.
 
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

== 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.
 
You cannot export the result of a report.

You can send the report image and formatted data to a Word document. click
Tools>Office Links>Publish with MS Word

You can also export a result of a recordset/query/table. In other words,
open the result of the query that was generated in a datasheet and then
manually (since you mentioned you want the user to view the result of the
query) and then click Tools>Office Links>Analyze with MS Excel.

JD
 
Guy Kerr said:
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
Click to expand...
 
Guy:

This may sound a little convoluted, but I recently did this in an ADP using
this technique:

1) Create your SELECT query based on the WHERE criteria supplied by the
user.
2) Turn the SELECT query into a SELECT INTO query that creates a table.
tmpOutput_UserName
3) Run the SQL necessary to delete the temp table, if indeed it exists from
a previous run
4) Execute the SELECT INTO table to create the temp table.
5) Use the DoCmd.TransferText or OutputTo to dump the table data into CSV
or Excel.

If you think you might want to try this out and you need specifics on the
sql for any of these steps, let me know and I'll post what I have.
 
Back
Top