By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Determine if query "x" exists

P: 62
Hello all,

I have (hopefully) an easy question for you all. I'm running Access '03 and have created a pile of VBA code to create a form and some queries related to each control in the form. (Basically, it uses structured naming conventions to create a form with a bunch of combo boxes that allow the user to easily add and remove controls... anyway--)

I am using the statement

Expand|Select|Wrap|Line Numbers
  1. With CurrentDb
  2. .QueryDefs.Delete ("qryName")
  3. Set qrydef = .CreateQueryDef("qryName", strSQL)
  4. .Close
  5. End With
to delete existing queries and make new ones, however my code returns an error message when the query doesn't exist when it tries to delete it.

Is there a function that can tell me if a query with a specific name exists that I can use in an "If" statement? Ideally, it would be something like:

Expand|Select|Wrap|Line Numbers
  1. If qryExists("qryName") Then
  2. .QueryDefs.Delete("qryName")
  3. End If
Any help would be greatly appreciated!

Thanks!
Jan 30 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
Hello all,

I have (hopefully) an easy question for you all. I'm running Access '03 and have created a pile of VBA code to create a form and some queries related to each control in the form. (Basically, it uses structured naming conventions to create a form with a bunch of combo boxes that allow the user to easily add and remove controls... anyway--)

I am using the statement

Expand|Select|Wrap|Line Numbers
  1. With CurrentDb
  2. .QueryDefs.Delete ("qryName")
  3. Set qrydef = .CreateQueryDef("qryName", strSQL)
  4. .Close
  5. End With
to delete existing queries and make new ones, however my code returns an error message when the query doesn't exist when it tries to delete it.

Is there a function that can tell me if a query with a specific name exists that I can use in an "If" statement? Ideally, it would be something like:

Expand|Select|Wrap|Line Numbers
  1. If qryExists("qryName") Then
  2. .QueryDefs.Delete("qryName")
  3. End If
Any help would be greatly appreciated!

Thanks!
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. For Each qdf In CurrentDb.QueryDefs
  4.   If qdf.Name = "qryName" Then
  5.     CurrentDb.QueryDefs.Delete "qryName"
  6.      Exit For
  7.   End If
  8. Next
Jan 31 '08 #2

P: 62
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. For Each qdf In CurrentDb.QueryDefs
  4.   If qdf.Name = "qryName" Then
  5.     CurrentDb.QueryDefs.Delete "qryName"
  6.      Exit For
  7.   End If
  8. Next

Works like a charm! Thanks Adezii.
Jan 31 '08 #3

ADezii
Expert 5K+
P: 8,597
Works like a charm! Thanks Adezii.
You are quite welcome.
Jan 31 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.