Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    Unanswered: Count the number of columns (fields) in a table

    HI

    Can anyone help me with what I think is a simple item in Access 2010 and just shows me up to be the numb nuts I really am ..

    I just want to count the number of columns in a table. E.g. the fields.

    Any pointers on that for me, before I top myself..

    thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use:
    Code:
    Function FieldCount(ByVal TableName As String) As Long
    
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        FieldCount = rst.Fields.Count
        rst.Close
        Set rst = Nothing
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hey

    thanks for the super speedy response. that is in VB right? Can you tell me where can I run that from? Do I have to create a form and then a control and assign it, or can I just run it ad hoc from somewhere.

    Also, is that a straigh SQL equivalent?

    Thanks so so much.

  4. #4
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hi

    So I created a button on a form and pasted this code


    Private Sub Form_Current()

    Function FieldCount(ByVal TableName As String) As Long

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
    FieldCount = rst.Fields.Count
    rst.Close
    Set rst = Nothing

    End Function
    End Function

    But I get a compile error. I would really like the equivalent SQL for this is possible but if this is the way to go, that is fine.

    clues?


  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The error you get is because you cannot inclue a finction in another procedure:
    Code:
    Private Sub Form_Current()
    
    Function FieldCount(ByVal TableName As String) As Long
    
    Dim rst As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
    FieldCount = rst.Fields.Count
    rst.Close
    Set rst = Nothing
    
    End Function
    End Function
    Should be:
    Code:
    Private Sub Form_Current()
    
        ' Place here the code you want to be executed on the Current event for the form.
    End Sub
    
    Function FieldCount(ByVal TableName As String) As Long
    
        Dim rst As DAO.Recordset
    
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        FieldCount = rst.Fields.Count
        rst.Close
        Set rst = Nothing
    
    End Function
    However, using a VBA function in a query is not very difficult:

    1. Create a new module in the database.

    2. Paste the following code into it:
    Code:
    Public Function FieldCount(ByVal TableName As Variant) As Variant
    
        Dim rst As DAO.Recordset
    
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        FieldCount = rst.Fields.Count
        rst.Close
        Set rst = Nothing
    
    End Function
    3. Save the Module and compile the project.

    4. You can now use the function FieldCount in a query expression.
    Have a nice day!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you need to do this in code?
    can you not just open the table in design mode and physically count them?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hey... thank you so much for posting such a complete reply. Not all of that makes sense to me but I am going to work through this best I can.

    Will report back!

  8. #8
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Yes I can physically count them. However I want to ultimate put this into a calulation to give me he % of un populated fields, so I think I need to capture this programatically.

    Agree?

  9. #9
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    OK... so I created the module and called it module1. (Does that name matter, as I am callilng the function WITHIN the module, right?)

    I altered TABLENAME to the name of the actual table. Is that right?


    Public Function FieldCount(ByVal CTO_NRC As Variant) As Variant

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset(CTO_NRC, dbOpenSnapshot)
    FieldCount = rst.Fields.Count
    rst.Close
    Set rst = Nothing

    End Function

    I then created a query with

    SELECT FieldCount AS Expr1
    FROM CTO_NRC;

    But when I run it it comes backwith Enter Parameter Value and asks for Fieldcount.

    I know this hurts but take heart, it is waaaay worse for me.

    Any more clues?

  10. #10
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Also... when I hit run, from within the VBA editor, for that function it come back asking me for the macro name... is that right?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you'd only need that information if you are adding or removing columns on the fly
    if you aren't doing that then you don't need to.

    granted its good practice if you think the design may change over time to use actual meta data rather than hard coding a value. however it strikes me that its something to implement if its quick and easy to do otherwise press on with moire important tasks. if you have the time then do it programatically.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Raddle View Post
    OK... so I created the module and called it module1. (Does that name matter, as I am callilng the function WITHIN the module, right?)

    I altered TABLENAME to the name of the actual table. Is that right?


    Public Function FieldCount(ByVal CTO_NRC As Variant) As Variant

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset(CTO_NRC, dbOpenSnapshot)
    FieldCount = rst.Fields.Count
    rst.Close
    Set rst = Nothing

    End Function
    No. "TableName" is a parameter that you will pass to the function when calling it (FieldCount works for any table or query), so keep the function as it is:
    Code:
    Public Function FieldCount(ByVal TableName As Variant) As Variant
    
        Dim rst As DAO.Recordset
    
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        FieldCount = rst.Fields.Count
        rst.Close
        Set rst = Nothing
    
    End Function
    Quote Originally Posted by Raddle View Post
    I then created a query with

    SELECT FieldCount AS Expr1
    FROM CTO_NRC;

    But when I run it it comes backwith Enter Parameter Value and asks for Fieldcount.
    As explained just before, you pass the name of the table to the function when you call it:
    Code:
    SELECT FieldCount("CTO_NRC") AS Expr1
    FROM <WhatEverYouWantProvidedItExists>;
    Be aware that SQL is a set-oriented language. Such a query will return one row for every row found in "<WhatEverYouWantProvidedItExists>" (which should be the name of a table). If you want to retrieve only one row in the query, you can use (here using the system table MSysObjects):
    Code:
    SELECT TOP 1 FieldCount("CTO_NRC") AS Expr1
    FROM MSysObjects;
    Quote Originally Posted by Raddle View Post
    Also... when I hit run, from within the VBA editor, for that function it come back asking me for the macro name... is that right?
    If you want to call the function in VBA, you also need to pass the name of the table as parameter:
    Code:
    Sub GetTableFieldCount()
    
        Dim lngCount As Long
        
        lngCount = FieldCount("CTO_NRC")
        
    End Sub
    Or from the Immediate Window of the VBA Editor:
    Code:
    Debug.Print FieldCount("CTO_NRC")
    Quote Originally Posted by Raddle View Post
    I know this hurts but take heart, it is waaaay worse for me.
    Never mind, you're welcome!
    Have a nice day!

  13. #13
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hey Sinndho!

    Firstly... hope I didn't say the wrong thing with my 'it's worse for me' comment. You are quite right what I should have said is, hey thank you sooo much for your kind and patient help. Which I really do mean!!!!!

    Second, hehehehe from the immedate window Debug.Print FieldCount("CTO_NRC") works perfectly. And the answer is 194 BTW

    Third, I need to get my head around 'calling' the function. I have only used Sub / End sub before, and don't know where to paste the function code. I would like to for a field on a form to just show the answer but that is for another time perhaps.

    Fourth, how do you get your code pasting, to show up in that nice lil' box in these threads?

    Last, thank you, thank you again for your excellent and patient help. I really appreciate it. Owe you one.

  14. #14
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    OK so I have been fiddling about (yes, working!!) with your helpful hints and now everything is right in the world.

    I have a some nice labels which just give me the field count and I am closer to understanding how to use modules. They are just ways to group things? IN themselves they don't have any realy function or meaning?

    And you have to compile after messing about with modules.

    Love the SELECT TOP 1 FieldCount("CTO_NRC") AS Expr1
    FROM MSysObjects; .. gig.

    Literally can't thank you enough.


  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Raddle View Post
    Hey Sinndho!

    Firstly... hope I didn't say the wrong thing with my 'it's worse for me' comment. You are quite right what I should have said is, hey thank you sooo much for your kind and patient help. Which I really do mean!!!!!
    No need to apologize, it's okay. No offense taken.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •