By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

Determine object type if have name?

P: n/a
MLH
Suppose MyName, a string variable
equals "frmEnterClients". 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)?
Mar 7 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Br
MLH wrote:
Suppose MyName, a string variable
equals "frmEnterClients". 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.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5 Or
(MSysObjects.Type)=6) AND ((Left$([Name],4))<>"MSys"))
ORDER BY MSysObjects.Name;

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

Br@dley
Mar 7 '06 #2

P: n/a
MLH
Thx, Br@dley
I think you're on to something.
What are all those Type 5 entries in MSysObjects with names like
~sq_ffrmAddnlOwnrEntryFrm

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

P: n/a
MLH
Jumpstarted off your SQL, I modified it a bit further...

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~sq_*" And (MSysObjects.Name) Not
Like "MSys*") AND (Not ((MSysObjects.Type)=6 Or (MSysObjects.Type)=8
Or (MSysObjects.Type)=3 Or (MSysObjects.Type)=2 Or
(MSysObjects.Type)=-32757 Or (MSysObjects.Type)=-32758)))
ORDER BY MSysObjects.Type 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)
Mar 7 '06 #4

P: n/a
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**@NorthState.net> wrote in message
news:05********************************@4ax.com...
Jumpstarted off your SQL, I modified it a bit further...

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~sq_*" And (MSysObjects.Name) Not
Like "MSys*") AND (Not ((MSysObjects.Type)=6 Or (MSysObjects.Type)=8
Or (MSysObjects.Type)=3 Or (MSysObjects.Type)=2 Or
(MSysObjects.Type)=-32757 Or (MSysObjects.Type)=-32758)))
ORDER BY MSysObjects.Type 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)

Mar 8 '06 #5

P: n/a
MLH
That really did make it much
more legible. Thx, Terry.
Mar 9 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.