Change a query?

  • Thread starter Thread starter Henrootje
  • Start date Start date
H

Henrootje

I have a query that is called 'qryROSR' which in SQL looks like

SELECT tblqROSR1.*
FROM tblqROSR1;

I would like to programmatically be able to change the SQL in the
query to

SELECT tblqROSR2.*
FROM tblqROSR2;


(mind the change from 1 ->2, users should be able to choose between 1
-2 -3 -4 )
without changing the name of the query.

Why? All following queries and reports and such are based on this query
and that way I can switch my db form using tblqROSR1 to using tblqROSR2

Suggestions anyone ?

TIA,

Henro
 
I have a query that is called 'qryROSR' which in SQL looks like
SELECT tblqROSR1.*
FROM tblqROSR1;

I would like to programmatically be able to change the SQL in the
query to

SELECT tblqROSR2.*
FROM tblqROSR2;

(mind the change from 1 ->2, users should be able to choose between 1
-2 -3 -4 )
without changing the name of the query.

Why? All following queries and reports and such are based on this query
and that way I can switch my db form using tblqROSR1 to using tblqROSR2

Suggestions anyone ?

TIA,

Henro
Click to expand...

why not write your own queries in VBA code and run SQLstr depending on
the choice of the user?

with some coding you could even generate dynamic queries
just like access does for queries made in design view

grtz
 
u r completely right, it would be better to define the source of a form
when opening it I think, but, unfortunately, I am not working alone on
this and 'it has been decided we do it as such'.

Therefore, is it possible to change the SQLsyntax of a query and then
reload it?

TIA,

Henro
 
You can manipulate the SQL property of the DAO QueryDef object ...
CurrentDb.QueryDefs("NameOfQueryHere").SQL = "valid sql statement here"
 
I feel like a moron for asking but could you explain that a bit more
elaborate?

TIA Henro
 
CurrentDb.QueryDefs("NameOfQueryHere").SQL = "valid sql statement here"

CurrentDb returns a DAO.Database object that is a reference to the current
database. The DAO.Database object has a QueryDefs collection that includes a
QueryDef object corresponding to each query in the database. The QueryDef
object has a SQL property which is a string corresponding to the SQL
statement of the query. You can assign any valid SQL statement to this
property. For example, if your user chooses an option from, say, an option
group ...

CurrentDb.QueryDefs("YourQuery").SQL = "SELECT * FROM SomeQuery" &
Me!fraTest

Where "YourQuery" is the query you want to modify, and "fraTest" is the
option group.

If the user chooses the option with the value of 1, the SQL statement ends
up as "SELECT * FROM SomeQuery1". If the user chooses the option with the
value of 2, the SQL statement ends up as "SELECT * FROM SomeQuery2", etc.

If you're more accustomed to using ADO than DAO, it can be done using ADOX
....

Dim cat As ADOX.Catalog

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
cat.Procedures("qryTest").Command = _
"SELECT * FROM Employees WHERE LastName = 'Davolio'"
 
Back
Top