Count of fields in Database

[Suttd via AccessMonster.com]

Hi all,

Hopefully someone will know this one, i need to find out how many fileds
there are within one of the databases I query - i would count but the are
well over a 100 tables and really hoped someone may know a quick way to
retrieve the number of fileds contained within them ?

Thanks in advance

Dave

 

[John Spencer]

You can use the fields count property

Currentdb().TableDefs("NameOfTable").fields.count

I would suggest a simple function built on that basis

Public Function fCountAllFields() As Long
Dim dbAny As DAO.Database
Dim LTableCount As Long
Dim LFieldCount As Long
Set dbAny = CurrentDb()

For LTableCount = 0 To dbAny.TableDefs.Count - 1
If dbAny.TableDefs(LTableCount).Name Like "Msys*" Then
'skip system table
Else
LFieldCount = dbAny.TableDefs(LTableCount).Fields.Count +
LTableCount
End If
Next LTableCount

fCountAllFields = LFieldCount
End Function