Testing for a queries existence 
Author Message
 Testing for a queries existence

I am running code which creates, uses and then deletes a query.
Occassionally a user will cause an error which kicks them out of the routine
after the create but before the delete. When they get back in and try to
rerun the code it gives the "query already exists" error. What statement
will test for the existence of the query so I can delete it before I call
the create command. I tried "If isloaded" and "if isnull" but neither
worked.
thanks in advance.
David DeMeglio


Mon, 23 Apr 2001 03:00:00 GMT  
 Testing for a queries existence
There are a number of ways: the easiest is to just by default delete any
existing query:
    On error resume next
    db.querydefs.detete "qryMyQuery"
    On Error GoTo ... 'restore any error handler.
    db.createquerydef ("qryMyQuery")
You should also add the deletion code to any exitr code in your procedure
and direct any error handler ther so that even when the user generates an
error the dynamic query will be deleted before the query is exited.

You could also create an unnamed query
    db.createquerydef ("",strSQL) which will not persist after the procedure
and thus you do not have to worry about deleting it.

You could also loop through the querydefs checking for the queryname or trap
the error by trying to set the new querydef ti the same name, but the above
is simplest and most effecient.
Hope this helps,
GR

Quote:

>I am running code which creates, uses and then deletes a query.
>Occassionally a user will cause an error which kicks them out of the
routine
>after the create but before the delete. When they get back in and try to
>rerun the code it gives the "query already exists" error. What statement
>will test for the existence of the query so I can delete it before I call
>the create command. I tried "If isloaded" and "if isnull" but neither
>worked.
>thanks in advance.
>David DeMeglio



Mon, 23 Apr 2001 03:00:00 GMT  
 Testing for a queries existence

Quote:

> I am running code which creates, uses and then deletes a query.
> Occassionally a user will cause an error which kicks them out of the routine
> after the create but before the delete. When they get back in and try to
> rerun the code it gives the "query already exists" error. What statement
> will test for the existence of the query so I can delete it before I call
> the create command. I tried "If isloaded" and "if isnull" but neither
> worked.
> thanks in advance.
> David DeMeglio

This was done in an old Access2.0 database, converted to Access95 and
then converted to Access97, so it should work in any version

   Set MyDB = DBEngine.Workspaces(0).Databases(0)

    ' Check if Query exists, delete if it does
    If (IsNull(MyDB.QueryDefs("sql_Search").Name) = False) Then
        DoCmd.DeleteObject A_QUERY, "sql_Search"
    End If

    Set myquery = MyDB.CreateQueryDef("sql_Search", sql_report)

HTH
LMH rogue(at)channel1 dot com |or| draupner (at) theglobe dot com
WWW http://www.channel1.com/users/rogue
FAQ:    http://members.forfree.at/~larsm
        http://home.att.net/~dashish
        http://www.hammerdata.com/Newsgrp
        http://www.channel1.com/users/rogue/Access/FAQ
        http://members.xoom.com/dashish
ICQ 10300112
Please limit questions/replies to news-group(s)...
Reply address may be foiled to fool spam-bots. Remove the obvious.
I kill all E-Mail from hotmail.com and other known sources of spam.
"I'm an engineer. I make slides that people can't read. Sometimes I eat
donuts."



Mon, 23 Apr 2001 03:00:00 GMT  
 Testing for a queries existence
A few ways of doing this,

1) iterate through the Table documents collection
2) iterate through the Querydefs collection
3) check for the query in the Msysobjects table

Number 1 is the least efficient
Number 2 goes like this

Function DoesQueryExist(QueryName As String) As Boolean
  Dim lodb As Database
  Dim loqdf As QueryDef
  Dim blnRet As Boolean
  blnRet = False
  Set lodb = CurrentDb
  For Each loqdf In lodb.QueryDefs
    If loqdf.Name = QueryName Then
      blnRet = True
      Exit For
    End If
  Next
  DoesQueryExist = blnRet
End Function

Number 3 goes like this

Function DoesQueryExist(QueryName As String) As Boolean
  Dim lodb As Database
  Dim strsql As String
  Dim lorst As Recordset
  strsql = "select count(name) as CountOfName " _
         & "from msysobjects " _
         & "where name = '" & QueryName & "' and type = 5"
  Set lodb = CurrentDb
  Set lorst = lodb.OpenRecordset(strsql)
  DoesQueryExist = CBool(lorst!CountOfName)
End Function

Quote:

>I am running code which creates, uses and then deletes a query.
>Occassionally a user will cause an error which kicks them out of the
routine
>after the create but before the delete. When they get back in and try to
>rerun the code it gives the "query already exists" error. What statement
>will test for the existence of the query so I can delete it before I call
>the create command. I tried "If isloaded" and "if isnull" but neither
>worked.
>thanks in advance.
>David DeMeglio



Mon, 23 Apr 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Testing for existence of a query

2. TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST

3. Testing for Existence of Table

4. Testing for a record already in existence

5. Test for table existence

6. Test for the existence of a table

7. test for existence of a field

8. Testing for table existence

9. Testing for table existence & listing available tables

10. Testing for file existence

11. Test for Contact EMail1Address existence

12. test existence of style

 

 
Powered by phpBB® Forum Software