Memo or Long Text Field Question (1 Viewer)

Cotswold

Active member
Local time
Today, 16:31
Joined
Dec 31, 2020
Messages
521
Hello All,
Although Memo Fields are probably fine now, I'd appreciate the opinions of the forum members.

Back in the dark days of DOS I found that memo fields could cause corruption. It certainly was the case using Clipper87 with dBASE tables.
When I switched to Access97 it was still the case, but then it could have been poor networks or the fact that Access doesn't just load the
record selected but a block of data, which may span several individual records and part records. I also wondered if it was maybe a bug
in Access?

My "scientific" conclusion was from the reality that Access tables in applications with Memos tended to corrupt, but those without didn't.
All systems were written with similar code developed and morphed from one application to another, using all the same libraries. After
experiencing corruption on a site, if I replaced the Memo with a text field, the corruption never happened afterwards.

I still had the odd case with Access2000, so I have always avoided their use. I would restrict a large Text field to 248 characters long.
If anyone wanted to enter more text, then the answer was always "it can't do that!"

I didn't use them in Access2010 and I am now using Access2019. I am wondering how reliable you find the Memo field, now called the
Long Text field, in current Access databases?

In a similar vein, any known issues with the Attachment type Field?

Regards, Will
 

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899
Hi Will
I have found that you get Truncation of Long Text fields when exporting to Excel.

Also, attachment fields will bloat the database.
 

Minty

AWF VIP
Local time
Today, 16:31
Joined
Jul 26, 2013
Messages
10,354
Generally speaking these days they are stable as far as I can tell.

If you are worried I have seen people move the LongText field into a "child" table that is a one to one relationship with the main table.
When you need to edit them, just pull them in as a subform, or join them in a query to view.

There are ways around the exporting problem, it depends on the method used.

Attachment fields I'm not a fan of, as Mike says, they bloat your database very quickly, and don't upscale, so if you move to SQL Express or some other backend they won't work.
 

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899
Minty
Can you recommend a method of exporting long text fields without truncating?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
method of exporting long text fields without truncating
one of them is allen browne's (don't remember where).
use Query and always put the Memo Column as the First select Column.
 

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899

Minty

AWF VIP
Local time
Today, 16:31
Joined
Jul 26, 2013
Messages
10,354
I would probably simply set a recordset to query?

Aircode untested something like
Code:
Dim rsQuery as Recordset

Set rsQuery = currentdb.openrecordset ("qryAllCases" , dbopensnapshot)

Call ExportRecordset2XLS(rsQuery)
 

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899
Hi Minty

Used the suggested and it does the export but the Truncating still persists.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
same thing whether Pineault or Browne, the memo is truncated to 32767 (32K) characters.


 

Attachments

  • sampleExportLongText.accdb
    696 KB · Views: 254

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899
same thing whether Pineault or Browne, the memo is truncated to 32767 (32K) characters.


Hi arnelgp

The method works just great when there is only 1 table.

My query has many tables with a Memo field in 3 of them.

I think this is where the process breaks down.
 

Minty

AWF VIP
Local time
Today, 16:31
Joined
Jul 26, 2013
Messages
10,354
Are you sure the fields are NOT truncated in the query as well??
 

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899
Hi Minty
You are spot on. How can I stop them truncating in the query?
 

mike60smart

Registered User.
Local time
Today, 16:31
Joined
Aug 6, 2017
Messages
1,899
Are you sure the fields are NOT truncated in the query as well??
Hi Minty
Just studied Allen Browne's solutions and the first one worked for me.
Removed the DISTINCT criteria and it now works a treat.
Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
Are you sure the fields are NOT truncated in the query as well??
it is not on my case.
same result truncated in 32K both browne and pineault.
i have 60K characters memo demo.
 

Isaac

Lifelong Learner
Local time
Today, 09:31
Joined
Mar 14, 2017
Messages
8,738
Hi Minty

Used the suggested and it does the export but the Truncating still persists.
Use the real method - excel vba range.copyfromrecordset.
For me, it solves Truncating as Minty suggested - it's the only method I use to export from Excel now.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:31
Joined
Feb 19, 2002
Messages
42,970
The other problem with attachment fields is that the data type is NOT supported by SQL Server. Therefore, if you ever have to upsize, you will need to export the attachments and modify all processes that used them.
 

Users who are viewing this thread

Top Bottom