Create a query based on user input

[Chris Freeman]

I have a database that uses a single query to run the most of the
application. Now we are changing data structure, and instead of recreating
queries for the 15 different data source tables, I'd like to keep the one
main query and change the data source. There are too many instances in other
queries and code where this one query is called from.

So the user selects a drop down for the client they want informationfrom,
and that would populate and create the qry_all_letters_sent query based on
the client table. Something like: docmd.createquery "Select * from " &
cboClient & """

The kicker is that I need the query for the enitre instance of client
records, so it has to be a makequery, similar to maketable.

TIA

 

[Allen Browne]

You can assign the SQL property of the TableDef, e.g.:
Dim strSql As String
strSql = "SELECT & FROM [" & Me.cboClient & "];"
Currentdb.QueryDefs("Query1").SQL = strSql

But if you have several tables with essentially the same fields, the real
problem is that this is not how you set up a database. Unlike Excel where
you might create different spreadsheets for different clients, you need to
use a relational data structure in Access, and that would not yield lots of
tables with similar structure. The real solution will be to normalize your
schema. That's a big topic in itself, but here's a starting point if you
wish to read up on it:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In answer to your specific question,If you really want to create a query
programmatically, there are several ways to do it, e.g. with DAO:
http://allenbrowne.com/func-DAO.html#CreateQueryDAO
or ADO:
http://allenbrowne.com/func-ADO.html#CreateViewAdo
or ADOX:
http://allenbrowne.com/func-ADOX.html#CreateViewAdox
http://allenbrowne.com/func-ADOX.html#CreateProcedureAdox
or DDL:
http://allenbrowne.com/func-DDL.html#CreateViewDDL

 

[Piet Linden]

I have a database that uses a single query to run the most of the
application. Now we are changing data structure, and instead of recreating
queries for the 15 different data source tables, I'd like to keep the one
main query and change the data source. There are too many instances in other
queries and code where this one query is called from.

So the user selects a drop down for the client they want informationfrom,
and that would populate and create the qry_all_letters_sent query based on
the client table. Something like:  docmd.createquery "Select * from " &
cboClient & """

The kicker is that I need the query for the enitre instance of client
records, so it has to be a makequery, similar to maketable.

TIA

Bit late now, but sounds like your database is not designed
correctly... anyway...

One way to do it might be something like this (Don't like it very
much, but it might work for you...)


Private Sub cmdChangeQuerySQL_Click()
ChangeSQLSourceTable Me.cboOldTableName, Me.txtNewTableName
End Sub

Private Sub ChangeSQLSourceTable(ByVal strOldSourceTable As String,
ByVal strNewSourceTable As String)
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = DBEngine(0)(0).QueryDefs("qHolder")
strSQL = Replace(qdf.SQL, strOldSourceTable, strNewSourceTable)
Set qdf = Nothing

DoCmd.DeleteObject acQuery, "qHolder"
DBEngine(0)(0).CreateQueryDef "qHolder", strSQL

End Sub

And before you go messing up your database, test this on a COPY of
your database, NOT the original.

"qHolder" is a query that I use to get the old query SQL from. In
your case, it could be anything. (You could pass the name in adn then
just point the "SET QDF =..." line to point at it...

Set qdf = DBEngine(0)(0).QueryDefs(strQueryToChange)

 

[John W. Vinson]

I have a database that uses a single query to run the most of the
application. Now we are changing data structure, and instead of recreating
queries for the 15 different data source tables, I'd like to keep the one
main query and change the data source. There are too many instances in other
queries and code where this one query is called from.

So the user selects a drop down for the client they want informationfrom,
and that would populate and create the qry_all_letters_sent query based on
the client table. Something like: docmd.createquery "Select * from " &
cboClient & """

The kicker is that I need the query for the enitre instance of client
records, so it has to be a makequery, similar to maketable.

TIA

If you have a set of identical tables, one for each client, you have made a
BIG mistake. What are the structures of these tables? How (if at all) are they
related?

 

[Chris Freeman]

Yes,
You are all correct in regards to the database structure, but unfortunately,
that's how the vendor application is built. tracker forces us to create a new
table for each client, and its a total pain. I tried using append queries to
post the data into a single table, but response times were unacceptable.

Thanks

 

[Chris Freeman]

Allen,
Another solution we tried was to use UNIONS to append the tables in query.
We did this for the first two tables, and then tried to add the third, and
recieved an error message of "Too many Fields defined"

Help says the system max of 255 fields. Each table is 97 fields wide. Is
there any way around this processing that would allow use of the UNION
statement in our queries?

--
Chris Freeman
IT Project Coordinator

You can assign the SQL property of the TableDef, e.g.:
Dim strSql As String
strSql = "SELECT & FROM [" & Me.cboClient & "];"
Currentdb.QueryDefs("Query1").SQL = strSql

But if you have several tables with essentially the same fields, the real
problem is that this is not how you set up a database. Unlike Excel where
you might create different spreadsheets for different clients, you need to
use a relational data structure in Access, and that would not yield lots of
tables with similar structure. The real solution will be to normalize your
schema. That's a big topic in itself, but here's a starting point if you
wish to read up on it:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In answer to your specific question,If you really want to create a query
programmatically, there are several ways to do it, e.g. with DAO:
http://allenbrowne.com/func-DAO.html#CreateQueryDAO
or ADO:
http://allenbrowne.com/func-ADO.html#CreateViewAdo
or ADOX:
http://allenbrowne.com/func-ADOX.html#CreateViewAdox
http://allenbrowne.com/func-ADOX.html#CreateProcedureAdox
or DDL:
http://allenbrowne.com/func-DDL.html#CreateViewDDL

 

[PieterLinden via AccessMonster.com]

Performance in union queries is absolutely dire, because the optimizer cannot
use the indexes on the individual tables. I'd stay away from them for as
long as possible!

 

[Allen Browne]

In answer to your question, you are not going to get this many UNIONS
working, regardless of performance. At very least, create one more table
with the same fields as you have in your other ones, plus one more to flag
whatever is the difference between your tables. Then use an Append query to
populate the new table with the data in the other tables, one at a time.
This new table that has all the data in it will work with better
performance, and this will be the only way to combine lots of data from that
many tables (rather than a UNION.)

Long term, though, this needs a completely different design to work well. 97
fields wide, and lots of fields with largely the same fields: this is
effectively a bunch of spreadsheets in Access, not a database. Normalization
will be the solution.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Another solution we tried was to use UNIONS to append the tables in query.
We did this for the first two tables, and then tried to add the third, and
recieved an error message of "Too many Fields defined"

Help says the system max of 255 fields. Each table is 97 fields wide. Is
there any way around this processing that would allow use of the UNION
statement in our queries?

--
Chris Freeman
IT Project Coordinator

You can assign the SQL property of the TableDef, e.g.:
Dim strSql As String
strSql = "SELECT & FROM [" & Me.cboClient & "];"
Currentdb.QueryDefs("Query1").SQL = strSql

But if you have several tables with essentially the same fields, the real
problem is that this is not how you set up a database. Unlike Excel where
you might create different spreadsheets for different clients, you need
to
use a relational data structure in Access, and that would not yield lots
of
tables with similar structure. The real solution will be to normalize
your
schema. That's a big topic in itself, but here's a starting point if you
wish to read up on it:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In answer to your specific question,If you really want to create a query
programmatically, there are several ways to do it, e.g. with DAO:
http://allenbrowne.com/func-DAO.html#CreateQueryDAO
or ADO:
http://allenbrowne.com/func-ADO.html#CreateViewAdo
or ADOX:
http://allenbrowne.com/func-ADOX.html#CreateViewAdox
http://allenbrowne.com/func-ADOX.html#CreateProcedureAdox
or DDL:
http://allenbrowne.com/func-DDL.html#CreateViewDDL

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that uses a single query to run the most of the
application. Now we are changing data structure, and instead of
recreating
queries for the 15 different data source tables, I'd like to keep the
one
main query and change the data source. There are too many instances in
other
queries and code where this one query is called from.

So the user selects a drop down for the client they want
informationfrom,
and that would populate and create the qry_all_letters_sent query based
on
the client table. Something like: docmd.createquery "Select * from " &
cboClient & """

The kicker is that I need the query for the enitre instance of client
records, so it has to be a makequery, similar to maketable.

TIA