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?

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 

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")