Delete Access query object [Resolved]

[RobDog888]

I need to create a query in Access using vb called qryIDCount but
I get an error if the query already exists. So I can either try to
delete the query object or try to update the query to select from
a different table. The query name needs to remain the same if it
exists because it is used in a report. I have tied both methods
and both return errors.

This is the one to alter it if it exists.

If bQryExists = True Then
    sSQL = "ALTER VIEW qryIDCount AS SELECT [" & cboComputer & "_" & cboLogType & "].Message,"
    sSQL = sSQL & " Count([" & cboComputer & "_" & cboLogType & "].Message) AS evtCount"
    sSQL = sSQL & " FROM [" & cboComputer & "_" & cboLogType & "]"
    sSQL = sSQL & " GROUP BY [" & cboComputer & "_" & cboLogType & "].Message;"
    goEnv.Cnn.Execute sSQL
End If

This is the one to delete if exists.

sSQL = "CREATE VIEW qryIDCount AS SELECT [" & cboComputer & "_" & cboLogType & "].Message,"
sSQL = sSQL & " Count([" & cboComputer & "_" & cboLogType & "].Message) AS evtCount"
sSQL = sSQL & " FROM [" & cboComputer & "_" & cboLogType & "]"
sSQL = sSQL & " GROUP BY [" & cboComputer & "_" & cboLogType & "].Message;"
goEnv.Cnn.Execute sSQL

Any ideas?

Thanks in advance for any help.

[Shawn N]

Try this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryIDCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryIDCount]
GO

[RobDog888]

I am using an Access database. Your code is for SQL.
Anything similar for Access?

[RobDog888]

Found it!

sSQL = "DROP VIEW qryIDCount;"
goEnv.Cnn.Execute sSQL