Creating SQL Queries from strings (VB)

[maxhavoc]

Does anyone know how to create a query object using a string that contains SQL code?

The reason I ask is that I want to be able to run an ad hoc query, but I can't use a RecordSet because there's no way (that I know of) to take the results of a recordset and create a query display out of it (meaning, make it look like a query was executed)

So what I really want to do is to create a query, and then execute it with "DoCmd.OpenQuery()" If that's possible. Any ideas?

 

[alastair69]

Are you going to use a button to run the sql code?

 

[maxhavoc]

That was my plan yes, why does that matter though?

 

[nateobot]

You could create a blank query in my case called 'qryTemp'.

Then assign the SQL of it like so:

CurrentDb.QueryDefs("qryTemp").SQL = buildSQL(, txtLName.Text)

Which would kick off this function:

Function buildSQL(Optional sFName As String, Optional sLName As String, Optional sCity As String, Optional sCountry As String) As String
    buildSQL = "SELECT * FROM TABLE1"
    If Not (sFName = "" And sLName = "" And sCity = "" And sCountry = "") Then
        'build the where clause
        buildSQL = buildSQL & " WHERE"
        If Not sFName = "" Then
            'query for first name
            buildSQL = buildSQL & " FName Like '*" & sFName & "*' AND"
        End If
        If Not sLName = "" Then
            'query for first name
            buildSQL = buildSQL & " LName Like '*" & sLName & "*' AND"
        End If
        'and so on
        'remove the final AND
        buildSQL = Left$(buildSQL, Len(buildSQL) - 3)
    End If
    
End Function

You can then run or open that query to get your results.

 

[maxhavoc]

I don't entirely understand your function. I just need to set a query to a string, that's all. Can I use CurrentDb.QueryDefs("qryTemp").SQL to just set a string to the query?

 

[maxhavoc]

Nevermind, I figured it out

CurrentDb.QueryDefs("qryTemp").SQL

was the exact code that I needed, thanks a lot.