Edit existing query by vb (1 Viewer)

[prometro]

Hi, I need help with this basic thing :

I have a query named Q1.

1) If I want change its sql string by VB, how can I do it? = how I can update its content?

2) other possibility is to delete query and create new query with the same name.
But there is a mistake if I want make new query and old one still exists.
Which vb code can ask "exists query named Q1" ?

....?
Thank you
Jiri

 

[vbaInet]

Here's a tutorial:

http://baldyweb.com/BuildSQL.htm

 

[prometro]

Hi, thanks for reply.
But Iam not sure if I explain it well, becaouse of my english.

1) I create query named "Q1" by VB.
2) Now I need to change sql statement in the same query "Q1" by VB.

If I run code to create query named Q1 again, there is a mistake because
query Q1 does exist already...

 

[vbaInet]

Something like this:

    Dim db As Database, qdf As QueryDef
    Dim strSQL As String, strFarRight As String
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Q1")
    strSQL = qdf.SQL

    strFarRight = Right(strSQL, 1)
    
    ' Strip out the following chars
    Do While strFarRight = vbLf Or strFarRight = vbCr Or strFarRight = " " Or strFarRight = ";"
        strSQL = Left(strSQL, Len(strSQL) - 1)
        strFarRight = Right(strSQL, 1)
    Loop
    
[COLOR=Red][B]    ' Change your the sql string here and remember to add ";" at the end[/B][/COLOR]
    ' E.g. qdf.sql = strSQL & " WHERE [FieldA]='Something'"
    Debug.Print strSQL

 

[evanscamman]

I don't think you need to create a saved query.
Instead, build and then execute an SQL statement.

 

[vbaInet]

I don't think you need to create a saved query.
Instead, build and then execute an SQL statement.

Post #2 was advising that already to which the OP replied he didn't want that.