I CANNOT CREATE CALCULATED FIELD BECAUSE OF PROBLEM WITH MaxLocksPerFile

Question

N° Fields

I have a database in Access with about 130 fields.

ACCESS and LOCATION

The Access file is in a shared folder, just because it really is, but only I use it, no one else goes in.

PROBLEM

When I want to create a calculated field the following problem appears concerning MaxLocksPerFile.

When I click OK the following appears:

SOLUTIONS I TRIED

I tried to modify the Registry Editor according to several forums was the solution, but it does not allow me. Consider that you are working on a corporate laptop.

What else can I do to solve my problem please, thank you very much in advance!

Answer 1

Firstly, if you are saying that you have a table with 130 columns (fields), that would suggest that the table is not correctly normalized, and in need of decomposition into a set of related tables.  Secondly, there is no need to include calculated fields in a table.  The appropriate place to compute values is in a computed column in a query, or a computed control in a form or report.

 

A non-normalized table can be decomposed into a set of normalized tables by, having firstly created and related the empty normalized tables, executing a set of 'append' queries in a specific order.  In doing so, rows should be firstly inserted into each referenced table in each relationship, before rows are inserted into the referencing tables.  For an example take a look at DecomposerDemo.zip in my Dropbox public databases folder at:

 

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

 

This little demo imports data from Excel into a temporary master table, which is then decomposed into asset of related tables.

 

The zip archive incudes two Excel files, one in which each cell contains a single value, and is thus normalized to First  Normal Form (1NF), the other where multiple values are stored in a single cell as a comma separated value list.  In the latter case, after importing the data into the master table, the value lists in the table are first parsed into single values at the same column position in multiple rows by a VBA function.  The master table is then decomposed into a set of correctly normalized tables by executing a series of 'append' queries.  A brief description is given at each stage of the process as each query is executed.

Answer 2

Hi,

1. How many records do you have in the table?

This is at least as important as the number of fields.

2. Are you sure that the expression used for the calculated field should work? Just to exclude a misleading error msg.

Servus
Karl

---

Access Forever News DevCon
Access-Entwickler-Konferenz AEK