Clash Royale CLAN TAG

If Query Already Exists Then Delete the Entire Query

I have a form that creates two queries, exports them to Excel and then deletes them. However, when I hit an error on my exporting, it doesn't make it the deletions. How would I go about checking to see if they already exist? And if they do, delete them so I can re-create them with the new/updated data?

Code so far:

Dim qdfNewQry As Object
Dim qdfNewWS As Object

'//----- qdfNewQry
If Not IsNull(DLookup("myExportQry", "MSysObjects", "Name='myExportQry'")) Then
CurrentDb.QueryDefs.Delete qdfNewQry.Name
Set qdfNewQry = CurrentDb.CreateQueryDef("myExportQry", exportQry)
Else
Set qdfNewQry = CurrentDb.CreateQueryDef("myExportQry", exportQry)
End If

'//----- qdfNewWS
If Not IsNull(DLookup("myExportWS", "MSysObjects", "Name='myExportWS'")) Then
CurrentDb.QueryDefs.Delete qdfNewWS.Name
Set qdfNewWS = CurrentDb.CreateQueryDef("myExportWS", exportWS)
Else
Set qdfNewWS = CurrentDb.CreateQueryDef("myExportWS", exportWS)
End If


I'm getting the error "The expression you entered as a query parameter produced this error: 'myExportQry'" on the line If Not IsNull(DLookup("myExportQry", "MSysObjects", "Name='myExportQry'")) Then

If Not IsNull(DLookup("myExportQry", "MSysObjects", "Name='myExportQry'")) Then

I'm pretty lost it seems. Any help/advice/corrections would be greatly appreciated!

EDIT1:

Just for clarification, I'm wanting to delete the entire query.

1 Answer
1

DLookup uses the following format:

DLookup([Field], [Table], [Criteria])


There's no field in MSysObjects named "MyExportQuery". So, the first argument in DLookup() is wrong.

Also, remember that when you use a reserved word (like "Name"), you need to enclose it in brackets.

I think you want to say,

DLookpup("[ID]", "MSysObjects", "[Name]='myExportQry'")


That should either return a number or a NULL, so that will give you what you need to determine if the query already exists.

The above is untested, but logically it makes sense to me.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.