0
Background
I am using a MS Access (2010) database to store and access information on certain international projects in the area of education. The data initially came from an EU source, in the form of an Excel datasheet. The data then has been transfered into the Access database. The database has 3900 records, and about 40 or so fields, most of them ordinary text fields.
Two fields, however, are "memo" fields holding larger amounts of text, usually 2000-5000 characters. One field has ordinary (plain) text. The other has the same text, but in RTF format, so that I can use it to highlight certain information, or tidy the text up for better reading.
Here is the actual problem:
This database has an unpleasant tendency to crash and lose data.
I cannot reproduce the behaviour. It just happens, usually (or perhaps solely) when working in one of the two large memo fields, or more exactly, the RTF one, since I do not touch the other one. - For editing the text I use a form that displays both the plain text and the RTF text versions in two columns.
It seems that I can work for about half an hour and edit some records, but then out of a sudden the bad behaviour begins.
Closing the database and compressing it does not help, it seems.
The worst problem is that often when this happens, the record that was opened (in a form I use to display the records) gets messed up: the fields values disappear, and the fields display then either "error" or a row of #######. This happens either only in the RTF memo field, or in both large text fields, or even in all fields.
Does anyone have
an explanation?
a solution?
The system is Windows 7 64bit, on a Lenovo T420 machine, RAM 4 GB, Processor Intel i5-2540M with 2601 MHz, two cores. The hard disk (500 GB) tends to be a little bit too full (20 GB free currently), and I have usually many programmes running in parellel. If this is relevant.
MS Office is, however, 32 bit.
Update: There is a report published on a Microsoft site that crashes may occur when a user tries to insert more than 2000 characters into an indexed field (see comment below). Unfortunately this hint is not helpful in my case. My table has not a single field that is indexed.
Christian Geiselmann
Posted 2017-03-14T17:12:07.683
Reputation: 759
According to this link: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other/ms-access-2007-problems-crashes-when-typing-in-big/f9229a40-980d-4f66-8748-5f13274ce5ab?auth=1 there is proposed solution: Access database may crash if you are trying to insert more than 2k characters into memo field, and this memo field is indexed. In this case just kill index.
– fernando.reyes – 2017-03-14T17:19:11.773Yes, I even read this, too; you are right, I should try this. Actually that's the advantage of the two parallel text fields: I can leave the first one indexed (need this for filtering), and de-index the second, which serves only for reading by humans. - Question: I do not actually "insert" 2000 chars or so (e.g. by copy-pasting them). What I do is slightly editing the text in the RTF memo field, e.g. highlighting a sentence, or adding paragraph marks to structure the text. - Would that count as "inserting 2k chars"? – Christian Geiselmann – 2017-03-15T18:33:01.443
No, de-indexing is not the problem-solver. See the update at the bottom of the original question. – Christian Geiselmann – 2017-03-15T22:31:33.807
How do you enter the data into the database? With a MSAccess form or some other way? I think that is an important thing to consider here – fernando.reyes – 2017-03-15T22:46:58.490
Do you have all the service packs and patches for office 2010 installed? Considered upgrading to 2013 or 2016? – cybernard – 2017-03-15T23:35:03.793
Answers: a) Application updates: Office 2010 is always with the latest updates (or should be: it is set to install updates automatically b) Entering data: The data was once imported (via a pretty large - 400 MB - Excel file of 4000 rows and 40 columns). Since then, not a single record has been added. The only thing I do is filtering for specific records, then edit the text in the RTF memo field (leaving the original text in the plain text memo field untouched), and adding some short comments (usually a single word) in a number of additional fields to categorize the records for later reference. – Christian Geiselmann – 2017-03-16T09:33:30.040