Enumerate only "true" queries 
Author Message
 Enumerate only "true" queries

I am trying to generate a list of user-generated queries to populate a combo
box in Access 2000, and am running into a problem.  I keep getting a list
that includes all of the "on the fly queries", i.e. the ones generated by
drop-down lists, etc.

I could get around that in Access 97 by testing the querydef.type for
dbQprepare+dbQunprepare, but that doesn't fly in 2000.  The querydef.prepare
only applies to ODBC tables, so I am not sure what else to test for.  I am
currently stuck with checking for a specific naming convention (exclude
names starting in "~sq"), but surely there must be something more reliable
than that.

Any ideas?



Sun, 11 Jan 2004 04:20:27 GMT  
 Enumerate only "true" queries
In A97, I use a Query with the following SQL String:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=5));

and I get all the Queries as seen in the Query tab of the Database window
(i.e. only the actual queries saved by the developer / users).

HTH
Van T. Dinh


Quote:
> I am trying to generate a list of user-generated queries to populate a
combo
> box in Access 2000, and am running into a problem.  I keep getting a list
> that includes all of the "on the fly queries", i.e. the ones generated by
> drop-down lists, etc.

> I could get around that in Access 97 by testing the querydef.type for
> dbQprepare+dbQunprepare, but that doesn't fly in 2000.  The
querydef.prepare
> only applies to ODBC tables, so I am not sure what else to test for.  I am
> currently stuck with checking for a specific naming convention (exclude
> names starting in "~sq"), but surely there must be something more reliable
> than that.

> Any ideas?



Sun, 11 Jan 2004 08:08:34 GMT  
 Enumerate only "true" queries
That seems to work.  Interestingly, in Access 2000,  MSysObjects has a field
called Flags which seems to be exactly like the old querydef.type field: all
of the "on the fly" ones have a type of 3, which once again evaluates to
dbQprepare+dbQunprepare (dbQprepare=1, dbQunprepare=2).  That actually might
be quicker for you than scanning for like "~*".

Thanks for the tip!!!!



Quote:
> In A97, I use a Query with the following SQL String:

> SELECT MSysObjects.Name
> FROM MSysObjects
> WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=5));

> and I get all the Queries as seen in the Query tab of the Database window
> (i.e. only the actual queries saved by the developer / users).

> HTH
> Van T. Dinh



> > I am trying to generate a list of user-generated queries to populate a
> combo
> > box in Access 2000, and am running into a problem.  I keep getting a
list
> > that includes all of the "on the fly queries", i.e. the ones generated
by
> > drop-down lists, etc.

> > I could get around that in Access 97 by testing the querydef.type for
> > dbQprepare+dbQunprepare, but that doesn't fly in 2000.  The
> querydef.prepare
> > only applies to ODBC tables, so I am not sure what else to test for.  I
am
> > currently stuck with checking for a specific naming convention (exclude
> > names starting in "~sq"), but surely there must be something more
reliable
> > than that.

> > Any ideas?



Sun, 11 Jan 2004 20:22:13 GMT  
 Enumerate only "true" queries
Flags is also in the MSysObjects in A97.  I think it is the sum of the
QueryDefType and QueryDefState.

Cheers
Van T. Dinh


Quote:
> That seems to work.  Interestingly, in Access 2000,  MSysObjects has a
field
> called Flags which seems to be exactly like the old querydef.type field:
all
> of the "on the fly" ones have a type of 3, which once again evaluates to
> dbQprepare+dbQunprepare (dbQprepare=1, dbQunprepare=2).  That actually
might
> be quicker for you than scanning for like "~*".

> Thanks for the tip!!!!



> > In A97, I use a Query with the following SQL String:

> > SELECT MSysObjects.Name
> > FROM MSysObjects
> > WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=5));

> > and I get all the Queries as seen in the Query tab of the Database
window
> > (i.e. only the actual queries saved by the developer / users).

> > HTH
> > Van T. Dinh



> > > I am trying to generate a list of user-generated queries to populate a
> > combo
> > > box in Access 2000, and am running into a problem.  I keep getting a
> list
> > > that includes all of the "on the fly queries", i.e. the ones generated
> by
> > > drop-down lists, etc.

> > > I could get around that in Access 97 by testing the querydef.type for
> > > dbQprepare+dbQunprepare, but that doesn't fly in 2000.  The
> > querydef.prepare
> > > only applies to ODBC tables, so I am not sure what else to test for.
I
> am
> > > currently stuck with checking for a specific naming convention
(exclude
> > > names starting in "~sq"), but surely there must be something more
> reliable
> > > than that.

> > > Any ideas?



Sun, 11 Jan 2004 22:02:31 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. txt.Span " ,;:.?!", True, True

2. ""Declare" query

3. Enumerating References - "Illegal Function Call"

4. Enumerating "CAPTURE" Devices

5. Enumerating "CAPTURE" Devices

6. commandbars("Main Menu").Visible = True give error

7. IsNumeric("0d132") Returns True

8. "Session variable expire by refreshing the page"?true:false

9. The equivalent of "CanGrow = True"

10. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

11. GetObject("","InternetExplorer.Application") fails in Excel VBA

12. SysCmd 603, "path","path"

 

 
Powered by phpBB® Forum Software