Count the number of fields in a table? 2

[Lost500]

I need a way to return a number value for the number of fields i have in any given table. I have an access database with linked tables to an sql server. thanks for the help!

 

[dhookom]

You can try an expression like:

Currentdb.TableDefs("any given").Fields.Count

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

thanks duane but i'm not sure how to use your code is "any given" suppose to be the table name? and does it go inside the quotations? it tried just inserting the table name and it said invalid use of property and highlighted .Count please advise!

 

[Lost500]

it looks like .count can't be used to count the fields themselves I'm trying different variations and it still says invalid use of property. I really need help on this any advice would be great!

 

[PWise]

Function PrintTableInfo(TableName As String)
Dim mydb As Database
Dim Tbldef As TableDef
Set mydb = CurrentDb
Set Tbldef = mydb.TableDefs(TableName)
PrintTableInfo = Tbldef.Fields.Count
End Function

 

[dhookom]

i have in any given table

It would help if you said I have a table named "tblEmployees" or "tblImport" that I need code to ...

I took your specification literally as "any given" as the table name. I expect that is not exactly what you wanted but that is what you asked for. I also expected that you would be able to substitute your table name. The Count property will return the number of fields. The table name must be in quotes.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

thanks pwise and dhookom I see the confusion in my question and will try to be more specific...

but i have already tried subtituting my table name inside the quotations and it still returns and error invalid use of property i am not familiar with the functions so i am having a hard time debugging it. my code looks excatly like your suggestion please advise .

thank you for your help i really appreciate it.

 

[dhookom]

Where did you use the code? Is it with other code or in a query or a control source or what? Could you copy and paste your code in a reply?

The code uses the DAO object library. You may need to set a reference to the Microsoft DAO xxx object library while in a module.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

thanks i don't have a set way to code this in yet but i've been trying the following the message box is just to see if it's working if there is a better place for it just let me know this is a module:

Option Compare Database

Sub fieldcounter()
Dim fieldcount As String
Set fieldcount = CurrentDB.TableDefs("mytablename").Fields.Count
MsgBox fieldcount
End Sub

 

[PHV]

Don't use Set:

Sub fieldcounter()
Dim fieldcount As String
fieldcount = CurrentDB.TableDefs("mytablename").Fields.Count
MsgBox fieldcount
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In addition to PH's comments. Why are you putting a count (number) into a string variable?

Sub fieldcounter()
  Dim intFieldCount As Integer
  intFieldCount = CurrentDB.TableDefs("mytablename").Fields.Count
  MsgBox intFieldCount
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

Thanks guys but i have tried all of that and i get the error message 'Item not found in this collection.' I really appreciate the help please advise.

 

[dhookom]

Did you check your references? Can you share your most recent code and context?

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

I don't code modules very often just to perform tasks i'm more query oriented, would this be easier in a query? but the last module i used was:

Option Compare Database

Sub Chris()
Dim CurrentDB As Database
Dim ExclFile As Excel.Workbook
Dim Table As Recordset
Dim i As Integer

Set ExclFile = GetObject("C:\Documents and Settings\cpope2\Desktop\Chris ExclFile.xlsx")
Set CurrentDB = DBEngine.Workspaces(0).Databases(0)
Set Table = CurrentDB.OpenRecordset("tablename", dbOpenTable, dbConsistent)

Table.Index = "indexable field name"
For i = 2 To 96
    Table.Seek "=", ExclFile.Sheets(1).Cells(i, 1)
    If Table.NoMatch Then
        MsgBox ExclFile.Sheets(1).Cells(i, 1) & " " & ExclFile.Sheets(1).Cells(i, 2) & " " & i
    Else
        Table.Edit
            Table("fieldname") = ExclFile.Sheets(1).Cells(i, 13)
        
        Table.Update
    End If
Next

I hope thats not too generic i think it might have to do with the references which ones do you suggest trying?

 

[dhookom]

By "Can you share your most recent code and context?" I was referring to your code that used the stuff we were attempting to fix. The code you posted seems to have nothing to do with this issue.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

Oh... anyways I am atempting to use this function to eventually export calculated resulsts into an excel spreedsheet. I haven't built the module yet this is where I am starting. Does the code we have been talking about use any special references? thanks for the help.

 

[dhookom]

Yes, there are specific references. As I stated earlier "The code uses the DAO object library. You may need to set a reference to the Microsoft DAO xxx object library while in a module"

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Lost500 . . .

[blue]The code uses the [purple]DAO object library[/purple]. [green]You may need to set a reference[/green] to the Microsoft DAO xxx object library while in a module.[/blue]

To the be sure [blue]Microsoft DAO x.x Object Library[/blue] is key here. The [blue]Count[/blue] property won't work without it! You've completely skipped over this. .

Before you do anything else check to see if you have the reference. If you had to install it, open any module in the [blue]modules window[/blue]. Call up the [blue]Immediate Window[/blue] and copy/paste the following (of course enter your table name in quotes):

[blue]Debug.Print CurrentDb.TableDefs("[purple][B][I]YourTableName[/I][/B][/purple]").Fields.Count[/blue]

Hit enter and see the field count!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Lost500]

thanks dhookom i've tried checking the microsoft DAO object library but i get an error 'name conflicts with existing module, project, or object library.' I don't know why i don't have anything named that. I'm almost there please help with this last challenge!

 

[dhookom]

It sounds like you already have a DAO library checked in your list of references. You can have only one DAO reference at a time. This is generally the most recent.

Did you try compile your code?

Did you try open the immediate window (Press Ctrl+G) and enter:

? CurrentDb.TableDefs("[i][b]YourTableName[/b][/i]").Fields.Count

Duane
Hook'D on Access
MS Access MVP