Using Multiple Memo Fields in an Access Database

Question

What are the pro's and cons with using Memo Fields in Microsoft Access 2010 Accdb databases? I'm altering an Access 2010 (accdb) database to convert 5 columns in a table of 45,000 records to be memo. This data will then be imported into SQL-Server as varchar(max).

I'm creating new fields for the data and then copying the data from the text(255) fields across as neither 'ALTER TABLE' in VBA nor altering the column through the Ms Access table view is working ("not enough disk space or memory").

This is making me feel very wary about using this many Memo fields. Eventually will eventually be 4 tables with 5 Memo fields each. Each table will have around 100+ fields in total with up to 400,000 records.

Should I just go back to the end user and tell them that they will have to use text(255) rather than multiple Memo fields as I suspect that the field has been defined as Memo 'just in case'.

DanielG DanielG · Accepted Answer · 2016-10-18T18:52:31

I really don't think 5 memo fields is a lot, and Access will only claim disk space for the actual entered values. While it can hold 1GB of characters (2GB of storage), it may only display 65K characters or so. I would not be overly concerned about using memo fields in general, and I don't think this is your issue.

If the memo fields are already part of your base table, you would be using an UPDATE statement to move the data from the other fields into it. If you are exporting the tables to SQL, and then making the columns larger, the

ALTER TABLE MyTable ALTER COLUMN MyColumn nvarchar(MAX)

Should work without issue.

You may want to consider creating a single relational table with a table key, record key, and a single memo field, and then allow as many memo fields as necessary by referencing this table in a one-to-many fashion from the base table.

So you would have this type of data:

TABLE          PK           MEMO
---------------------------------
TableA         1            Note A
TableA         1            Note B
TableA         2            Note A
TableB         1            Note A

Where exactly are you getting an out of disk space error?

Using Multiple Memo Fields in an Access Database

1 Answers