1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Cyrus
  • 11
  • 1
  • 2
    Access 2010 predates Windows 10 by many years and is not “Windows 10 software.” It has its limitations and those are quite reasonable. Rarely is there a need for hundreds of columns. – Sami Kuhmonen Jan 31 '20 at 21:18
  • 2
    What kind of data are you storing in a table that requires 350 columns? – Honeyboy Wilson Jan 31 '20 at 22:22
  • I have a sizeable system that has been running for 20 years and had modules added to it regularly. It is a financial package including Sales/Purchase/Nominal and various other modules, e.g. booking system. The table in question is a parameters table and for a system of this size 350 parameters isn't an unreasonable amount of fields for a package that is used by many different people with many different requirements. – Cyrus Feb 04 '20 at 09:58
  • 2
    Access always - and that is since version 1.0 - has had a limitation of 255 fields in a table. So, if you wish to stick with 350, you must use another database. – Gustav Feb 10 '20 at 07:42

3 Answers3

2

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     |
+----------------+--------------+------------+
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • Many thanks for your answer. The problem with each parameter being in a different record is that when in the code the field is not descriptive of what it is. I do have a solution to the design issue and it's not really been poor design as the system which is quite sizeable comes from a technical background where the number of tables was an issue, not the number of fields. I was simply surprised that Access imposed a restriction that even DOS table structures didn't. The solution I have is to have a parameters table for each module but I just hoped there was a way of opening up this limit. – Cyrus Feb 04 '20 at 09:52
  • I´m a bit curios about the > 255 fields topic: As far as I could remember, the dBase IV file format did only support 255 fields. So you should not exceed the Access limit, too. – nabuchodonossor Feb 28 '20 at 11:28
0

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.

Vlado
  • 839
  • 6
  • 16
0

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.

DRapp
  • 47,638
  • 12
  • 72
  • 142