vba adox "too many fields defined"

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 .
Who is Participating?
ste5anSenior DeveloperCommented:

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

fernando50Author Commented:
ste5an:i will test shortly ,and tell you if it solves the problem
fernando50Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

ste5anSenior DeveloperCommented:
Can you provide a sample database?
fernando50Author Commented:
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?
ste5anSenior DeveloperCommented:

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?

fernando50Author Commented:
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
fernando50Author Commented:
the  problem wasn't solved .The main issue was to find a vba command to make access see the correct number of fields
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.