vba adox "too many fields defined"

Posted on 2011-04-28
Last Modified: 2012-06-27
Using vba and adox I managed to built ,dynamicaly a crosstab table ,in order to compare prices and other info from many suppliers .

 Before I reach the 255 field limit of access(even before I reached 150) I receive the error "too many fields define"

Here is a piece of my program :
 (vendornamex,vendorspecs... are variables from a table )
" With tbl1
        .Columns.Append vendornamex
        .Columns.Append vendorspecs
        .Columns.Append vendorprice
      End With
'cat1.Tables.Append tbl1
   Set dbs = CurrentDb
 dbs.TableDefs("suppliercardbasket4").Fields(vendornamex).Required = False
 dbs.TableDefs("suppliercardbasket4").Fields(vendorav).Required = False
 dbs.TableDefs("suppliercardbasket4").Fields(vendorprice).Required = False

 StrSQL = "Alter Table suppliercardbasket4  ALTER COLUMN [" & vendorprice & "] number"
 Set objRS = statConn.Execute(StrSQL)

May be  access is considering  that  i am using an extra field when I am modifying the  Required property to  False?! if so ,Is there a workaround ?

Is there a way to reset the table before adding new set of fields in order for access the real actual number of fields that the table has ?

I really need solution to the too many fields problem ,I need to make access see the real actual
number of fields .Either by closing and reopening the table by adox (if so tell me how) or resetting the count of fields by a vba command .
Question by:fernando50
    LVL 32

    Accepted Solution


    It looks like you're using a mix of DAO, ADOX and SQL DDL in you sample code. I would stay to DAO only if possible. E.g.

    Dim td As DAO.TableDef 
    Set td = New DAO.TableDef
    td.Name = TABLE_NAME
    AppendField td, "vendornamex", dbText
    AppendField td, "vendorprice", dbCurrency
    CurrentDb.TableDefs.Append td

    Open in new window

    Public Sub AppendField(ATableDef As DAO.TableDef, _
      AFieldName As String, _
      AFieldType As DAO.DataTypeEnum, _
      Optional ARequired As Boolean = False)
      Dim f As DAO.Field
      Set f = New DAO.Field
      f.Name = AFieldName
      f.Required = ARequired
      f.Type = AFieldType
      ATableDef.Fields.Append f
    End Sub

    Open in new window


    Author Comment

    ste5an:i will test shortly ,and tell you if it solves the problem

    Author Comment

    ste5an:I tested the code , the fields I am adding are variables from a table and not fixed field names for example "vendorspecs" could be bestbuyspecs or sonyspecs .

     when i am using "AppendField td, "vendorspecs", dbText" I am getting the following error  :
    "" is not a valid name .make sure that it does not include invalid characters or puctuation and that it is not too long is"
    Anyways vendorspecs should be a variable so I removed the double quotes  and I received the following error :
    "Byref argument type mismatch"
    please help
    LVL 32

    Expert Comment

    Can you provide a sample database?

    Author Comment

    ste5an:regarding the sample database the file is too big ?

    Do you have a way to reset the index of access table by code ,in order to make access see the correct actual number of fields?
    LVL 32

    Expert Comment


    you wrote "index". A table can only have 32 indices:

    btw, can you post a concise and complete repo? A sub would be sufficent. btw, what is the file version of your database file?

    Author Comment

    ste5an:I am not sure of the version but i am using  (office 2003  pro). Will include part of my code as .txt attachment .i just want to reach almost the limit of 255 fields (240 may be will do) please help

    Author Closing Comment

    the  problem wasn't solved .The main issue was to find a vba command to make access see the correct number of fields

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now