Problems with programatically creating queries

[Guest]

I have entered the following

Private Sub Command45_Click()

Dim db As Database
Dim strSQL As String
Dim Stable As String
Dim qdf As QueryDef

Set db = CurrentDb

If Forms!RecSelect!Tables = "Company" Then
Stable = "Companies"
End If
strSQL = "SELECT " & Stable & ".SwitchPhone, "
strSQL = strSQL & " FROM " & Stable
strSQL = strSQL & " WHERE (((" & Stable & "." &
Forms!RecSelect!SelectField
strSQL = strSQL & ") " & Forms!RecSelect!Operator1
If Forms!RecSelect!Operator1 = "Like" Then
strSQL = strSQL & Chr(34) & Chr(42)
strSQL = strSQL & Forms!RecSelect!Criteria1 & Chr(42) & Chr(34)
Else
strSQL = strSQL & Forms!RecSelect!Criteria1
End If
strSQL = strSQL & "))"
Me.Text46 = strSQL
db.QueryDefs.Delete ("Search")
Set qdf = db.CreateQueryDef("Search", strSQL)

End Sub

If I leave out the db.QueryDefs.Delete ("Search") part, it will only run
once and then report an error that it already exists
When I put db.QueryDefs.Delete ("Search") to try and delete the query before
recreating it, I have also tried Append .

Amiga1200

 

[Tim Ferguson]

db.QueryDefs.Delete ("Search")
Set qdf = db.CreateQueryDef("Search", strSQL)

If I leave out the db.QueryDefs.Delete ("Search") part, it will only
run once and then report an error that it already exists
When I put db.QueryDefs.Delete ("Search") to try and delete the query
before recreating it, I have also tried Append .

Such is the wonder of VBA... I think that some of these functions run
asynchronously, meaning that the function issues the command to the DAO
library and returns immediately, before all the various lists and heaps
have been tidied up. Try

calling the .Delete at the start of the procedure instead of
immediately before recreating it.

putting in a call to db.QueryDefs.Refresh or, less likely, a DoEvents


at worst, a short Sleep() call -- look up the API declaration for this
on Google.

Then again, if you want to create a temporary QueryDef object, you can
just give it an empty name "" and it'll delete itself once you are
finished with it. Then again again, why not just stuff the SQL straight
into an OpenRecordset method or a Report and forget the qdf part
altogether?

All the best


Tim F