Memo data type field

[lmcc007]

Is there any problem using the Memo field in your databases? It was suggested from another site to store a memo field in a separate table (one-to-one relationship) to keep corruption from happening so much. I have searched the web and found no complaints about the using the Memo field.

Additionally, my Memo fields can very lengthy.

 

[RoyVidar]

They may corrupt under some circumstances. Some will use the trick of storing them in another table.

I've heard of the following trick:

In stead of binding the form control to the Memo-field, have the control unbound, and use for instance the on current event to populate the control with the content of the field, and the controls after update event to populate the field with the content of the control (In the last link, one of the contributors use this technique together with keeping the field in a separate table)

Some will use them "normally"... I think - it depends on how important the data is, and how recent your backups are

Here are two links that might be of value:

http://allenbrowne.com/ser-47.html

http://allenbrowne.com/ser-25.html

Here's a thread from elsewhere, where opposite views are presented

http://bytes.com/topic/access/answers/851398-memo-fields-multiuser-database

Now what to choose

Roy-Vidar

 

[lmcc007]

Thanks RoyVidar,

I created a separate table for the Note field. A one-to-one relationship with Event table to EventNote table. I am having trouble when I delete a Note it leaves a blank/empty field in the EventNote table. I added a delete button to the EventNote form, but when it deletes it deletes the Event information instead of just deleting the Note.

Anyway, I will start reading the links you suggested.

 

[dhookom]

I have created lots of Access applications and have not experienced corruption issues with memo fields. I expect it is because of:
- good network connections
- proper table structures
- possibly some luck

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay Duane,

I'm reading on the web about corruption with memo fields and each suggesting using a separate table for memo fields and pictures especially if there will be a lot of data.

I created a one-to-one relationship with Event table to EventNote table. I am having trouble when I delete a Note it leaves a blank/empty field in the EventNote table. I added a delete button to the EventNote form, but when it deletes it deletes the Event information instead of just deleting the Note.

Event table:

EventID
EventDAte
EventTime and other fields

EventNote table:
EventNoteID Autonumber
EventID Number
EventNote Memo


Any suggestions on being able to delete the EventNote without deleting the Event Record also?

 

[dhookom]

Apparently a event can have only one note? I would use a subform for the notes. The delete would then just delete the record in the subform.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

You're right Duane,

I used a query to create the subform, which I created the query wrong. I was using the Event and EventNote table instead of just using the EventNote table to get the EventID and CompanyID. Now, I am only using the EventNote table and using DLookup to get the CompanyID. Now when I hit the Delete key on the EventNote form it only deletes the EventNote record.

Whoa, queries are dangerous if you get them wrong.

Thanks!

 

[dhookom]

using DLookup to get the CompanyID" there is no CompanyID in any of your tables.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Yep, Event table has CompanyID and lots of other fields. I did that because I want the CompanyID to appear on each form or subform. Oh, there is a Company table with info about the company.

 

[dhookom]

Why do you need to display the company on the subform when it is available on the main form?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Not on subform in a main form--just when a form (subform) pops up or you click a button to open another form.