existing query

[Jeff98]

How to combine those two subs?
first check if qurey Q1 already exsits, if it does not, create it, otherwise create it.


THX
Jeff

Public Sub MakeQuery1()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim sqlText As String

Set db = Access.CurrentDb
sqltxt = "Select * from sales_channel"

Set qry = db.CreateQueryDef("Q1", sqltxt)
qry.Close

Set qry = Nothing
Set db = Nothing

End Sub


Public Sub ModifyQuery1()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim sqltxt As String

Set db = Access.CurrentDb
sqltxt = "Select * from sales_channel"

Set qry = db.QueryDefs("Q1")
qry.Sql = sqltxt

Set qry = Nothing
Set db = Nothing


End Sub

 

[PHV]

Something like this ?
Sub MakeOrModifyQuery1()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim sqlText As String
Set db = Access.CurrentDb
sqltxt = "Select * from sales_channel"
On Error Resume Next
Set qry = db.QueryDefs("Q1")
If Err.Number = 0 Then
qry.Sql = sqltxt
Else
Set qry = db.CreateQueryDef("Q1", sqltxt)
End If
qry.Close
Set qry = Nothing
Set db = Nothing
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886