Solved

Count the fields in a table

Posted on 2010-11-30
4
435 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
ID: 34240508
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
ID: 34240812
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
ID: 34240829
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
ID: 34242464
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

12 Experts available now in Live!

Get 1:1 Help Now