Change a Query's SQL with VBA code then save the query

[GeorgeJ]

Suppose I want to set a string variable S$ to the SQL string of a saved query named Q_Temp01.

Can someone show me the code that would enable me to do this?
What about the code to change the SQL to something else and then save the query with the new SQL?



Thanks in advance.

SORRY FOR THE ACCIDENTAL DOUBLE-POST. (Is there some way to delete a post one has just made?)

[pbaldy]

The tool you're looking for is a DAO QueryDef. You can read or write the SQL property of a saved query with one.

I deleted the duplicate post.

[GeorgeJ]

Thank you Paul.

I've been noodling around and I've discovered that this works. This may be of help to somebody:
Sub test24()
Dim S$, v As Variant
v = CurrentDb.QueryDefs![Q_Temp01].SQL
Debug.Print v
'below is the stuff copied from the immedeate window.
'SELECT T_AllCards.CardNum AS T_AllCards_CardNum, T_AllCards.Chosen01, T_DeckCards.DeckNum, T_DeckCards.CardNum AS T_DeckCards_CardNum
'FROM T_DeckCards INNER JOIN T_AllCards ON T_DeckCards.[CardNum] = T_AllCards.[CardNum]
'WHERE (((T_DeckCards.DeckNum)=3));

'Now I Change 3 to 4
S$ = "SELECT T_AllCards.CardNum AS T_AllCards_CardNum, T_AllCards.Chosen01, T_DeckCards.DeckNum, T_DeckCards.CardNum AS T_DeckCards_CardNum" & _
" FROM T_DeckCards INNER JOIN T_AllCards ON T_DeckCards.[CardNum] = T_AllCards.[CardNum]" & _
" WHERE (((T_DeckCards.DeckNum)=4));"

CurrentDb.QueryDefs![Q_Temp01].SQL = S$

'Success! I find that the change to Q_Temp01 is saved and remains when I close and reopen the DB. This is encouraging.
End Sub

[pbaldy]

Happy to help! Is there a reason you don't just use a parameter query, and have the value for DeckNum entered on a form?

[GeorgeJ]

Happy to help! Is there a reason you don't just use a parameter query, and have the value for DeckNum entered on a form?

Yes there is, but the explanation is long-winded. If you're interested, here it is. (I sure wouldn't blame you if you don't want to wade through it. Apparently you have the power to delete posts, which you might want to consider in this case .)


ere's what I am trying to do. I have a large table T_AllCards, which contains fields CardNum(Long), SideAText and TextBText, and Chosen01(yes/no), along with other fields. A specific value of CardNum never appears more than once in T_AllCards. There is another table T_DeckCards which has fields Decknum(Integer) and CardNum(Long). T_AllCards.Cardnum is joined to T_DeckCardsCardnum. I have a form F_Query1 which has as its record source Q_Query1. The user interacts with the form in text boxes to specify which Decks will be included as well as search strings in SideAText and SideBText. For the example, the user might indicate that he wants a list which includes: All cards in Deck1, all cards in Deck 3 which have "sample" in their SideAText field, etc. The problem I ran into was that card numbers were duplicated in the search. For instance, T_Deck Cards might contain the record


DeckNum CardNum SideAText
5 433 st5
6 433 st6

If the user specified that he wanted all cards in Deck 5 and all cards in Deck 6 included in the search, then card 433 would show up twice in the recordset of the query, which is not what I wanted. The DISTINCT word isn't helpful here, because as far as I can figure out it applies to all fields in the search, so the above records would not be distinct from one another. I did not want any card number to appear more than once in the search results. So I hit upon the following strategy. Add the Boolean field Chosen01 to T_AllCards. Then use a two stage process. First run this

CurrentDb.Execute "UPDATE T_AllCards SET Chosen01=False"


Then set the SQL of Q_Query1 so that it caught all the appropriate cards, then run

CurrentDb.Execute "UPDATE Q_Query01 SET Chosen01=True"

There would be Q_Query2 which chose all records in T_AllCards for which T_AllCards.Chosen01 = True.

The tabular form F_CardsChosen has recordsource Q_Query2.

So After the user specifies new criteria for the card selection, the routine MAKESQL runs. This routine creates the appropriate global string
MYSQL. The following code runs



CurrentDb.Execute "UPDATE T_AllCards SET Chosen01=False"
MAKESQL
CurrentDb.QueryDefs![Q_Temp01].SQL = MYSQL
CurrentDb.Execute "UPDATE Q_Temp01 SET Chosen01=True"
Me.Requery ‘now Q_Query02, which is the recordsource for Me, picks up all the records in T-AllCards
‘ for which T_AllCards.Chosen01 = True. No card numbers are duplicates because any given value of CardNum appears only
' once in T_AllCards

[pbaldy]

Apparently you have the power to delete posts

I'm a moderator; we can do anything!

I won't pretend to understand your situation, so it sounds like you've come up with a solution that works, and I agree with the philosophy "if it ain't broke, don't fix it".

[June7]

I would avoid process that modifies query definition as this is a design edit. I have only ever used QueryDef once for manipulation of data and this was to delete and recreate a query object so that users can customize a data export to Excel. I have used QueryDef to programmatically modify properties of new table which is also created by code. (A procedure so rarely used I am tempted to remove it.)

Otherwise, I recommend a 'temp' table - the table is permanent but records are not. Delete records when the data manipulation process is completed. In my case, the end goal is report output.