How to add linked tables objects to this access query output?

[tony007]

Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;

 

[Pat Hartman]

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND MsysObjects.Type In(1, 4, 5, 6) AND MySysObjects.Flags In (2097152, 0, 16, 128)
ORDER BY MsysObjects.Name;

I think 4 is the missing type. The flags test may be messing up your query.

 

[tony007]

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND MsysObjects.Type In(1, 4, 5, 6) AND MySysObjects.Flags In (2097152, 0, 16, 128)
ORDER BY MsysObjects.Name;

I think 4 is the missing type. The flags test may be messing up your query.

thanks for u reply. i get this error. Syntax error. Missing operator !

 

[Pat Hartman]

I'm sure if you tried hard you could have fixed the syntax error -

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type) In (1,4,5,6)) AND ((MsysObjects.Flags) In (2097152,0,16,128)))
ORDER BY MsysObjects.Name;