First Published 21 Dec 2022


This article was prompted by a recent web page on the same subject by Daniel Pineault : Find Attachment and MultiValued Fields

Daniel provided code to list all tables with multivalued or attachment fields.
As he stated, this information is very useful when analysing a client database, either to fix issues or prior to upsizing to SQL Server

I have written separate articles about the use (misuse) of these datatypes:
      Multivalued Fields . . . and why you really shouldn't use them!
      Use Column History to store historical data in long text (memo) fields

I use a different method to list all tables with any complex fields (multivalued, attachment or column history memo fields)
So I thought I would share my approach which uses a simple query based on the read only MSysObjects system table.

The MSysObjects table lists all Access objects including the Name, Type and Flags values together with other information not relevant here

All Access tables have one of the following values for the Type field in MSysObjects:

Table	Type
Local	1
Linked ODBC e.g. SQL Server	4
Linked Access	6

As linked ODBC tables cannot include complex fields, we can restrict our query to search to those tables with Type = 1 or 6
We can also filter out system tables (beginning with 'MSys') and attached tables (beginning with 'f_')

The following query lists all local and linked Access tables excluding system and attached tables

SELECT Name, Type, Flags
FROM MSysObjects
WHERE Not ((Name Like 'MSys*') Or (Name Like 'f_*')) AND (Type=1 Or Type=6);



The Flags value give more information about the table type including whether it is user created or a system table and whether it is visible or hidden
. It also identifies tables with complex fields together with their associated deep hidden attached tables

The Flags values for tables with complex fields have one of five possible values: 262144, 262152, 262154, 2359296 and 2359304

So one way to get a list of all complex tables is by using this query:

SELECT Name, Type, Flags
FROM MSysObjects
WHERE (Type=1 Or Type=6) AND Flags In (262144,262152,262154,2359296,2359304);


We can exclude the complex system table MSysResources either by filtering out tables starting with MSys or by removing the 262154 value as in this query:

SELECT Name, Type, Flags
FROM MSysObjects
WHERE (Type=1 Or Type=6) AND Flags In (262144,262152,2359296,2359304);


These rather peculiar Flags values can be perhaps understood more clearly by looking at the HEX equivalent values
NOTE: Hexadecimal (HEX) uses a base 16 number system: 0 to 9 then A to F

Table Type	Flags	Hex(Flags)
Visible Complex	262144	40000
Hidden Complex	262152	40008
System Complex	262154	4000A
Linked Complex	2359296	240000
Hidden Linked Complex	2359304	240008

This shows all complex tables are identified by the hex value '4' in the fifth character from the end

So, a perhaps simpler way to get a list of all complex tables is to filter for that value using this query:

SELECT Name, Type, Flags, Left(Right(Hex([Flags]),5),1) AS HexFlags
FROM MSysObjects
WHERE (Name Not Like 'MSys*') AND (Type=1 Or Type=6) AND Left(Right(Hex([Flags]),5),1)='4';


Having identified the table names, it is then a simple matter to look at the table design in order to identify the complex fields (multivalued, attachment or column history memo) in each of those tables



Feedback

Please use the feedback form below to email me with any comments on this article.

If you find the article useful, please consider giving a donation to help offset the costs of maintaining my website. Thanks



Colin Riddington           Mendip Data Systems                 Last Updated 21 Dec 2022

Return to Code Samples Page

Return to Top