More than 255 Fields in Access 2000/2010

Question

I am converting a 20-year old system from DBase IV into Access 2010, via Access 2000, in order to be more suitable for Windows 10. However, I have about 350 fields in the database as it is a parameters table and MS-Access 2000 and MS-Access 2010 are complaining about it. I have repaired the database to removed the internal count problem but am rather surprised that Windows 10 software would have such a low restriction. Does anyone know how to bypass this? Obviously I can break it into 2 tables but this seems rather archaic.

Answer 1

When you start to run up against limitations such as this, it reeks of poor database design.

Given that you state that the table in question is a 'parameters' table, with so many parameters, have you considered structuring the table such that each parameter occupies its own record?

For example, consider the following approach, where ParamName is the primary key for the table:

+----------------+------------+
| ParamName (PK) | ParamValue |
+----------------+------------+
| Param1         | Value1     |
| Param2         | Value2     |
| ...            |            |
| ParamN         | ValueN     |
+----------------+------------+

Alternatively, if there is the possibility that each parameter may have multiple values, you can simple add one additional field to differentiate between multiple values for the same parameter, e.g.:

+----------------+--------------+------------+
| ParamName (PK) | ParamID (PK) | ParamValue |
+----------------+--------------+------------+
| Param1         | 1            | Value1     |
| Param1         | 2            | Value2     |
| Param1         | 3            | Value3     |
| Param2         | 1            | Value2     |
| ...            | ...          | ...        |
| ParamN         | 1            | Value1     |
| ParamN         | N            | ValueN     |
+----------------+--------------+------------+

Answer 2

I had similar problem - we have more than 300 fields in one Contact table on SQL sever linked to Access. You probably do not need to display 255 fields on one form - that would not be user friendly. You can split it to several sub-forms with different underlined queries for each form with less than the limitation. All sub-forms would be linked by the ID.

Sometimes splitting tables as suggested above is not the best idea because of performance.

Answer 3

As Lee Mac described a sample change in structure of a "parameters" table really would be your better choice. You could then define some constants for each of these to be used in code to prevent accidental misspelling later in code in case used in many places.

Then you could create a function (or functions) that take a parameter of what parameter setting you are looking for, it queries the table for that as the key and returns the value. Not being a VB/Access developer, but would think cant overload the functions to have a single function but returning different data types such as string, int, dates, etc. So you may want functions something like

below samples in C#, but principle would be the same.

public int GetAppParmInt( string whatField )
public DateTime GetAppParmDate( string whatField )
public string GetAppParmString( string whatField )

etc...

Then you could get the values by calling the function that has the sole purpose of querying the parameters table for that one key and returns the value as stored.

Hopefully a combination of offered solutions here can help you in your upgrade, even if your parameter table (expanding a bit on Lee Mac's answer) has each data type you are storing to correspond with the "GetAppParm[type]"

ParmsTable
PkID    ParmDescription   ParmInt    ParmDate     ParmString
1       CompanyName                               Your Company
2       StartFiscalYear              2019-06-22
3       CurrentQuarter    4
4... etc.

Then you don't have to worry about changing / data conversions all over the place. They are stored in the proper data type you expect and return that type.