[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


vba adox "too many fields defined"

Posted on 2011-04-28
Medium Priority
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
  • 5
  • 3
LVL 36

Accepted Solution

ste5an earned 1500 total points
ID: 35489867

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

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

Author Comment

ID: 35499520
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 36

Expert Comment

ID: 35500144
Can you provide a sample database?

Author Comment

ID: 35696047
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 36

Expert Comment

ID: 35696560

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

ID: 35722020
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

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

834 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