changing sql sentence in query via vba code

[thread]

Hi all,
I'm trying to find a way to change the sentece of the sql inside the
query via vba code
for now no clue

 

[tina]

try

CurrentDb.QueryDefs("QueryName").SQL = "type the entire SQL statement
here"

note that you have to set the SQL property of the QueryDef to a complete SQL
statement - you can't just change "bits and pieces" of it.

hth

 

[Douglas J. Steele]

<picky>

CurrentDb.QueryDefs("QueryName").SQL = _
Replace(CurrentDb.QueryDefs("QueryName").SQL, "Table1", "Table2")

or, more efficiently,

Dim qdfCurr As DAO.QueryDef

Set qdfCurr = CurrentDb.QueryDefs("QueryName")
qdfCurr.SQL = Replace(qdfCurr.SQL, "Table1", "Table2")
</picky>

 

[thread]

thanks people,
i fould something more efficient for me,
i'm just placing the sql sentence inside of the recordsource form,saves
place of a query even

Douglas J. Steele ëúá:

 

[tina]

<pickier>

but if you can run the same SQL statement on two different tables by
changing only the table name, they must be identical but for the table name.
sounds to me like that should be one table, with a field to hold whatever
data is currently stored in the table name...

 

[Douglas J. Steele]

No point getting into an endless semantic argument, but if you consistently
use the same field names, it's certainly conceivable that you might have the
following SQL statements where it doesn't make sense for Table1 and Table2
to be the same table:

UPDATE Table1 SET ConfirmationDate = Date() WHERE ConfirmedFG = True

UPDATE Table2 SET ConfirmationDate = Date() WHERE ConfirmedFG = True

Heck, you could even be talking about

SELECT * FROM Table1

SELECT * FROM Table2

<grin>

 

[thread]

what i found that was best for me is the issue that i can programmicly
pick up diffrent fields and add them to the recordsource of the form so
that way i just had to make some kind of dynamic arguments for the
textboxes that are there and have diffrent grouping for the same
modular form.
it can save alot of space
Douglas J. Steele ëúá: