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;

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.

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 !

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;