Terry-
Why do the names of the tables change? If you have a list of the "current"
table names (maybe in a table), you could open up each QueryDef of a stored
"template" query, change the table name using Replace, then execute it.
Let's say you have a table with TableName and QueryName, and all the "template"
queries have zzzz stored in the SQL as the table name. Your code might look
like:
Dim db As DAO.Database, rst As DAO.Recordset, qd As DAO.QueryDef
Set db = CurrentDb
' Open the table with query and table names
Set rst = db.OpenRecordset("SELECT * FROM ztblQueriesTables")
Do Until rst.EOF
' Get the query named in the table row
Set qd = db.QueryDefs(rst!QueryName)
' Temporarily replace zzzz with the real table name
qd.SQL = Replace(qd.SQL, "zzzz", rst!TableName)
' Run the query
qd.Execute
' Put the table name back for next time
qd.SQL = Replace(rst!TableName, "zzzz")
rst.MoveNext
Loop
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terry Olsen
Sent: Tuesday, September 27, 2011 5:52 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Use of Table Parameters in Query Builder
Is there a book or website that might have examples of the code used to become
parameterizable?
Thanks
terryomsn
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Parameterizable. <g>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
> Sent: Monday, September 26, 2011 11:56 PM
> To: Access Professionals Yahoo Group
> Subject: RE: [MS_AccessPros] Use of Table Parameters in Query Builder
>
>
> Table/query names are not parametizable (if that's a word). You would need to
> use some code that would update the sources.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> From: terryomsn@...
>
>
>
>
>
> Hi
> I have a set of queries that remain somewhat constant.
> The tables that use the queries change every two weeks, content only, not in
> format.
> Is it possible to create a parameter for a table name that for a query that
will
> use the parameter for in that query?
> Thanks
> Terryomsn
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar