Determine object type if have name?

[MLH]

Suppose MyName, a string variable
equals "frmEnterClient s". How can I
determine ...

1) if frmEnterClients exists as an object in the database?
2) what type of object is it (tbl, qry, frm, rpt, mac, mod)?

[Br]

MLH wrote:

Suppose MyName, a string variable
equals "frmEnterClient s". How can I
determine ...

1) if frmEnterClients exists as an object in the database?
2) what type of object is it (tbl, qry, frm, rpt, mac, mod)?

One way would be to use the MSysObjects system table:

This will list all the queries in the database:

SELECT DISTINCTROW MSysObjects.Nam e
FROM MSysObjects
WHERE (((MSysObjects. Type)=1 Or (MSysObjects.Ty pe)=5 Or
(MSysObjects.Ty pe)=6) AND ((Left$([Name],4))<>"MSys"))
ORDER BY MSysObjects.Nam e;

You could reverse it by looking for a name and return the type...
--
regards,

Br@dley

[MLH]

Thx, Br@dley
I think you're on to something.
What are all those Type 5 entries in MSysObjects with names like
~sq_ffrmAddnlOw nrEntryFrm

I haven't knowingly created or named any objects beginning with ~sq_f
but there are lots of them in MSysObjects table.

[MLH]

Jumpstarted off your SQL, I modified it a bit further...

SELECT MSysObjects.Nam e, MSysObjects.Typ e
FROM MSysObjects
WHERE (((MSysObjects. Name) Not Like "~sq_*" And (MSysObjects.Na me) Not
Like "MSys*") AND (Not ((MSysObjects.T ype)=6 Or (MSysObjects.Ty pe)=8
Or (MSysObjects.Ty pe)=3 Or (MSysObjects.Ty pe)=2 Or
(MSysObjects.Ty pe)=-32757 Or (MSysObjects.Ty pe)=-32758)))
ORDER BY MSysObjects.Typ e DESC;

This way, the qry dynaset displays only deliberately created objects,
excluding system objects. The types shown seem to be
Type 1, 5, -32761, -32764, -32766 and -32768
(tables, queries, modules, reports, macros and forms respectively)

[Terry Kreft]

Just a suggestion, you're SQL would be more readable if you use the IN
clause e.g.
SELECT [Name], Type
FROM MSysObjects
WHERE [Name] Not Like "~sq_*"
AND [Name] Not Like "MSys*"
AND Type Not In (6,8,3,2,-32757,-32758)
ORDER BY Type DESC;
--

Terry Kreft
"MLH" <CR**@NorthStat e.net> wrote in message
news:05******** *************** *********@4ax.c om...

Jumpstarted off your SQL, I modified it a bit further...

SELECT MSysObjects.Nam e, MSysObjects.Typ e
FROM MSysObjects
WHERE (((MSysObjects. Name) Not Like "~sq_*" And (MSysObjects.Na me) Not
Like "MSys*") AND (Not ((MSysObjects.T ype)=6 Or (MSysObjects.Ty pe)=8
Or (MSysObjects.Ty pe)=3 Or (MSysObjects.Ty pe)=2 Or
(MSysObjects.Ty pe)=-32757 Or (MSysObjects.Ty pe)=-32758)))
ORDER BY MSysObjects.Typ e DESC;

This way, the qry dynaset displays only deliberately created objects,
excluding system objects. The types shown seem to be
Type 1, 5, -32761, -32764, -32766 and -32768
(tables, queries, modules, reports, macros and forms respectively)

[MLH]

That really did make it much
more legible. Thx, Terry.