User generated export queries

[Nathan H]

I am curious if something is possible. I would like to set up a method for which an end user could create their own queries for export, much like you can do in most AS400 systems. I am trying to plan for as many pre-built queries as possible...but obviously can not plan for every possible scenario. These are runtime packaged databases, so db window is not available. Any ideas on how this could be done? ...I will worry about security issues later, just curios if this is possible.

[zachster17]

I am curious if something is possible. I would like to set up a method for which an end user could create their own queries for export, much like you can do in most AS400 systems. I am trying to plan for as many pre-built queries as possible...but obviously can not plan for every possible scenario. These are runtime packaged databases, so db window is not available. Any ideas on how this could be done? ...I will worry about security issues later, just curios if this is possible.

You could use SQL language and set it to a recordset object.

such as:

Dim SQL As String
Dim rsSet As Recordset
 
SQL = "SELECT field1, field 2 FROM table1"
Set rsSet = CurrentDb.OpenRecordset(SQL)
 

I don't know if this is what you meant or not. Search SQL on the web--you can do anything that you can do in query design view with SQL. As a matter of fact if you ever design a query in design view you can change the view to 'SQL' and see the SQL part of it.

Zach

[puppydogbuddy]

I am curious if something is possible. I would like to set up a method for which an end user could create their own queries for export, much like you can do in most AS400 systems. I am trying to plan for as many pre-built queries as possible...but obviously can not plan for every possible scenario. These are runtime packaged databases, so db window is not available. Any ideas on how this could be done? ...I will worry about security issues later, just curios if this is possible.

Nathan,
Yes, you can. See this link for a free download that is a working demo of one way you can construct flexible query facility.

http://www.candace-tripp.com/utterangel.aspx#22

[Nathan H]

Nathan,
Yes, you can. See this link for a free download that is a working demo of one way you can construct flexible query facility.

http://www.candace-tripp.com/utterangel.aspx#22

Thanks PDB,

this is pretty close to what I am looking for. Have you played around with this demo? What I would really like to do is be able to let the user "drop" fields into the query result. A table would pop up and then they could select which fields to use in the select query, assuming that the user does not understand SQL. It would be almost like using the query wizard, but in form view.

I hope I am making sense of what I am trying to envision.

Nathan

[puppydogbuddy]

Thanks PDB,

this is pretty close to what I am looking for. Have you played around with this demo? What I would really like to do is be able to let the user "drop" fields into the query result. A table would pop up and then they could select which fields to use in the select query, assuming that the user does not understand SQL. It would be almost like using the query wizard, but in form view.

I hope I am making sense of what I am trying to envision.

Nathan

Hi Nathan,

Your explanation of what you want is very clear. Check these three downloads out.It has been a long time since I played around with them, but as best as I can remember, these are closer to what you want.


CreateQueryFields.mdb
CreateMultiSelectQuery.db
CreateQuery5.mdb

http://www.rogersaccesslibrary.com/TableOfContents3.asp


So that you are aware, the site requests a voluntary $1 donation for each mdb you download.