Count fields in a table

[Perfac]

I found ways on the internet to get the result counting the fields of one table.
I do have a query that lists all tables.
I would appreciate to get an exact expression in a query that returns the number of fields per table.


VBA that returns the result will be good.

[Gicu]

You can use a simple VBA function like this:

Public Function fnCountFields (sTable as string) as IntegerDim tdf as DAO.TableDef,db as DAO.Database
Set db=CurrentDb
Set tdf=db.TableDefs(sTable)


fnCountFields=tdf.Fields.Count


Set tdf=nothing
Set db=Nothing
End Function

You would use it in your query:
FieldCount:fnCountFields ([TableNameB])

Cheers,

[June7]

Consider:

Function CountFields(sT As String) As Integer
CountFields = CurrentDb.TableDefs(sT).Fields.Count
End Function

[Gicu]

I tend to always use a variable for CurrentDb as I was getting unexpected things happening with using CurrentDb directly, but it should work in most cases...
https://stackoverflow.com/questions/...using-currentd

Cheers,

[Micron]

I would appreciate to get an exact expression in a query that returns the number of fields per table.

I'm thinking that what is wanted is a way to loop over all tables and count for each?

[Perfac]

[June7]

Let's see the query SQL. Is it pulling from MSysObjects? In which case there is a field that can be referenced in the function call to pass table name.

Fix the VBA code so DIM line not on procedure declaration line. That's a posting error caused by forum. For some reason it drops carriage return between first two lines. I basically have to correct this every time I post code.

[Gicu]

@Perfac: click after Public Function fnCountFields (sTable as string) as Integer and press enter, the Dim tdf as DAO.TableDef,db as DAO.Database
should be on a new line.

Seeing your table design you could probably convert this Select query into a Update query to update the FieldsCountB field with fnCountFields ([TableNameB]):

UPDATE t9CountTables SET FieldsCountB = fnCountFields ([TableNameB])

To do this open your Select query in design view, change the view to SQL and copy and paste from above (after you fix the function in VBA).

Cheers,

[Perfac]

Thanks.

SELECT MSysObjects.Name AS TableNameA, "Table" AS ObjectB, 127 AS Acobty_IDc, 1 AS RecordsTypeC, 2 AS RectypIDbFROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "f_*") AND ((MSysObjects.Type) In (1,4,6)) AND ((Left([Name],4))<>"MSys"));

[June7]

Reference MSysObjects.Name in function call.

Why does query show alias name TableNameA but image of output shows TableNameB?

[Perfac]

I have a separate table where all object names are stored. There is a reason why the query was very slow. I appended it to a table. I also keep a separate table with all the 3000 field names, since I try to never give a second field the same name than another. My issue here is solved, thank you.

[Welshgasman]

Pleas try and understand the code you are supplied with.
How is that you copied and pasted ever going to work.

I was thinking of mentioning it, but then thought 'better not, as it is obvious'

[Perfac]

I am not sure about the right word for my condition. But sorry for sometimes being stupid.
Having been an international chess champion 40 years ago, an accounting degree and having employed a 1000 people over 32 years just don't improve the condition.
This last one, I was just fast asleep, should have seen that.