Part of the process of trying to optimize client databases always involves analyzing tables to try and find potential problems. For instance, I like to quickly identify tables containing Attachment and Memo fields. Now if you’re dealing with a simple database it may be conceivable to open each table one by one and look over the fields manually, but this isn’t efficient, can lead to human error, and certainly is not an option for large databases. As such I put together a simply procedure to perform the check for me and I thought I’d share with the community at large.
'---------------------------------------------------------------------------------------
' Procedure : FindTblWFieldsType
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Identify which table contains the specified Field Type
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call FindTblWFieldsType(dbAttachment)
' Call FindTblWFieldsType(dbMemo)
' Call FindTblWFieldsType(dbBoolean)
'
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2015-Oct-03 Initial Release
'---------------------------------------------------------------------------------------
Public Sub FindTblWFieldsType(sFieldType As DataTypeEnum)
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim td As DAO.TableDefs
Dim t As DAO.TableDef
Dim tdfld As DAO.TableDef
Dim fld As Field
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" Then 'ignore system tables
GoTo Continue
End If
For Each fld In t.Fields 'loop through all the fields of the tables
If fld.Type = sFieldType Then 'Find attachment fields
Debug.Print "Processing Table: " & t.Name
Debug.Print vbTab & "Field: " & fld.Name & " is a(n) " & FieldTypeName(fld) & " field."
Debug.Print ""
End If
Next
Continue:
Next
Error_Handler_Exit:
On Error Resume Next
Set tdfld = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: FindTblWFieldsType" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
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
As indicated in the comments of the main procedure, you simply call the sub by:
Call FindTblWFieldsType(dbAttachment)
and it will return (in the VBE immediate window) a result like:
Processing Table: tblTransaction
Field: Receipt is a(n) Attachment field.
Field: RefDoc is a(n) Attachment field.
Processing Table: tblTransactionConfirmation
Field: ConfirmationNotice is a(n) Attachment field.
You might ask what is the point of Allen Browne’s FieldTypeName function. It is used to convert the returned field type (which is numeric) into a comprehensible text. For example, it converts 101 into the word ‘Attachment’ which I use in the reporting. That said since you perform a search for a single field type that you specify, it could be stripped out of my procedure, but I left it to make it as complete and as ‘idiot proof’ as possible to avoid any possible problems.
I hope this help you quickly take control of your database analysis.