need to extract WHERE clause from access query in code 1

[danielemc2]

I am creating a list of queries for the user to select from.

Dim obj As AccessObject
Dim dbs As Object
For Each obj In dbs.AllQueries
QueriesList.RowSource = QueriesList.RowSource + obj.Name + ";"
End If
Once they have chosen a query I need to get the sql from it.
These are SELECT queries and I need to build and run an UPDATE query based on the WHERE clause.

Is this possible?

 

[KyleS]

You can declare an object as a querydef and get the SQL that way. The code would look like:

Sub GetSQLWhere()
Dim qry As DAO.QueryDef, str As String
Set qry = CurrentDb.QueryDefs("qryNRDTasks&quot

str = qry.SQL

str = Right(str, Len(str) - InStr(1, str, "WHERE&quot + 1)
If InStr(1, str, "ORDER BY&quot Then
str = Left(str, InStr(1, str, "ORDER BY&quot - 3)
End If

Set qry = Nothing

End Sub

This will give you the "WHERE" clause including the "WHERE" and without the "ORDER BY"
Kyle