none
Confusing information in Access help MAXIMUM NUMBER OF FIELDS AND DATEDIFF FUNCTION RRS feed

  • Question

  • Thank you to anyone that can help me. I have created many databases in Alpha5 and now will start using Access due to pricing and policies of the other program.

    I have a table for recording the test results of fiber cable. The fiber cable has 144 ends to tests. With the calculated fields and other job information this database (table) is 210 fields wide. Access is giving me a message of 'Too many fields defined'.

    The other databases I created to link to this are an 'operator' database with one field for their names and a 'pass or fail' data base with four fields to allow the user to select 'pass', 'fail' etc. from a drop down box rather than typing.

    I do not understand why I am receiving the error message for too many fields when there are only 210 in the database and 215 in all three databases.

    Follow-up, I have to cut the number of fields (columns) down to 112, I get the error message on the 113th field. NOTE: Error does not appear on entry of these fields. It appears when I try to save the table.

    Second issue:

    I am also trying to calculate time differences for the start and end time of the testing.

    The various help screens point toward using 'datediff' function. This function is not available in the Expression Builder under any of the 'Functions' in the table editor or the form editor. According to the Microsoft help website this function is available 'anywhere you create calculations'.



    • Edited by Randall H Friday, September 23, 2016 4:42 PM Added to the Title for better key word action
    Friday, September 23, 2016 4:01 PM

Answers

  • What is neat as I recreate the table the FORM reconnects to the new fields since I used the same table and field names as before.

    Only the main table disappeared, the FORM and the secondary tables did not.

    The form should reconnect once you rebuild the table. That's because the form still has the field names within its properties.

    If you haven't closed the database or compacted it you can sometimes restore a deleted table if you catch it soon enough. 

    Function RecoverDeletedTables()
    'Purpose  : Restore deleted tables. Must be used before 
    '           compact Or exiting database.
    'DateTime : 10/29/2008 09:38
    'Author   : Microsoft
    '           http://support.microsoft.com/kb/209874
    'Edited By: Bill Mosca
    'Requires : DAO library
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim fld As DAO.Field
        Dim strTableName As String
        Dim strFieldList As String
        Dim strSQL As String
        Dim intCount As Integer
        Dim intTableCt As Integer
    
    
        On Error GoTo err_PROC
    
        Set db = CurrentDb()
    
        For intCount = 0 To db.TableDefs.Count - 1
            strTableName = db.TableDefs(intCount).Name
            If Left(strTableName, 4) = "~tmp" Then
                'Get field names.
                Set rs = db.OpenRecordset("SELECT TOP 1 * FROM [" _
                    & strTableName & "]", dbOpenSnapshot)
                strSQL = "SELECT * INTO [zzz_" & Mid(strTableName, 5) _
                        & "] FROM [" & strTableName & "];"
                strFieldList = ""
                For Each fld In rs.Fields
                    strFieldList = strFieldList & fld.Name & ";"
                Next
    
                If MsgBox("Table contains these fields:" _
                        & vbNewLine & strFieldList _
                        & vbNewLine & "Shall I recover it?", _
                        vbYesNo, "Table Found") = vbYes Then
                    db.Execute strSQL
                    intTableCt = intTableCt + 1
                End If
    
            End If
        Next intCount
    
        If intTableCt = 0 Then
            MsgBox "No recoverable tables found.", vbOKOnly
        Else
            Application.RefreshDatabaseWindow
            MsgBox intTableCt & " tables were restored. " _
                & "All begin With 'zzz_'." _
                    & vbNewLine & "Compacting is suggested " _
                    & "To remove the tables " _
                    & "from memory now that they are restored."
        End If
    
    exit_PROC:
        DoCmd.SetWarnings True
        Set db = Nothing
        Exit Function
    
    err_PROC:
        MsgBox Err.Description
        Resume exit_PROC
    
    End Function


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 26, 2016 3:56 PM

All replies

  • When built-in functions like DateDiff start failing it's usually a sign that your DAO library is missing. Press Ctrl+G to open the code window. Go to Tools>References and look to see if any libraries are marked as MISSING. If one is, uncheck it and add an earlier version.

    But because you are also getting a "too many" error as well, I suspect the database is corrupt. Open a new, blank database and import everything from the old one into it. That usually forces system indices to re-build and clear up such meaningless errors.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, September 23, 2016 6:46 PM
  • Hello and Thank You Bill,

    I wish I had got your message 10 minutes sooner.

    I was able to add fields to the database and they seemed to work as long as I only added 16 at a time.

    When I went to add the last four fields it kept giving me the error message.

    I tried several times from the table design window.

    I tried adding the fields in the table mode by clicking the add field arrow on the far right side of the table.

    when I went to add the fourth field (the last field that is) it gave me the error message and the only option I had was to close the file. It would not accept closing so I closed the program.

    My main table disappeared. Disappeared from the tree on the left totally.

    Is there, please is there, a way to get it back?

    No local backup as I was creating this on my hard drive, not the server.

    Your further assistance would be greatly appreciated.

    Friday, September 23, 2016 7:03 PM
  • This is a road to Dublin question.  You are designing your tables like spreadsheets, not as relational database tables.  The real issue here is not any limit on the number of columns, but the incorrect design of the table.  By having a separate column for each cable end data is being encoded as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    The correct way to model this is for each end to be represented by a separate row in a related table which has a column for the end identifier, i.e. the datum which is currently being modelled as a column heading, and another column for the test value.  Without knowing the details of the reality being modelled I cannot be categorical about what other tables will be required, but these will almost certainly include Cables and Tests tables, the latter being referenced by a foreign key in your restructured 'TestResults' table.  If the start and end times are those of a test for the full set of ends then these will be represented by columns in the Tests table; if they relate to each end individually they will be columns in the TestResults table.

    The same is true of your 'PassOrFail' table, which is also encoding data as column headings.  Again these should be represented by values in rows, not as column headings.  A combo box can, as its RowSource, return rows from a referenced table in which the values 'pass', 'fail' etc are represented as separate rows.

    It is possible to automatically restructure data from a table which encodes data s column headings, and you'll find a simple example as UnencodeColumns.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file takes extracts data from an incorrectly design table and by way of some VBA code inserts it into rows in a set of correctly related tables.

    Ken Sheridan, Stafford, England

    Friday, September 23, 2016 7:44 PM
  • Randall H -

    You are in good hands with Bill Mosca …

    In addition, I would add: some of the Access maxima are delineated in the help file under ‘specifications’. The maximum number of fields in a table is 255. However, since you say some of the fields are calculated, that may change the limit somehow; I didn’t find that in the list.

    Although Access allows calculated fields, ‘good practice’ recommends against them, and suggests that calculations be done in queries. Have you been able to incorporate all "uncalculated" fields?

    That there are four fields for ‘pass’ ‘fail’ ‘etc’ ‘etc’ tells me that you’re treating the table like a spreadsheet. Instead, that table should have 1 field called ‘score’ or the equivalent, containing 1 of the 4 possible scores for each name(?) (not sure what you’re modeling there).

    As for DateDiff: in the Expression Builder, open Built-In Functions | Date/Time and find DateDiff as the 6<sup>th</sup> entry from the top.

    As for losing the table: try creating it with a CREATE TABLE statement in SQL, like this:

    CREATE TABLE newtable
    (
    firstname CHAR,
    lastname CHAR
    );
    

    using some code to overpower the loss of material ...


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, September 23, 2016 7:59 PM
  • Okay, Ken...I'll bite. Please tell this Italian-American what a "road to Dublin" question is.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, September 23, 2016 8:00 PM
  • would that be a Rocky Road to Dublin?

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, September 23, 2016 8:05 PM
  • Hello and thank you Peter,

    I am using Access 2016 with current updates.

    I have almost completed recreating the table, however; this weekend I will look into creating it with the Queries for the calculations rather than calculating in the table. I was just hoping to give the operators a nice compact screen to see all the results as they enter the data. (The finish product was looking great before the table disappeared)

    Fortunately; I copied and pasted my long formulas so I could manipulate them for each color group easier in Excel.

    I am using lookup tables for the repeat information such as 'Pass', 'Fail', and another table for the operators names.

    On the 'DATEDIFF' function. I am in the Expression Builder in the table creation screen.

    The 'Date/Time' function list is:

    • DateSerial
    • Day
    • Hour
    • Minute
    • Month
    • MonthName
    • Second
    • TimeSerial
    • Weekday
    • WeekdayName
    • Year

    This is all the functions that appear in the box.

    Thank you for the SQL table creation advise, however; I am not sure what is going on there. I create the tables in the 'Create Table' screen by selecting the new table and using the 'Design View'.

    thank you again and have a great weekend.

    Friday, September 23, 2016 8:26 PM
  • What is neat as I recreate the table the FORM reconnects to the new fields since I used the same table and field names as before.

    Only the main table disappeared, the FORM and the secondary tables did not.

    Friday, September 23, 2016 8:30 PM
  • Okay, Ken...I'll bite. Please tell this Italian-American what a "road to Dublin" question is.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Well, despite coming from an Irish family myself and being married to a born and bred Irishwoman, I actually picked this up from American users in the old CompuServe Access forum.  It refers to the fact that if, in Ireland, you ask for directions to the road to Dublin, the answer is 'If I were you, I wouldn't start from here'.

    Ken Sheridan, Stafford, England

    Friday, September 23, 2016 9:40 PM
  • Hi,

    For the error Too many fields defined, please visit ACC: "Too Many Fields Defined" Error Message Saving Table. You could find:

    The internal column count that Microsoft Access uses to track the number of fields in the table has reached 255, even though you may have fewer than 255 fields in the table. This can happen because Microsoft Access does not change the internal column count when you delete a field. Microsoft Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify.

    If you get the error again, please try to compact the database.

    Regards,

    Celeste


    Monday, September 26, 2016 7:01 AM
    Moderator
  • In addition to the excellent advice you already have, another reason to restructure your tables is the limitation of the maximum number of characters in a record, which is 4000.
    With so many fields (depending on the field type), you could easily exceed that and then have another problem on your hands.

    https://support.office.com/en-us/article/Access-2010-specifications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854

    Monday, September 26, 2016 12:44 PM
  • Randall,

    Ken is spot on.  Your issue is a design issue at its heart.  Instead of adding more and more columns you need to completely change the way you store your data.

    Now I don't know you current structure, but imagine you currently have something like

    T_TestResults
    *******************
    TestResultID
    TestResultDate
    End1
    End2
    End3
    ...

    you would need to create something along the lines of

    T_Tests
    *******************
    TestID
    TestDate

    T_TestResults
    *******************
    TestResultID
    TestID (Foreign key to relate the data to the T_Test table)
    EndNo
    TestResult

    This way you end up with 4 fields rather than 144.  This design allows for as many ends as you want.

    Look over Ken's sample db and post back with any questions.


    Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Monday, September 26, 2016 2:33 PM
  • Hahaha! My uncle would say, "You can't get there from here."

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 26, 2016 3:24 PM
  • What is neat as I recreate the table the FORM reconnects to the new fields since I used the same table and field names as before.

    Only the main table disappeared, the FORM and the secondary tables did not.

    The form should reconnect once you rebuild the table. That's because the form still has the field names within its properties.

    If you haven't closed the database or compacted it you can sometimes restore a deleted table if you catch it soon enough. 

    Function RecoverDeletedTables()
    'Purpose  : Restore deleted tables. Must be used before 
    '           compact Or exiting database.
    'DateTime : 10/29/2008 09:38
    'Author   : Microsoft
    '           http://support.microsoft.com/kb/209874
    'Edited By: Bill Mosca
    'Requires : DAO library
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim fld As DAO.Field
        Dim strTableName As String
        Dim strFieldList As String
        Dim strSQL As String
        Dim intCount As Integer
        Dim intTableCt As Integer
    
    
        On Error GoTo err_PROC
    
        Set db = CurrentDb()
    
        For intCount = 0 To db.TableDefs.Count - 1
            strTableName = db.TableDefs(intCount).Name
            If Left(strTableName, 4) = "~tmp" Then
                'Get field names.
                Set rs = db.OpenRecordset("SELECT TOP 1 * FROM [" _
                    & strTableName & "]", dbOpenSnapshot)
                strSQL = "SELECT * INTO [zzz_" & Mid(strTableName, 5) _
                        & "] FROM [" & strTableName & "];"
                strFieldList = ""
                For Each fld In rs.Fields
                    strFieldList = strFieldList & fld.Name & ";"
                Next
    
                If MsgBox("Table contains these fields:" _
                        & vbNewLine & strFieldList _
                        & vbNewLine & "Shall I recover it?", _
                        vbYesNo, "Table Found") = vbYes Then
                    db.Execute strSQL
                    intTableCt = intTableCt + 1
                End If
    
            End If
        Next intCount
    
        If intTableCt = 0 Then
            MsgBox "No recoverable tables found.", vbOKOnly
        Else
            Application.RefreshDatabaseWindow
            MsgBox intTableCt & " tables were restored. " _
                & "All begin With 'zzz_'." _
                    & vbNewLine & "Compacting is suggested " _
                    & "To remove the tables " _
                    & "from memory now that they are restored."
        End If
    
    exit_PROC:
        DoCmd.SetWarnings True
        Set db = Nothing
        Exit Function
    
    err_PROC:
        MsgBox Err.Description
        Resume exit_PROC
    
    End Function


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 26, 2016 3:56 PM
  • Nice. Thx.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, September 26, 2016 5:29 PM