CODEWITHSUNDEEP
vbams-accesserror-handling
Andrew
Andrew

Reputation: 1818

Runtime Issue 3012 Access

Dim db As DAO.Database
Set db = CurrentDb()
Dim qd As DAO.QueryDef
Dim sqlQry As String

sqlQry = "SELECT x,y,z " & _
"INTO MyTable2 " & _
"FROM mytable1 " & _
"WHERE ((Condition1) And (Condition2)) "

Set qd = db.CreateQueryDef("MyQueryName", sqlQry) 
DoCmd.OpenQuery (qd.Name)
Set qd = Nothing
Set db = Nothing
End Sub

Hi I am simply trying to run a query in Access VBA and keep getting runtime error '3012' object MyQueryName already exists at line:

Set qd = db.CreateQueryDef("MyQueryName", sqlQry)

Now I didn't have:

Set qd = Nothing

and thought this was the issue. but after adding it I still got the error and then remembered that as I had bombed out with the error at qd object it probably still hadn't cleared so ran it with these two lines commented out so it was bypassing and going straight to the object reset:

Set qd = db.CreateQueryDef("MyQueryName", sqlQry) 
DoCmd.OpenQuery (qd.Name)

But afterwards it still gives me this error. Stumped confused etc!

Any ideas from you guys?

Thanks

Andrew

Upvotes: 1

Views: 4637

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

You cannot create a query when one already exists. You can delete the query, checking first that it does exists, or you can update the sql

db.QueryDefs("MyQueryName").SQL= sqlQry

So:

If DLookup("Name", "MSysObjects", "Name= 'MyQueryName'") <> "" Then
    Set qdf = CurrentDb.QueryDefs("MyQueryName")
    qdf.SQL = sqlQry
Else
    Set qdf = CurrentDb.CreateQueryDef("MyQueryName", sqlQry)
End If

Upvotes: 1

Related Questions