You can use a simple VBA function like this:
Code: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,
Consider:
Function CountFields(sT As String) As Integer
CountFields = CurrentDb.TableDefs(sT).Fields.Count
End Function
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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,
I'm thinking that what is wanted is a way to loop over all tables and count for each?I would appreciate to get an exact expression in a query that returns the number of fields per table.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
@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,
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"));
Reference MSysObjects.Name in function call.
Why does query show alias name TableNameA but image of output shows TableNameB?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.