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

--
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/acc...abaseDesign101

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.htm...eProcedureAdox
or DDL:
http://allenbrowne.com/func-DDL.html#CreateViewDDL

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Chris Freeman" <(E-Mail Removed)> wrote in message
news:78241DDB-B0D9-432A-B8C0-(E-Mail Removed)...
> 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
>
> --
> Chris Freeman
> IT Project Coordinator

On Aug 26, 9:49*pm, Chris Freeman <cfree...@hotmail.com> wrote:
> 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
>
> --
> Chris Freeman
> IT Project Coordinator

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)

On Wed, 26 Aug 2009 19:49:01 -0700, Chris Freeman <(E-Mail Removed)>
wrote:

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

John W. Vinson [MVP]

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
IT Project Coordinator


"John W. Vinson" wrote:

> On Wed, 26 Aug 2009 19:49:01 -0700, Chris Freeman <(E-Mail Removed)>
> wrote:
>
> >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?
> --
>
> John W. Vinson [MVP]
>

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


"Allen Browne" wrote:

> 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/acc...abaseDesign101
>
> 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.htm...eProcedureAdox
> or DDL:
> http://allenbrowne.com/func-DDL.html#CreateViewDDL
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "Chris Freeman" <(E-Mail Removed)> wrote in message
> news:78241DDB-B0D9-432A-B8C0-(E-Mail Removed)...
> > 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
> >
> > --
> > Chris Freeman
> > IT Project Coordinator
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Chris Freeman" <(E-Mail Removed)> wrote in message
news:8E104505-345E-4B0D-80A7-(E-Mail Removed)...
> 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
>
>
> "Allen Browne" wrote:
>
>> 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/acc...abaseDesign101
>>
>> 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.htm...eProcedureAdox
>> or DDL:
>> http://allenbrowne.com/func-DDL.html#CreateViewDDL
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "Chris Freeman" <(E-Mail Removed)> wrote in message
>> news:78241DDB-B0D9-432A-B8C0-(E-Mail Removed)...
>> > 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
>> >
>> > --
>> > Chris Freeman
>> > IT Project Coordinator
>>
>>