Solved

Count the fields in a table

Posted on 2010-11-30
4
434 Views
Last Modified: 2013-11-27
I am trying to document an existing application, and I want to determine the number of fields in each table.  Is there a way to do this with the TableDefs collection?
0
Comment
Question by:AaronGreene1906
  • 3
4 Comments
 
LVL 10

Expert Comment

by:VTKegan
Comment Utility
This will print to the immediate window the number of records in each table excluding System Tables.
Sub PrintTableCounts()



    Dim Tbl As Object

    Dim rst As DAO.Recordset

    For Each Tbl In CurrentDb.TableDefs

        If Not Tbl.Name Like "*Sys*" Then

            Set rst = CurrentDb.OpenRecordset("Select * From " & Tbl.Name)

            If Not rst.EOF Then

                rst.MoveLast

                Debug.Print Tbl.Name & ":" & rst.RecordCount & "Records"

            Else

                Debug.Print Tbl.Name & ": 0 Records"

            End If

        End If

    Next

    Set Tbl = Nothing

    Set rst = Nothing

End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:VTKegan
Comment Utility
After re-reading the question is looks like you are looking for fields, not records.  My mistake.

Use this code below
Sub PrintTableCounts()



    Dim Tbl As Object

    Dim Counter As Integer

    For Each Tbl In CurrentDb.TableDefs

        Counter = 0

        If Not Tbl.Name Like "*Sys*" Then

            For Each Field In Tbl.Fields

                Counter = Counter + 1

            Next

            Debug.Print Tbl.Name & ": " & Counter & " Fields"

        End If

    Next

    Set Tbl = Nothing



End Sub

Open in new window

0
 
LVL 10

Accepted Solution

by:
VTKegan earned 500 total points
Comment Utility
And after putting some thought into it.  I didn't need the For loop to count.  It is already a property of Fields.

This is a simpler version of the code.
Sub PrintTableCounts()



    Dim Tbl As Object

    For Each Tbl In CurrentDb.TableDefs

        If Not Tbl.Name Like "*Sys*" Then

            Debug.Print Tbl.Name & ": " & Tbl.Fields.Count & " Fields"

        End If

    Next

    Set Tbl = Nothing



End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:nathaniel
Comment Utility
here is a function if you want to know the number/count of fields in a given table:

Function fieldCount(vTable)
    dim db as Database
    dim tdf as TableDef
    set db  = CurrentDb
    set tdf = db.TableDefs(vTable)
    fieldCount = tdf.Fields.Count
End Function

hope that helps
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now