Delete a Query, or Empty it and Reload With New SQL?

R

ryguy7272

I’m wondering if it makes sense to delete a query which I build on the fly
each time I use. If I start with no Query, and then run my code, the Query
is loaded with SQL, which comes from several objects on a Form. This is all
driven by VBA. In the original example, the author did NOT delete the query,
but with the query present, I keep getting messages like this:
‘Error Description: Object ‘qryFilter’ already exists.’

If I manually delete the Query and rerun my code, everything works fine.
Would it make more sense to empty the query and then populate it with SQL?

Here is my code (works fine except that I keep getting that ‘query already
exists message’):

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()

On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strTraderCondition As String

Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
DoCmd.DeleteObject acQuery, "qryFilter"
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM Trades"
cat.Views.Append "qryFilter", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
' Build criteria string for Office
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

' Get condition

If Me.optAndTrader.Value = True Then
strTraderCondition = " AND "
Else
strTraderCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryFilter").Command
cmd.CommandText = strSQL
Set cat.Views("qryFilter").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "qryFilter"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

Private Sub optAndTrader_Click()
' Toggle option buttons
If Me.optAndTrader.Value = True Then
Me.optOrTrader.Value = False
Else
Me.optOrTrader.Value = True
End If
End Sub

Private Sub optOrTrader_Click()
' Toggle option buttons
If Me.optOrTrader.Value = True Then
Me.optAndTrader.Value = False
Else
Me.optAndTrader.Value = True
End If
End Sub

Finally, I already tried to delete the Query with this snippet of code:
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
DoCmd.DeleteObject acQuery, "qryFilter"
Exit For
End If
Next qry

That actually doesn’t do anything, and I can’t figure out why that doesn’t
work.

I’d sincerely appreciate help with this.

Thanks everyone!
Ryan--
 
J

John W. Vinson

If I manually delete the Query and rerun my code, everything works fine.
Would it make more sense to empty the query and then populate it with SQL?
Click to expand...

No.

A query *has no independent existance*. If you delete records from the query,
you're deleting records from the underlying table (permanently and without an
Undo option!)

And you don't "populate a query" with SQL. The SQL *IS* the query; that's how
it's stored in Access, as a SQL string (and in a binary compiled form derived
from the SQL string). The query is *NOT* the records that are displayed; the
query is a view, a way to select fields and records from a table or tables.
 
J

JimBurke via AccessMonster.com

I didn't look through all of your code, but it sounds like you're defining a
query to access when you could just be running the SQL and not actually
creating a query. Is there a reason you're creating a query, or am I
misinterpreting? You can just execute an SQL statement without actually
defining a new query.
I’m wondering if it makes sense to delete a query which I build on the fly
each time I use. If I start with no Query, and then run my code, the Query
is loaded with SQL, which comes from several objects on a Form. This is all
driven by VBA. In the original example, the author did NOT delete the query,
but with the query present, I keep getting messages like this:
‘Error Description: Object ‘qryFilter’ already exists.’

If I manually delete the Query and rerun my code, everything works fine.
Would it make more sense to empty the query and then populate it with SQL?

Here is my code (works fine except that I keep getting that ‘query already
exists message’):

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()

On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strTraderCondition As String

Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
DoCmd.DeleteObject acQuery, "qryFilter"
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM Trades"
cat.Views.Append "qryFilter", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
' Build criteria string for Office
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

' Get condition

If Me.optAndTrader.Value = True Then
strTraderCondition = " AND "
Else
strTraderCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryFilter").Command
cmd.CommandText = strSQL
Set cat.Views("qryFilter").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "qryFilter"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

Private Sub optAndTrader_Click()
' Toggle option buttons
If Me.optAndTrader.Value = True Then
Me.optOrTrader.Value = False
Else
Me.optOrTrader.Value = True
End If
End Sub

Private Sub optOrTrader_Click()
' Toggle option buttons
If Me.optOrTrader.Value = True Then
Me.optAndTrader.Value = False
Else
Me.optAndTrader.Value = True
End If
End Sub

Finally, I already tried to delete the Query with this snippet of code:
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
DoCmd.DeleteObject acQuery, "qryFilter"
Exit For
End If
Next qry

That actually doesn’t do anything, and I can’t figure out why that doesn’t
work.

I’d sincerely appreciate help with this.

Thanks everyone!
Ryan--
Click to expand...
 
R

ryguy7272

Thanks John and Jim! I got it worked out. For anyone who is interested,
here is the final version of code:

Private Sub cmdRun_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strTraderCondition As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFilter")

' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
' Build criteria string for Office
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

' Get condition
If Me.optAndTrader.Value = True Then
strTraderCondition = " AND "
Else
strTraderCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And (Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ");"

' Open the Query
qdf.SQL = strSQL
DoCmd.OpenQuery "qryFilter"
Set qdf = Nothing
Set db = Nothing
End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


JimBurke via AccessMonster.com said:
I didn't look through all of your code, but it sounds like you're defining a
query to access when you could just be running the SQL and not actually
creating a query. Is there a reason you're creating a query, or am I
misinterpreting? You can just execute an SQL statement without actually
defining a new query.
I’m wondering if it makes sense to delete a query which I build on the fly
each time I use. If I start with no Query, and then run my code, the Query
is loaded with SQL, which comes from several objects on a Form. This is all
driven by VBA. In the original example, the author did NOT delete the query,
but with the query present, I keep getting messages like this:
‘Error Description: Object ‘qryFilter’ already exists.’

If I manually delete the Query and rerun my code, everything works fine.
Would it make more sense to empty the query and then populate it with SQL?

Here is my code (works fine except that I keep getting that ‘query already
exists message’):

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()

On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strTraderCondition As String

Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
DoCmd.DeleteObject acQuery, "qryFilter"
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM Trades"
cat.Views.Append "qryFilter", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
' Build criteria string for Office
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

' Get condition

If Me.optAndTrader.Value = True Then
strTraderCondition = " AND "
Else
strTraderCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo] And Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryFilter").Command
cmd.CommandText = strSQL
Set cat.Views("qryFilter").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "qryFilter"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

Private Sub optAndTrader_Click()
' Toggle option buttons
If Me.optAndTrader.Value = True Then
Me.optOrTrader.Value = False
Else
Me.optOrTrader.Value = True
End If
End Sub

Private Sub optOrTrader_Click()
' Toggle option buttons
If Me.optOrTrader.Value = True Then
Me.optAndTrader.Value = False
Else
Me.optAndTrader.Value = True
End If
End Sub

Finally, I already tried to delete the Query with this snippet of code:
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "qryFilter" Then
DoCmd.DeleteObject acQuery, "qryFilter"
Exit For
End If
Next qry

That actually doesn’t do anything, and I can’t figure out why that doesn’t
work.

I’d sincerely appreciate help with this.

Thanks everyone!
Ryan--
Click to expand...

--
Jim Burke




.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Build Dynamic Query from Form 6
Query to Filter by Dates, Using Form 7
Query to Include cboSymbols or ‘*’ 4
How can I filter by Dates on a Form? 6
Seeking Help With VBA and SQL Instant Query 8
Using a Form to Create a Query. Problem with VBA code... 2
Order of Operations in a Query 3
Run-time error where 'object already exists' 3

Top