I thought I’d share a simple function that can iterate through a database’s tables to identify those containing ‘complex’ fields.
What Are ‘Complex’ Fields?
Simply put those, that use magic behinds the scene to store multiple values in hidden system tables. In Microsoft’s words:
Which Fields Constitute Complex/Multi-Valued Fields?
Basically, Attachments and Multi-Valued fields.
Why Would We Want To Identify These Fields?
I’ve touched upon this in the past, MVF are EVIL. You never want to use hidden feature like these. You want to build your own related table structure that you are in control of. MVF can’t be upsized, can’t be used in union queries, … Attachments lead to database bloating and are always ill-advised except for very limited usages!).
Hence, when I take over a database, I run a series of checks, amongst others, identifying MVF so I can review them to see if they legitimately have their place or should be replace properly.
The Code
The code is pretty straightforward and iterates through each database table (excluding system tables) and then loops through each field to review the properties and prints out those that are ‘Complex’.
'---------------------------------------------------------------------------------------
' Procedure : MVF_Find
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Identify table fields that use Complex data types (attachments, MVF)
' so they can be eliminated!
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' Call MVF_Find
' Returns -> a listing of fields in the VBE Immediate Window
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2022-12-19 Initial Release
'---------------------------------------------------------------------------------------
Sub MVF_Find()
'https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/field2-iscomplex-property-dao
'https://github.com/MicrosoftDocs/office-developer-client-docs/blob/main/docs/access/desktop-database-reference/datatypeenum-enumeration-dao.md
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tbl As DAO.TableDef
Dim fld2 As DAO.Field2
Dim sSQL As String
sSQL = "SELECT MsysObjects.Name AS [ObjectName]" & vbCrLf & _
" FROM MsysObjects" & vbCrLf & _
" WHERE (((MsysObjects.Name Not Like '~*') " & _
" AND (MsysObjects.Name Not Like 'MSys*') " & _
" AND (MsysObjects.Name Not Like 'f_*'))" & vbCrLf & _
" AND (MsysObjects.Type In (1, 6)))" & vbCrLf & _
" ORDER BY MsysObjects.Name;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
Do While Not .EOF
Debug.Print ![ObjectName]
Debug.Print String(50, "-")
Set tbl = db.TableDefs(![ObjectName])
For Each fld2 In tbl.Fields
If fld2.IsComplex = True Then 'indicates whether the specified field is a multi-valued data type MVF, Attachment
Debug.Print , fld2.Name, FieldTypeName(fld2)
End If
Next fld2
Debug.Print
.MoveNext
Loop
End If
End With
Error_Handler_Exit:
On Error Resume Next
Set tbl = Nothing
Set fld2 = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: MVF_Find" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Helper Function
To return a plain English field type I am using Allen Browne’s FieldTypeName function.
Function FieldTypeName(fld As DAO.Field) As String
'Purpose: Converts the numeric results of DAO Field.Type to text.
'Source/Copyright: Allen Browne
'URL: http://allenbrowne.com/func-06.html
Dim strReturn As String 'Name to return
Select Case CLng(fld.Type) 'fld.Type is Integer, but constants are Long.
Case dbBoolean: strReturn = "Yes/No" ' 1
Case dbByte: strReturn = "Byte" ' 2
Case dbInteger: strReturn = "Integer" ' 3
Case dbLong ' 4
If (fld.Attributes And dbAutoIncrField) = 0& Then
strReturn = "Long Integer"
Else
strReturn = "AutoNumber"
End If
Case dbCurrency: strReturn = "Currency" ' 5
Case dbSingle: strReturn = "Single" ' 6
Case dbDouble: strReturn = "Double" ' 7
Case dbDate: strReturn = "Date/Time" ' 8
Case dbBinary: strReturn = "Binary" ' 9 (no interface)
Case dbText '10
If (fld.Attributes And dbFixedField) = 0& Then
strReturn = "Text"
Else
strReturn = "Text (fixed width)" '(no interface)
End If
Case dbLongBinary: strReturn = "OLE Object" '11
Case dbMemo '12
If (fld.Attributes And dbHyperlinkField) = 0& Then
strReturn = "Memo"
Else
strReturn = "Hyperlink"
End If
Case dbGUID: strReturn = "GUID" '15
'Attached tables only: cannot create these in JET.
Case dbBigInt: strReturn = "Big Integer" '16
Case dbVarBinary: strReturn = "VarBinary" '17
Case dbChar: strReturn = "Char" '18
Case dbNumeric: strReturn = "Numeric" '19
Case dbDecimal: strReturn = "Decimal" '20
Case dbFloat: strReturn = "Float" '21
Case dbTime: strReturn = "Time" '22
Case dbTimeStamp: strReturn = "Time Stamp" '23
'Constants for complex types don't work prior to Access 2007 and later.
Case 101&: strReturn = "Attachment" 'dbAttachment
Case 102&: strReturn = "Complex Byte" 'dbComplexByte
Case 103&: strReturn = "Complex Integer" 'dbComplexInteger
Case 104&: strReturn = "Complex Long" 'dbComplexLong
Case 105&: strReturn = "Complex Single" 'dbComplexSingle
Case 106&: strReturn = "Complex Double" 'dbComplexDouble
Case 107&: strReturn = "Complex GUID" 'dbComplexGUID
Case 108&: strReturn = "Complex Decimal" 'dbComplexDecimal
Case 109&: strReturn = "Complex Text" 'dbComplexText
Case Else: strReturn = "Field type " & fld.Type & " unknown"
End Select
FieldTypeName = strReturn
End Function
Usage Example
To use the function you can either place your cursor within the function and press F5, or type
Call MVF_Find
in the Immediate Window and press enter to execute it. Either way, it will return something along the lines of:
Contacts
--------------------------------------------------
Department Complex Text
CompanyPicture Attachment
Projects
--------------------------------------------------
Deadlines Complex Text
ProjectManagers Complex Long
Specifications Attachment
So now you know exactly which tables and fields need to be properly analyzed and most probably replaced.
Long Text with Append Only (Column History)
After verification with the Dev Team, the IsComplex property does NOT return true for Long Text (Memo) with the Append Only property set to Yes. The reasoning is that such fields do not return their Value property, thus the information isn’t accessible via automation, thus they don’t return that they are complex. This is yet one more glaring omission by the Access Dev Team! So in reality the IsComplex property is not valid as such columns are indeed Complex, yet it returns False! What a MESS! So the IsComplex property works for some ‘complex’ fields, but not all. So what’s the point exactly?!
This is just one more clear example of the issues with such fields and the way that you can’t work with them like a properly built relational 1-n data set that you create yourself!
There are ways of retrieving the related data, but this involves messing around with system tables and this is never a good idea, nor should it even be required. You simply do not want to mess around with system tables!
The moral of the story remains always the same, don’t use them in the first place and if you find them in databases you take over, strongly consider replacing with a properly normalized structure of you’re own creation that can thus be fully automated and upsized should the need arise!
