vba adox "too many fields defined"
Posted on 2011-04-28
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
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 .