Can't change data types in existing table - says too many fields defined

[mcomp72]

Hi everyone. I’ve encountered a problem with my database and am not sure what is wrong. I have created a new table with about 185 fields in it. When I first created the table, everything seemed fine. I started building a Form based on it, but realized that I had not set some of the Number fields to the correct type. They were all set as Long Integer by default, and I need some of them to be Single, and a few to be Integer. I went through all of them and changed them to what I wanted them to be, and when I tried to close the Table, I was given an error: “Too many fields defined.” I hit OK, and another window popped up that said: “Errors were encountered during the save operation. Data types were not changed.” There was a Show Help button, so I clicked it, and it gave me this very long response:

This error can be caused by one of the following:

• The maximum number of columns allowed in a table or the maximum number of locks for a single file is exceeded.
• The indexed property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table.
• An expression is not specified in the Expression property of a calculated field.

If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry. However, this is not a recommended option.
If you use Registry Editor incorrectly, you could cause serious problems that require you to reinstall the operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
Make a backup of the registry. Find the MaxLocksPerFile registry value by using the Windows Registry Editor, and then increase the value. The MaxLocksPerFile value is saved as part of the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\ Access Connectivity Engine\Engines\ACE
If the Indexed property of a field and duplicate data is located in the table, reset the Indexed property to the previous setting, or remove duplicate records from the table.

I don’t think it’s a maximum number of columns issue, because I believe that number is 255, so I’m under that. There’s no duplicate data in the table, because the table currently only has one record. There are no calculated fields in my table, so that can’t be it. So the only thing it seems it could be is the maximum number of locks in a single file is exceeded. I have no idea what that even means. Can anyone enlighten me? As for changing the Registry, is that really the only option I have? I don’t mess with my Registry much at all, so I’d really rather not do that unless it’s absolutely necessary. I’m hoping there may be another work-around. Anyone experienced this before?

[mcomp72]

I may have solved the problem, but I now have a different problem. I just tried a "Compact & Repair" on the database, and now it is allowing me to change the data types. Maybe I just tried changing too many of them at once last time? Is there some kind of rule about how many you should change before you save the table?

Also, as I look more closely at the table, I see that the changes I attempted to make last time really were made. Strange.

I also notice that the size of my database file was reduced dramatically after I did the "Compact & Repair". It was over 20 MB before, and now it's down to around 2 MB.

Is it safe to say that I should run "Compact & Repair" on a fairly regular basis?

Here's my new problem: On the Form I setup to input data into a Query that is tied to the Table in question, it now won't let me make any changes on the Form. At the bottom left of the screen whenever I try it, it says "This Recordset is not updateable." It was working fine before, so I'm not sure what I should do now. Please tell me I don't have to delete the Form and remake it??

[June7]

Run C&R after design edit. Run C&R after deleting a lot of data. And then wouldn't hurt to run on a regular basis.

[mcomp72]

Any recommendations about what I should do about not being able to input any data into my Form? I just ran C&R again, but that didn't solve it. I also tried editing the data in the query directly, but I got the same message -- This Recordset is not updateable. I don't believe I have changed anything in the query since it was working correctly, so I'm at a loss as to what to do.

[June7]

When you close the database, does the laccdb automatically delete? If not, try deleting it. If still an issue and you really did not change anything in the query design, corruption is only cause I can suggest. I've never encountered corruption like that but that is the only conclusion I can make. Post your query SQL statement.

[mcomp72]

Yes, the laccdb file was automatically deleted when I just closed the database.

As for the SQL statement: just a warning... it's REALLY long. This is by far the most completed query of the entire database. It's for the creation of the timecard, and it needs to pull data from several different tables and other queries.

SELECT Q_EmployeesAndPositions.NameAndTitle, T_Jobs.JobTitle, T_Jobs.ProductionNumber, [T_Employees on Jobs].Guarantee, [T_Employees on Jobs].Rate, T_Timecards.WeekEnding, T_Employees.SSN, [T_Position Titles & OCC].OCCCode, [T_Position Titles & OCC].PositionTitle, [T_Employees on Jobs].PositionAcctCode, T_Employees.LoanOutName, T_Employees.LoanOutFedID, T_Jobs.WorkArea, T_Timecards.D1WorkState, T_Timecards.D1WorkCity, T_Timecards.D1AcctCode, T_Timecards.D1Date, T_Timecards.D1Loc, T_Timecards.D1Call, T_Timecards.D1Meal1Out, T_Timecards.D1Meal1In, T_Timecards.D1Meal2Out, T_Timecards.D1Meal2In, T_Timecards.D1Wrap, T_Timecards.D1ReRate, T_Timecards.D1OccCode, T_Timecards.D1TotalHours, T_Timecards.D1_1X, T_Timecards.D1_1point5X, T_Timecards.D1_2X, T_Timecards.D1_3X, T_Timecards.D1_MP1, T_Timecards.D1_MP2, T_Timecards.D1_MP3, T_Timecards.D1_PaidAs, T_Timecards.D2WorkState, T_Timecards.D2WorkCity, T_Timecards.D2AcctCode, T_Timecards.D2Date, T_Timecards.D2Loc, T_Timecards.D2Call, T_Timecards.D2Meal1Out, T_Timecards.D2Meal1In, T_Timecards.D2Meal2Out, T_Timecards.D2Meal2In, T_Timecards.D2Wrap, T_Timecards.D2ReRate, T_Timecards.D2OccCode, T_Timecards.D2TotalHours, T_Timecards.D2_1X, T_Timecards.D2_1point5X, T_Timecards.D2_2X, T_Timecards.D2_3X, T_Timecards.D2_MP1, T_Timecards.D2_MP2, T_Timecards.D2_MP3, T_Timecards.D2_PaidAs, T_Timecards.D3WorkState, T_Timecards.D3WorkCity, T_Timecards.D3AcctCode, T_Timecards.D3Date, T_Timecards.D3Loc, T_Timecards.D3Call, T_Timecards.D3Meal1Out, T_Timecards.D3Meal1In, T_Timecards.D3Meal2Out, T_Timecards.D3Meal2In, T_Timecards.D3Wrap, T_Timecards.D3ReRate, T_Timecards.D3OccCode, T_Timecards.D3TotalHours, T_Timecards.D3_1X, T_Timecards.D3_1point5X, T_Timecards.D3_2X, T_Timecards.D3_3X, T_Timecards.D3_MP1, T_Timecards.D3_MP2, T_Timecards.D3_MP3, T_Timecards.D3_PaidAs, T_Timecards.D4WorkState, T_Timecards.D4WorkCity, T_Timecards.D4AcctCode, T_Timecards.D4Date, T_Timecards.D4Loc, T_Timecards.D4Call, T_Timecards.D4Meal1Out, T_Timecards.D4Meal1In, T_Timecards.D4Meal2Out, T_Timecards.D4Meal2In, T_Timecards.D4Wrap, T_Timecards.D4ReRate, T_Timecards.D4OccCode, T_Timecards.D4TotalHours, T_Timecards.D4_1X, T_Timecards.D4_1point5X, T_Timecards.D4_2X, T_Timecards.D4_3X, T_Timecards.D4_MP1, T_Timecards.D4_MP2, T_Timecards.D4_MP3, T_Timecards.D4_PaidAs, T_Timecards.D5WorkState, T_Timecards.D5WorkCity, T_Timecards.D5AcctCode, T_Timecards.D5Date, T_Timecards.D5Loc, T_Timecards.D5Call, T_Timecards.D5Meal1Out, T_Timecards.D5Meal1In, T_Timecards.D5Meal2Out, T_Timecards.D5Meal2In, T_Timecards.D5Wrap, T_Timecards.D5ReRate, T_Timecards.D5OccCode, T_Timecards.D5TotalHours, T_Timecards.D5_1X, T_Timecards.D5_1point5X, T_Timecards.D5_2X, T_Timecards.D5_3X, T_Timecards.D5_MP1, T_Timecards.D5_MP2, T_Timecards.D5_MP3, T_Timecards.D5_PaidAs, T_Timecards.D6WorkState, T_Timecards.D6WorkCity, T_Timecards.D6AcctCode, T_Timecards.D6Date, T_Timecards.D6Loc, T_Timecards.D6Call, T_Timecards.D6Meal1Out, T_Timecards.D6Meal1In, T_Timecards.D6Meal2Out, T_Timecards.D6Meal2In, T_Timecards.D6Wrap, T_Timecards.D6ReRate, T_Timecards.D6OccCode, T_Timecards.D6TotalHours, T_Timecards.D6_1X, T_Timecards.D6_1point5X, T_Timecards.D6_2X, T_Timecards.D6_3X, T_Timecards.D6_MP1, T_Timecards.D6_MP2, T_Timecards.D6_MP3, T_Timecards.D6_PaidAs, T_Timecards.D7WorkState, T_Timecards.D7WorkCity, T_Timecards.D7AcctCode, T_Timecards.D7Date, T_Timecards.D7Loc, T_Timecards.D7Call, T_Timecards.D7Meal1Out, T_Timecards.D7Meal1In, T_Timecards.D7Meal2Out, T_Timecards.D7Meal2In, T_Timecards.D7Wrap, T_Timecards.D7ReRate, T_Timecards.D7OccCode, T_Timecards.D7TotalHours, T_Timecards.D7_1X, T_Timecards.D7_1point5X, T_Timecards.D7_2X, T_Timecards.D7_3X, T_Timecards.D7_MP1, T_Timecards.D7_MP2, T_Timecards.D7_MP3, T_Timecards.D7_PaidAs, T_Timecards.CommentLine1, T_Timecards.CommentLine2, T_Timecards.[1XTotalHours], T_Timecards.[1point5XTotalHours], T_Timecards.[2XTotalHours], T_Timecards.[3XTotalHours], T_Timecards.MP1Total, T_Timecards.MP2Total, T_Timecards.MP3Total, T_Timecards.[1XAcct], T_Timecards.[1XRate], T_Timecards.[1XTotalAmt], T_Timecards.[1point5XAcct], T_Timecards.[1point5XRate], T_Timecards.[1point5XTotalAmt], T_Timecards.[2XAcct], T_Timecards.[2XRate], T_Timecards.[2XTotalAmt], T_Timecards.[3XAcct], T_Timecards.[3XRate], T_Timecards.[3XTotalAmt], T_Timecards.MPAcct, T_Timecards.MPRate, T_Timecards.MPTotalAmt, T_Timecards.TOTALAMT, T_Timecards.BoxRentalTotalAmt, T_Timecards.MileageTotalAmt, T_Timecards.MileageAcct, Right([SSN],4) AS SSNLast4, T_Timecards.JobID_notFK, T_Timecards.EmployeeID_notFK, T_Timecards.PositionID_notFK, T_Jobs.WBSNumber, T_Timecards.TimecardID, Q_EmployeesAndPositions.EmployeesOnJobsID, Q_EmployeesAndPositions.CombinedName
FROM ([T_Production Companies] INNER JOIN (T_Jobs INNER JOIN ((T_Employees INNER JOIN Q_LastNameFirstName ON T_Employees.EmployeeID = Q_LastNameFirstName.EmployeeID) INNER JOIN ([T_Position Titles & OCC] INNER JOIN ([T_Employees on Jobs] INNER JOIN Q_EmployeesAndPositions ON [T_Employees on Jobs].EmployeesOnJobsID = Q_EmployeesAndPositions.EmployeesOnJobsID) ON ([T_Position Titles & OCC].PositionID = [T_Employees on Jobs].PositionID) AND ([T_Position Titles & OCC].PositionID = Q_EmployeesAndPositions.PositionID)) ON T_Employees.EmployeeID = [T_Employees on Jobs].EmployeeID) ON T_Jobs.JobID = [T_Employees on Jobs].JobID) ON [T_Production Companies].CompanyID = T_Jobs.CompanyID) INNER JOIN T_Timecards ON [T_Employees on Jobs].EmployeesOnJobsID = T_Timecards.EmployeesOnJobsID;

[June7]

Could shorten by using wildcard.

SELECT T_Timecards.*, Right([SSN],4) AS SSNLast4, Q_EmployeesAndPositions.NameAndTitle, T_Jobs.JobTitle, T_Jobs.ProductionNumber, T_Jobs.WorkArea, [T_Employees on Jobs].Guarantee, [T_Employees on Jobs].Rate, T_Employees.SSN, [T_Position Titles & OCC].OCCCode, [T_Position Titles & OCC].PositionTitle, [T_Employees on Jobs].PositionAcctCode, T_Employees.LoanOutName, T_Employees.LoanOutFedID, T_Jobs.WBSNumber, Q_EmployeesAndPositions.EmployeesOnJobsID, Q_EmployeesAndPositions.CombinedName
FROM ([T_Production Companies] INNER JOIN (T_Jobs INNER JOIN ((T_Employees INNER JOIN Q_LastNameFirstName ON T_Employees.EmployeeID = Q_LastNameFirstName.EmployeeID) INNER JOIN ([T_Position Titles & OCC] INNER JOIN ([T_Employees on Jobs] INNER JOIN Q_EmployeesAndPositions ON [T_Employees on Jobs].EmployeesOnJobsID = Q_EmployeesAndPositions.EmployeesOnJobsID) ON ([T_Position Titles & OCC].PositionID = [T_Employees on Jobs].PositionID) AND ([T_Position Titles & OCC].PositionID = Q_EmployeesAndPositions.PositionID)) ON T_Employees.EmployeeID = [T_Employees on Jobs].EmployeeID) ON T_Jobs.JobID = [T_Employees on Jobs].JobID) ON [T_Production Companies].CompanyID = T_Jobs.CompanyID) INNER JOIN T_Timecards ON [T_Employees on Jobs].EmployeesOnJobsID = T_Timecards.EmployeesOnJobsID;

Since the form is to enter data into T_Timecards, why do you need to include all those other tables and queries? Looks more like a query for a report. What do the queries provide that are not available in the tables?

Are you positive T_Timecards was updatable through this query before? Suggest you rebuild the query, adding tables/queries in multiple steps until it fails to allow data edits. I suspect [T_Employees on Jobs] may be the issue, just don't know db to be sure.

Advise not to use spaces and punctuation/special characters (underscore only exception) in naming convention.

[CJ_London]

can also shorten it by using aliases

original

SELECT T_Timecards.*, Right([SSN],4) AS SSNLast4, Q_EmployeesAndPositions.NameAndTitle, T_Jobs.JobTitle....
...
...
...
INNER JOIN ([T_Employees on Jobs] INNER JOIN Q_EmployeesAndPositions ON [T_Employees on Jobs].EmployeesOnJobsID = Q_EmployeesAndPositions.EmployeesOnJobsID)

with aliases

SELECT T_Timecards.*, Right([SSN],4) AS SSNLast4, EP.NameAndTitle, T_Jobs.JobTitle....
...
...
...
INNER JOIN ([T_Employees on Jobs] AS EJ INNER JOIN Q_EmployeesAndPositions AS EP ON EJ.EmployeesOnJobsID = EP.EmployeesOnJobsID)

You can set aliases in the query design window by showing the properties window then clicking on a table for that tables properties

If this sql is for an updateable form, I doubt it has every worked for updating - bearing in mind the rule is one table, one form you have a number of tables plus queries - which in themselves may not be updateable. Usual way is to have your form based on the main table you want to update, use subforms for other tables to be updated and combos/listboxes for reference data in other tables.

Also, looking at your fields, it does not look like your data is normalised e.g.

T_Timecards.D1Meal1Out, T_Timecards.D1Meal1In, T_Timecards.D1Meal2Out, T_Timecards.D1Meal2In

by not normalising, you make things extremely difficult for yourself.

[mcomp72]

Since the form is to enter data into T_Timecards, why do you need to include all those other tables and queries? Looks more like a query for a report. What do the queries provide that are not available in the tables?

You're right. I was trying to save myself from making another query, so I was planning to use this one both for updating the form AND for generating a report. I now see that that was a bad idea. I will change this query to ONLY include items that are needed for the form, and then build a new one for the report. Hopefully that solves this.

As for why it's not working now, perhaps I added a piece to the query at some point after I'd been using it that caused it to stop being updatable. I didn't think I had, but maybe -- that's the only thing I can think of.

[mcomp72]

Also, looking at your fields, it does not look like your data is normalised e.g.

T_Timecards.D1Meal1Out, T_Timecards.D1Meal1In, T_Timecards.D1Meal2Out, T_Timecards.D1Meal2In

by not normalising, you make things extremely difficult for yourself.

I'm not clear on why in this case, it would not be considered normalised. Can you explain further? I created T_Timecards to hold any data that would be on a particular timecard, and I want my Form to look basically the same as a paper timecard. Here's a PDF of a paper timecard that I am basing it off of:
https://1.cdn.ep.com/wp-content/uplo..._Time_Card.pdf

There's a row for every day of the week, and for each day, it's possible to have two meal breaks. (Doesn't happen often, but it does if it's a LONG day.) So each day has the potential of two meal breaks, thus Meal1 and Meal2 (and a start time and end time of each). Are you saying that listing the meal breaks on this Table is not doing it in a normalised way? If so, could you let me know how I should do it? Would meal breaks go onto their own table?

[June7]

IMHO, it is a balancing act between normalization and ease of data entry/output. You have to decide how far to take normalization to meet your needs - "normalize until it hurts, denormalize until it works". However, in this case I agree with Ajax that probably need more normalization. The reality of T&A reporting is that digital forms for data entry do not usually replicate the paper version.

My employer utilizes a paperless system - the data entry form presents a row for each day in the pay period. We enter start and end times for each break period.

Have you been able to easily calculate weekly totals with your current design?

[mcomp72]

Gotcha. Well, because I am so new to database design (this is my first!), I may try it with all of the days & meal breaks as part of the same table, since it seems the simplest that way to do it to my brain. I may curse myself later, though!

Have you been able to easily calculate weekly totals with your current design?

I actually first built this timecard in Excel a few years back, so all the code to do the calculations already exists. I just need to bring it into Access and make the required changes, i.e. instead of it reading data from a particular cell, it would now read it from a particular field. Hopefully goes smoothly, but I anticipate a few hiccups along the way.

Good news to report. I remade the query with ONLY items I needed to use the Form, and it is now updatable!

[June7]

Can't just 'bring it into Access' - I expect major hiccups. Can't 'read from a particular field' the way Excel can 'read from a particular cell'. Excel and Access are very different - forget what you know about Excel.

[mcomp72]

Oh -- yikes. Well, all this may have been for nothing. I'll start playing around with that part of it in the next couple of days, and will report back how it's going.

[Micron]

This Recordset is not updateable

If it's not because of any joined query not being updatable, I would expect the Right function to cause an issue if it's being expressed on a bound field. You might want to bookmark this page for future use. In fact, the whole site is a go-to place for many of us.
http://allenbrowne.com/ser-61.html