How to add linked tables objects to this query ?

method · July 14th, 2005, 05:42 AM

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

Code:

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;

AccessMaster · July 30th, 2005, 01:54 AM

I have not tried this but I think it should work, but try linking to the MsysObjects table in the "linked to" MDB.
Then you can use two queries, one for each table, then a third query that "unions" the two queries into a single recordset.

Otherwise you will have to run your query in the MDB where the tables are located and wave the data into a table. The link to the table and "union" the results with you original query.

HTH...

Boyd
Access Based Accounting/Business Solutions developer.