Handling memo fields (1 Viewer)

AZ_CC

Registered User.
Local time
Today, 02:32
Joined
Apr 25, 2018
Messages
11
I'm currently building up a database, and want to improve the way I'm handling the memo fields. I'd thought I'd planned for it fairly well, but had the first corruption problem at about 750 records.


Right now, I'm simply using a bound text box with the underlying table as a record source for the form. I've gone back and split the underlying table out into a separate table with a one-to-one relationship and populated it using an append query. It would be simple at this point to switch the underlying row source to a query that pulls in the new table and keep running it basically as is. However, I'm not sure if that really helps me because I don't understand the corruption problems that come from those memo field pointers well enough to know if modifying the database design like this is sufficient to protect against corruption.

Is it a good idea to set up that field as an unbound box that populates using the new table, and if so how do I code the operations the bound form usually handles automatically (populating and updating existing records, or adding and saving new ones)? I'm reasonably comfortable working with VBA, but this isn't something I've tried to do before and if just using a query to incorporate the second table is workable to stabilize this memo field I'll happily do that.
 

plog

Banishment Pending
Local time
Today, 04:32
Joined
May 11, 2011
Messages
11,613
I've gone back and split the underlying table out into a separate table with a one-to-one relationship and populated it using an append query.

Huge red flag. What are you accomplishing with your 1-1 relationships? Data like this should be in the same table.

Can you set up your Relationship Tool, expand the tables so I can see all fields and then post a screenshot?
 

AZ_CC

Registered User.
Local time
Today, 02:32
Joined
Apr 25, 2018
Messages
11
Huge red flag. What are you accomplishing with your 1-1 relationships? Data like this should be in the same table.

Can you set up your Relationship Tool, expand the tables so I can see all fields and then post a screenshot?

This is specific to a memo field - when I was researching around after the corruption happened, I discovered that memo field corruption damage can often be limited by storing those fields in a separate table with a 1-to-1 relationship. I'd still lose the text in those fields.

I've not used the 1-to-1 before, really.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:32
Joined
May 21, 2018
Messages
8,463
Code:
Huge red flag. What are you accomplishing with your 1-1 relationships? Data like this should be in the same table.
What is the Huge red flag? This is a good idea. Memo fields are much safer than in the early days, but still the number one cause of corruption. By doing this you avoid loosing all your data instead of the memo data. Also as stated using unbound controls can also help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Jan 23, 2006
Messages
15,364
In addition to plog's request, can you give us a 30,000 ft overview of what the application is about (subject matter) -in plain English?
 

June7

AWF VIP
Local time
Today, 01:32
Joined
Mar 9, 2014
Messages
5,425
Never had issue of corruption due to use of memo type. However, I avoid them because of their intrinsic limitations.

You put the memo field in a separate table? Have you tried a subform? I expect use of unbound form and control will involve complex coding. Never gone that route.
 

Mark_

Longboard on the internet
Local time
Today, 02:32
Joined
Sep 12, 2017
Messages
2,111
Most often when I see "Memo", I think "Should be in a CHILD table with date/who put in/action date/other things/memo itself". Ask your self and your users what they expect to have in a "Memo". Often you find they want to put text information that does require follow up, separate entries for different users, and other values that lead to "Should be a child record".

From a design standpoint there is little difference between having "One" memo for a record and having "Many". If you plan for "Many" from the start it is far easier to handle those that have one or none. If you hard code a "One to One" you run into problems the moment you realize you need to have two for one record.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:32
Joined
May 21, 2018
Messages
8,463
Most often when I see "Memo", I think "Should be in a CHILD table with date/who put in/action date/other things/memo itself". Ask your self and your users what they expect to have in a "Memo". Often you find they want to put text information that does require follow up, separate entries for different users, and other values that lead to "Should be a child record".

From a design standpoint there is little difference between having "One" memo for a record and having "Many". If you plan for "Many" from the start it is far easier to handle those that have one or none. If you hard code a "One to One" you run into problems the moment you realize you need to have two for one record.

I am sorry, but this makes zero sense to me. A memo field in Access is a field that can hold over 255 characters. That is it. It has nothing to do with content. You seem to be describing a Note field, which is unrelated to a memo datatype. There is no such thing as a one to one relationship. It is only a construct to separate data. You have to go out of your way to force a one to one relationship, it is not like you can start with that and have to expand from that to a one to many. You start with a one to many and then force it through code into a "one to one"

Bottom line if you are on a shared db with the backend on the network, it is not a bad idea to separate memo fields in their own tables. For the sole purpose if the memo field gets corrupted you only lose the memo data. I have seen corruption on memo fields as late as 2013 dbs. It is still pretty rare. The second thing that can help is to read the memo from a recordset. Populate an unbound control. After update do an insert or update query to populate the new data.

The question is do you need to go this route. If your connection is shaky then I would consider it. Writing generic code to do this should not be hard, but it gets more complicated when you could have 2 users editing the same memo field.
 

Mark_

Longboard on the internet
Local time
Today, 02:32
Joined
Sep 12, 2017
Messages
2,111
MajP,

How does "If you hard code a one to one" not make sense when you post
You start with a one to many and then force it through code into a "one to one"
I was stating to use a child table from the beginning and NOT force a one to one.

Your response does not make sense to me.
 

Users who are viewing this thread

Top Bottom