Memo to Text Data Type (1 Viewer)

[spenzer]

Good Day!

I have a couple of fields spread across the database named "Notes" which is currently set as memo data type, I would like to change it to text data type.

What I already know:
1. Text data type can only have a maximum 255 characters.
2. Memo data type can store up to 1000 characters.

* I am sure that we don't need more than 255 characters for it; not now not ever, and we currently don't even have 50 characters stored in a single field so the truncation matter is out of the question.

What I would like to know:
1. Could I just directly change the Data Type of All Notes field in our Back end from MEMO to TEXT. Is it safe that way?

2. Is there a possibility of a corruption issue after the change or future use?

 

[RainLover]

spenzer,

The easiest way to find out is to just do it and see for yourself.

Of course you would make a copy first and test on the copy. NEVER do something like this on your live database without having tested first and made a backup..

I can't see a problem with what you intend to do. In fact based upon your information I would highly recommend it.

 

[Rabbie]

Good Day!

I have a couple of fields spread across the database named "Notes" which is currently set as memo data type, I would like to change it to text data type.

What I already know:
1. Text data type can only have a maximum 255 characters.
2. Memo data type can store up to 1000 characters.

Memo type can store 65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically

Check out Access Help under specification for this info

 

[RainLover]

Memo type can store 65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically

Check out Access Help under specification for this info

Rabbie thanks for that.

I knew about 65,535 but did not know about the 1 gig. The question arises WHY would you want 1 gig.

 

[spenzer]

@Rainlover

Thanks for the input. I decided that i would give it a go and stop chickening already.

@Rabbie

Thank you for the detailed spec of memo data type, it appears i should really avoid it due to tons of wasted space.

 

[gemma-the-husky]

i think the 1000 char memo is a SQL thing isn't it.

varchar fields, aren't as long as Jet/Ace memo fields

I am pretty sure the two are interchangeable. however you will get a warning that changing from a memo to text may truncate data in the fields.

the other benefit is that you can sort text fields, but not memo fields

 

[RainLover]

Access help says;

Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)

So I assume this could be up to 2 Gig.

 

[gemma-the-husky]

@Rainlover

Thanks for the input. I decided that i would give it a go and stop chickening already.

@Rabbie

Thank you for the detailed spec of memo data type, it appears i should really avoid it due to tons of wasted space.

there isn't wasted space with text or memo fields. access only stores the size of data required - not the full unused width of text or memo fields

 

[spenzer]

there isn't wasted space with text or memo fields. access only stores the size of data required - not the full unused width of text or memo fields

Hi gemma, thanks for the tip. I have already changed my data type. But before that, I have read somewhere that memo data type field is prone or can cause database corruption. So with that reason along with we won't really benefit for the increased storage capacity. I decided to convert all of it into text data type.