Memo Efficiency Question 1

[hubs99]

I have a database with 7000+ entries that unfortuantely I inherited and it basically is a flat file (on Access). I am migrating this data into a more traditional relational database setup (on access). The flat file dabase has 1 table with ~100 fields and I'm splitting that up into 10+ tables with 5+ fields.

Now for the question. Many of the tables need to have a comment. So I was going to put a memo field in each of the 10+ tables. However I also contemplated creating an autogenerating Replication ID field in each of these tables instead and linking it a common Comment table which would have this ID + a memo field.

So which of these methods do people think is the better way to go? Is the memo field typer already a referenced datatype like an OLE or is it like a text field?

Any ideas would be much appreciated. Thanks in advance for the help

 

[willir]

hubs99

Either is fine. A memo field is not really part of the record ... well perhaps the first 255 characters are, but the memo field is stored external to the table and Access just stores a link or pointer from the record to the appropriate memo field.

The question what do you gain by consolidating your memo fields into one? If it avoids duplication, great. But you will have slightly more overhead, and coding to address.

Richard

 

[hubs99]

OK so a memo field is Definitely just a pointer and will not slow down this particular table?

By consolidating I would hopefully be gaining speed since not every entry in all the different tables will have an actual memo and I would rather just have a common memo table if it means the DB will be smaller and faster because the giant bulky memo field is not attached to each of the tables.

But if it is already just a pointer then what I was thinking about doing is essential the same thing. So I would be creating a pointer to another pointer. Which is useless. Thanks ya'll

 

[hubs99]

Any other suggestions out there?

 

[hubs99]

To The Top Please

 

[KenReay]

MEMO is definately a pointer, the foolowing from Access Help should answer your question I think:

Should I create a Text or a Memo field?
Microsoft Access provides two field data types to store data with text or combinations of text and numbers: Text or Memo.

Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. A Text field can store up to 255 characters, but the default field size is 50 characters. To control the maximum number of characters that can be entered in a Text field, set the FieldSize property.

Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 64,000 characters. If you want to store formatted text or long documents, you should create an OLE field instead of a Memo field. To find out more about OLE fields, click .

Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.

For more information on creating a text or memo field, click .



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK