ms access- performance issue with memo data type on text box

0
votes

I have few text box on my sub-form. User should be able to enter more than 255 characters. So I thought of using memo data type on those text boxes. But then sub form takes 2-3 minutes to open it.

I have changed data type to Text and now I can see significant performance change. It's faster (4-5 seconds).

But now user can't enter more characters. Not even more than 100 characters. I am not sure how to achieve this. Any suggestions?

Thanks,

1
ms-access
Is the subform RecordSource a table or a query? I have never encountered this issue. Did you run compact and repair? I do avoid memo type field because of limitations. If you want to provide db for analysis, I recommend Box.com fileshare site. – June7
I am new to access. I am using a table for subform RecordSource. – user659469
What do you mean by "Did you run compact and repair?" what is that? – user659469
Do a Google search on 'Access database compact and repair'. – June7
@June7, app.box.com/s/osspekgldiku25hddfqtfezn9cpbf6ny please look for table name -"specific". Fields project id, name, project manager, proj control engg are text data type. These field doesn't have any issue. Issue with other text data fields in table. there is main form and subform (sub_specific). The subform will open when you run main form; select specific option in project category dropdown; select any project; select month-year and and click update record button. Now when you enter comments in comments field. Comments fields are having issue with character limit. – user659469

1 Answers

1
votes

This answer expands on discussion in comments below the question.

Never encountered 'record too large' error.

To normalize your database would mean adding new tables that would be dependent (children) related to the parent Specific and Blanket tables. For a starter, Blanket table has fields named Project_1, Project_2 … up to 10 as well as a bunch of other fields with the same naming convention. Normalization would have another table named maybe BlanketDetails: 

+===========+=========+============+==============+=============+  
| BlanketID | Project | Equipment  | Installation | MonForecast |  
+===========+=========+============+==============+=============+  
|         1 | C.91524 | Leonia     | Leonia       | $723,000.00 |  
+-----------+---------+------------+--------------+-------------+  
|         1 | C.91525 | Belleville | Belleville   | $258,000.00 |  
+-----------+---------+------------+--------------+-------------+