Check if query exists using VBA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Check if query exists using VBA

Author

Topic

pepig
Starting Member

10 Posts

Posted - 2007-08-30 : 17:54:30

Hi,

Is there any VBA method checks if a query exists ?

I have a procedure that creates a query. If I run it the first time, no Problem. If I run it a second time, an error message pops out because a query already exists and it won't overwrite it.

But then if I add a statement that tells the procedure to delete the existing query and the query doesn't exist, then BANG! anoter error occurs.

I'd like to do (pseudo-code)

Sub procThatCreatesQuery1()
If [Query1] already exists Then delete [Query1] End If
Create [Query1]
End Sub

Any existing stuff in VBA out there? or any suggestion for a workaround ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 17:56:56

Where is the query stored? In Access or SQL Server?

If in Access, use the DAO.Queries collection.

E 12°55'05.25"
N 56°04'39.16"

pepig
Starting Member

10 Posts

Posted - 2007-08-30 : 18:07:26

It's Access that I'm using.

Can you please be more precise ?

I typed "DAO.queries" in the Acces Help. It does talk about methods to create or delete queryDefs but I found nothing about methods that would tell wether or not a quryDef already exists. And that's precisely what I'm missing.
--Pierre

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-08-31 : 10:44:53

Why..."procedure that creates a query. "....normal operation is just to execute a previously created query....Why are you creating (and saving) queries on the fly???

pepig
Starting Member

10 Posts

Posted - 2007-08-31 : 12:01:18

Because it's actually a crosstab query where I need to force the column headings to be values of another table that can be edited by the user.

the column headings of the crosstab queries are destinations (and the rows are origins)

and all the destinations are listed in a table. And users need to create and delete destinations so the number of columns of the crosstab query needs to vary accordingly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 15:12:19

Run through the QueryDefs collection and use the .Name property.
If the query in question exists, you will find it.

E 12°55'05.25"
N 56°04'39.16"

pepig
Starting Member

10 Posts

Posted - 2007-08-31 : 16:11:05

... And if it doesn't exist yet, then an error message pops out. So what I'm going to do is treat that error like that:
"If the 'Item Not Found In Collection'-error occurs, then create a dummy query with the wanted name."

Thanks for your time guys.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-01 : 04:04:41

Who told you to use the Item value?

dim db as dao.database, q as dao.querydef, hit byte

FOR EACH q IN db.querydefs
if q.name = "myqueryname" then hit = 1
next

if hit = 1 then print "query found"
else print "query not found"
end

E 12°55'05.25"
N 56°04'39.16"

pepig
Starting Member

10 Posts

Posted - 2007-09-04 : 09:13:58

Great, thanks!

alastair
Starting Member

1 Post

Posted - 2010-07-13 : 09:57:34

Peso has it quite correct - I can never remember the syntax to cycle through a collection, which is why I got here!!!

However I had a similar problem which I solved by deleting and then creating the query, and using on error resume next to deal with deleting a non-existant query.

It is much easier to store the queries in an mdb, but sometimes creating them on the fly is the only option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources