HOW TO delete Query sitting in a closed database ? 1

[marco02]

I am working from an open database (db1). Can I delete a query sitting in a closed database (db2)?
How ???

I hoped:
DoCmd.DeleteObject acQuery, db2.Querydefs(NameOfQryToDelete)
would work but it does not :-(

I would like to delete this query without opening db2 if possible ... i could open db2 if necessary but would prefer not to ... my code is slow enough already !!! ;-)

Thanks! You rule!

Marco

 

[CCLINT]

In a Module in another Access Db, or in VB6, you need to set up another db connection pointing to the db where you want to delete the query. Then you can drop the query as long as it is not in use.

 

[marco02]

Thanks CClint but my connection is there and the DROP statement only works for TABLES and INDEXES...

>> Set db2 = DBEngine(0).OpenDatabase(path2)
>> db2.Execute "DROP TABLE QueryNameToDelete;"

Doesn't work to delete my query. I'm working with DAO btw.

"DoCmd.DeleteObject" and "Drop" doesn't work, what is left to me to delete this darn query in the 2nd database ;-( ?

Thanks a lot for your help!

 

[cheerio]

Your code was: DoCmd.DeleteObject acQuery, db2.Querydefs(NameOfQryToDelete)

You were I think almost there. I have not tested but this should work:

db2.Querydefs(NameOfQryToDelete).Delete

NameOfQryToDelete should either be a variable holding the name or if it is the actual name then surround with quotes.

Ken

 

[marco02]

uuuuuh it was to good to be true

>> db.Querydefs().Delete

is not valid... :-(
thanks anyway,

marco
-desparated-
;-)

 

[CCLINT]

I meant to delete it - drop or whatever - I didn't mean the Drop method.

CurrentDb.QueryDefs.Delete NameOfQryToDelete

 

[CCLINT]

But you could have also done the following if the query is in the same MDB as where this code:

DoCmd.DeleteObject acQuery,"NameOfQryToDelete"

and in my previous post,
CurrentDb.QueryDefs.Delete NameOfQryToDelete
you can change CurrentDb to any Db object.

 

[marco02]

... no wonder why my boss looks at me with this funny face ... I AM REALLY STUPID ! Thanks CClint for your constant effort on my question, it works now (why the heck is there so many different ways to do the same thing in VB ???)
marco.

 

[CCLINT]

1. It happens to all of us - don't say "Stupid". There are other words:
Forgot; "mind block"; "Bad day"; "One of those areas that I just never touched upon in spite of all the many areas that I Do have experience in", etc., etc.

That is why we are all here.
Well, most of us....

2.
"...so many different ways"
That's not VB....