Handling memo fields

[AZ_CC]

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]

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]

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]

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]

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]

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_]

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]

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_]

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.