Download it now!
Bug 79026 - Content of memo fields in MS Access tables get truncated in LibreOffice Base
Summary: Content of memo fields in MS Access tables get truncated in LibreOffice Base
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.4.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Tables Database-MS_Access
  Show dependency treegraph
 
Reported: 2014-05-21 17:13 UTC by Albrecht Müller
Modified: 2019-07-31 14:11 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
.mdb and .odb (9.11 KB, application/x-zip-compressed)
2015-07-16 02:02 UTC, Terrence Enger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Albrecht Müller 2014-05-21 17:13:56 UTC
Steps to reproduce:

Create a new database that refers to some MS Access database. This database should contain a table with a memo-field, i.e. a column where you can enter text which can contain much more than 255 characters. There should be entries containing more than 255 characters. Using the form assistent create a form to access this table. I simply created a form containing all fields of the table. Save the form an open it. Navigate to some record whose memo field contains more than 255 characters.


Current behavior:

The content of the memo field is truncated to about 255 characters.  This seems to depend of the wrap around feature of the control showing the memo field. If no wrap around is selected the content is truncated to less than 255 characters. Seems to be truncated to 255 characters, and then the CR/LF sequences are deleted.

Due to the amount of data a memo fields can contain this behavior can cause substantial loss of data! It is especially dangerous as the form seems to detect that the content of the control is not the same as the corresponding content in the table and therefore offers to store the content despite the fact that the user did not change it.

The table view shows the full content of the memo field. Therefore the problems seems to be related to the form.


Expected behavior:

The control should show the complete content of the memo field (more than 255 characters).


Platform: 
LibreOffice 4.2.4.2 german, Windows 7
Comment 1 Alex Thurgood 2014-05-21 17:39:34 UTC
Hi Albrecht,

Do you have a Database > Advanced Properties context menu when you right mouse button click on a table in the main ODB window ? This might be database type dependent, I don't remember anymore.

If you do have access to such a menu entry, you should be able to set the form to not verify that the data entered in the form corresponds to that required for the bound fields. Untick that box, and see whether that changes anything.

If not, this does sound like a duplicate of a previous bug entry, but I don't have the number to hand.

Alex
Comment 2 Alex Thurgood 2014-05-21 17:42:36 UTC
Maybe the problem here is similar/same, and perhaps specific to Windows :

https://bugs.freedesktop.org/show_bug.cgi?id=65644
Comment 3 Albrecht Müller 2014-05-22 11:18:14 UTC
Hi Alex,

After I switched the user interface language to English (UK) I found:
right click to some object (can be a table or a form) -> Database -> Advanced Settings -> Form data input checks for required fields.

The corresponding check box was checked and I unchecked it.

I was not able to notice a difference. When I close a form showing truncated content I sometimes get a dialog box asking me I a want to save the changed record. I have got the impression that this message does not appear if I just navigated to some record containing truncated content. If I had set the focus into the control containing the truncated content (without changing anything) the dialog appears when I close the form. It is complicated to assess when changes to the database occur as the table view seems not to reflect these changes immediately, i.e. I have to close and reopen the table view if want to see current content of database.

I don't think this is a duplicate of 65644 as this bug is related to version LibreOffice 4.0.3.3.
My problem in related to Access 97 databases, and to my knowledge the Access2Base feature is not included in versions before 4.2. (see http://www.access2base.com/access2base.html) Using previous versions I had no easy way to connect to MS Access databases.

The behavior of this bug seems to have changed between versions 4.2.3.3 and 4.2.4.2: In version 4.2.3.3 memo fields were truncated in the table view. In 4.2.4.2 the table view shows the full content. (I don't have 4.2.3.3. installed now therefore I cannot verify this observation again). Both versions are unable to display the full content in forms.

I think this bug is really dangerous as it makes it very easy to inadvertently destroy database content.

Albrecht
Comment 4 Buovjaga 2014-11-24 11:58:42 UTC
Albrecht: can you test with 4.3 and 4.4 beta and report your results here? Change back to UNCONFIRMED, if the problem persists. Change to RESOLVED WORKSFORME, if the problem went away.
Comment 5 Albrecht Müller 2014-11-25 09:51:08 UTC
(In reply to Beluga from comment #4)
> Albrecht: can you test with 4.3 and 4.4 beta and report your results here?
> Change back to UNCONFIRMED, if the problem persists. Change to RESOLVED
> WORKSFORME, if the problem went away.

Tried with Version: 4.3.4.1 Build ID: bc356b2f991740509f321d70e4512a6a54c5f243. Unfortunately this is the only version available to me. 

The behaviour persists. What I did: Using Base, I opened an MS-Access97 database, created some form (I used one that shows a single record per page) on a table containing memo fields, navigated to some record containig a field with content longer than about 255 characters, moved cursor to the end of the field (was careful - tried a avoid changing any content), saw that content was missing, quit the form. Got prompted if I wanted to save the changed content (I did not change anything!), confirmed this, used a MS-Access installation to see that the content is actually truncated. Summary: There is a substantial loss of data.
Comment 6 Buovjaga 2014-11-25 10:33:16 UTC
Let's set to UNCONFIRMED then.
Comment 7 Alex Thurgood 2014-12-15 15:20:11 UTC
Unless we have a test Access97 file with which to test, it is going to be difficult for any of the qa team to reproduce.
Comment 8 Lionel Elie Mamane 2014-12-16 13:22:41 UTC
What type of control is being used in the form to show? Text box? Formatted field?

Do you use ODBC or ADO to connect to the .mdb file?

(If you attach the .odb file, that would be nice.)

(In reply to Albrecht Müller from comment #3)
> My problem in related to Access 97 databases, and to my knowledge the
> Access2Base feature is not included in versions before 4.2.

Access2Base is only about running Basic macros, that's unrelated to accessing *data* in .mdb files. The latter is available since OO.org times.
Comment 9 Alex Thurgood 2015-01-03 17:41:05 UTC Comment hidden (no-value)
Comment 10 raal 2015-01-04 10:43:35 UTC
(In reply to Alex Thurgood from comment #7)
> Unless we have a test Access97 file with which to test, it is going to be
> difficult for any of the qa team to reproduce.
Hello Albrecht,

Please send us a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document.
(Please note that the attachment will be public, remove any sensitive information before attaching it.)
How can I eliminate confidential data from a sample document?
https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F
Thank you
Comment 11 Terrence Enger 2015-07-16 02:02:05 UTC
Created attachment 117272 [details]
.mdb and .odb

.zip file contains ...
(*) db1.mdb, created by Microsoft(R) Access 2003 (11.8321.8341) SP3
(*) exmple.odb, created by LibreOffice ...
        Version: 5.1.0.0.alpha1+
        Build ID: d3b6f3790953bdfeaeebcd3ba9ec370d94ca4ebf
        TinderBox: Win-x86@39, Branch:master, Time: 2015-07-09_00:11:56
        Locale: en-CA (en_CA)
    connecting to db1.mdb.
Comment 12 Terrence Enger 2015-07-16 02:04:58 UTC
I am setting status NEW and removing "forms" from the summary.

STR ...

(0) Download the attached .zip and extract the two contained files.

(1) Open db1.mdb in MS Access and observe that in Table1, first (and
    only) row, field "words" contains two sentences.

(2) Open example.odb in LibreOffice and observe that that field is
    truncated in the first sentence (after the words "after experience
    into").  I see this in LibreOffice ...
        Version: 5.1.0.0.alpha1+
        Build ID: 89b5967658392d27fb3147e85abb2b5c1c34b101
        TinderBox: Win-x86@39, Branch:master, Time: 2015-06-24_04:10:17
        Locale: en-CA (en_CA)
Comment 13 QA Administrators 2016-09-20 10:18:13 UTC Comment hidden (obsolete)
Comment 14 Terrence Enger 2016-12-11 20:22:48 UTC
I still see the problem in bibisect-win32-5.3 repository commit
479ca41, source hash f8c463b3.
Comment 15 QA Administrators 2019-07-15 02:48:03 UTC Comment hidden (obsolete)
Comment 16 Terrence Enger 2019-07-31 14:11:32 UTC
I no longer have Windows and I cannot connect to the .mdb file.  I
would welcome a retest by anybody who can do it.

Meanwhile, I am closing the bug report as RESOLVED INSUFFICIENTDATA.