How to count number of fields in a table?

Question

I am trying to count number of fields in a table in Access 2010. Do I need a vb script?

Gilbreath

Answer 1

You can retrieve the number of fields in a table from the .Count property of the TableDef Fields collection. Here is an Immediate window example (Ctrl+g will take you there) ...

? CurrentDb.TableDefs("tblFoo").Fields.Count
 13

If you actually meant the number of rows instead of fields, you can use the TableDef RecordCount property or DCount.

? CurrentDb.TableDefs("tblFoo").RecordCount
 11 
? DCount("*", "tblFoo")
 11 

Nonalcoholic

@HansUp, damn I missed that one. I always forget about working in the Immediate Window – Dustidustie

@Dustidustie Your answer made me question whether the question is about field count or row count, so I provided both. – Nonalcoholic

Ok, I think I am dumb... where do I run this command in Access? – Gilbreath

You can use Ctrl+g to open the Immediate window and execute those statements there. But you could use those same techniques in VBA code if you prefer. – Nonalcoholic

Answer 2

Using a query:

'To get the record count
SELECT Count(*) FROM MyTable

In DAO it would look like:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
rst.MoveLast

'To get the record count
MsgBox ("You have " & rst.RecordCount & " records in this table")

'To get the field count
MsgBox ("You have " & rst.Fields.Count & " fields in this table")

Note, it is important to perform the MoveLast before getting the RecordCount.

In ADO it would look like:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("MyDatabaseName.mdb"))

Set rst = Server.CreateObject("ADODB.recordset")
rst.Open "SELECT * FROM MyTable", conn

'To get the record count
If rst.Supports(adApproxPosition) = True Then _
  MsgBox ("You have " & rst.RecordCount & " records in this table")

'To get the field count
MsgBox ("You have " & rst.Fields.Count & " fields in this table")

Dustidustie

Answer 3

Quick and easy method: Export the table to Excel and highlight row 1 to get number of columns.

Immunity