Memo or Long Text Field Question

Cotswold

Well-known member
Local time
Today, 20:47
Joined
Dec 31, 2020
Messages
916
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
 
Hi Will
I have found that you get Truncation of Long Text fields when exporting to Excel.

Also, attachment fields will bloat the database.
 
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.
 
Minty
Can you recommend a method of exporting long text fields without truncating?
 
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.
 
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)
 
Hi Minty

Used the suggested and it does the export but the Truncating still persists.
 
same thing whether Pineault or Browne, the memo is truncated to 32767 (32K) characters.


 

Attachments

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.
 
Are you sure the fields are NOT truncated in the query as well??
 
Hi Minty
You are spot on. How can I stop them truncating in the query?
 
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
 
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.
 
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.
 
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

Back
Top Bottom