A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How many MEMO fields allowed in a table?



 
 
Thread Tools Display Modes
  #11  
Old September 14th, 2007, 02:02 AM posted to microsoft.public.access.tablesdbdesign
fredg
external usenet poster
 
Posts: 4,386
Default How many MEMO fields allowed in a table?

On Thu, 13 Sep 2007 19:29:58 -0500, Michael Gramelspacher wrote:

If so, then Memo fields must be excluded as I have records with two
Memo fields each in excess of 3,000 characters.

On Thu, 13 Sep 2007 13:26:52 -0700, "Jeff Boyce"
wrote:

Access has a limit of (?)2,000 characters per record ... that would be why
multiple text fields at 255 characters each would exceed the limit.

If you'll provide a bit more specific description of the domain you are
working in, the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP


From Access Help + Specifications + Access Specifications + Tables

Number of characters in a record (excluding Memo and OLE Object
fields) 2,000
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #12  
Old September 14th, 2007, 06:46 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How many MEMO fields allowed in a table?

On Thu, 13 Sep 2007 19:55:32 -0500, Michael Gramelspacher
wrote:

Why would I want memo fields to be indexed?


I can get by perfectly well without indexes on them... but the question comes
up surprisingly often in the newsgroups.

John W. Vinson [MVP]
  #13  
Old September 14th, 2007, 09:29 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How many MEMO fields allowed in a table?

On 14 Sep, 01:55, Michael Gramelspacher wrote:
Why would I want memo fields to be indexed?

I have almost no practical experience, and I really am
wondering because I have read this many times before.


One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns.

One government agency schema I must work with has a wide key; let's
say it's up to 1000 characters. In Jet, this column would be best IMO
as a MEMO column (yes I know it's blob data but surely that's better
than scaling my own NCHAR/NVARCHR columns?)

Values are unique in the domain so naturally I'd want a UNIQUE
constraint in my db. The SQL DBMSs I work with all use indexes to
implement such constraints, as does Jet. Also, I have to search, join,
etc on this column so why _wouldn't_ I want to index it if possible?
Hint: they agency chose such a wide key because the number of entities
in the domain is vast (they have since realized they didn't need such
a wide key but the seed has been sown) and users could have literally
millions of values in the db...

...SQL Server Express...


I'm glad *you* mentioned that g. In SQL Server I can create a
calculated column to store a hash of the key value with a constraint
to ensure the hash is a match for the value, and put an index (dupes
allowed, obviously) on the hash column. I can then use both the value
column and the hash column in a trigger to ensure uniqueness and the
index on the hash column yields acceptable performance. Obviously, the
wise may use both columns in regular SQL JOINs, WHERE clauses etc to
ensure the index on the hash gets used. This is partially achievable
in Jet (e.g. the hash column and index), more so in Access (e.g.
validation rule to ensure the hash is a match) but not everything
(e.g. the trigger to ensure uniqueness).

Jamie.

--


  #14  
Old September 14th, 2007, 09:33 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How many MEMO fields allowed in a table?

On 13 Sep, 23:30, John W. Vinson
wrote:
memos have disadvantages... sorting will
truncate them to 255 bytes


Picky, I know, but yours is a misstatement. To test:

CREATE TABLE Test
(
memo_col MEMO NOT NULL
)
;
INSERT INTO Test (memo_col) VALUES
('123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90_JohnV')
;
INSERT INTO Test (memo_col) VALUES
('123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678 90_Jamie')
;
SELECT memo_col AS result1, LEN(result1) AS result1_bytes
FROM Test
ORDER BY memo_col
;

result1_bytes returns 306 for both rows. Conclusion: sorting has not
caused truncation.

Jamie.

--



  #15  
Old September 14th, 2007, 01:38 PM posted to microsoft.public.access.tablesdbdesign
Michael Gramelspacher
external usenet poster
 
Posts: 482
Default How many MEMO fields allowed in a table?

Jamie, a single-column key that is 1,000 characters wide seems absurd
in my mind.

NVARCHAR and NCHAR are not recognized by most people as Access
datatypes. And in SQL Server their length can be 4,000 characters
according to Books on Line. An Access Text datatype is limited to 255
characters.

I guess my vision does not extend much beyond the Access world. And I
am still left wondering why I might want to index or sort a memo
column.


On Fri, 14 Sep 2007 08:29:20 -0000, Jamie Collins
wrote:

One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns.

One government agency schema I must work with has a wide key; let's
say it's up to 1000 characters. In Jet, this column would be best IMO
as a MEMO column (yes I know it's blob data but surely that's better
than scaling my own NCHAR/NVARCHR columns?)

  #16  
Old September 14th, 2007, 03:24 PM posted to microsoft.public.access.tablesdbdesign
mscertified
external usenet poster
 
Posts: 835
Default How many MEMO fields allowed in a table?

One thing I have run into a few times is when you do a UNION join between 2
tables that contain a memo column, the memo column gets truncated. There are
ways around this but it can trip you up and may not be detected until the bug
has existed for a long time when someone notices the truncation.


"Michael Gramelspacher" wrote:

Jamie, a single-column key that is 1,000 characters wide seems absurd
in my mind.

NVARCHAR and NCHAR are not recognized by most people as Access
datatypes. And in SQL Server their length can be 4,000 characters
according to Books on Line. An Access Text datatype is limited to 255
characters.

I guess my vision does not extend much beyond the Access world. And I
am still left wondering why I might want to index or sort a memo
column.


On Fri, 14 Sep 2007 08:29:20 -0000, Jamie Collins
wrote:

One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns.

One government agency schema I must work with has a wide key; let's
say it's up to 1000 characters. In Jet, this column would be best IMO
as a MEMO column (yes I know it's blob data but surely that's better
than scaling my own NCHAR/NVARCHR columns?)


  #17  
Old September 18th, 2007, 01:37 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How many MEMO fields allowed in a table?

On Sep 14, 1:38 pm, Michael Gramelspacher wrote:
Jamie, a single-column key that is 1,000 characters wide seems absurd
in my mind.


I repeat: the example I gave is not my invention g!

I
am still left wondering why I might want to index or sort a memo
column.


I would hope it doesn't need too much thought/imagination to find an
example within your own domain of something in excess of 255
characters that may need to be unique e.g. URL, SQL statements' text
(removing white space, comments, etc).

NVARCHAR and NCHAR are not recognized by most people as Access
datatypes.


I really don't know what to make of that sentence Do you mean 'Jet
data types' or do you really mean something pertaining to Access that
has no direct meaning in Jet such as the 'hyperlink data type' (if
that is indeed the correct phrase)? You corrected my misspelling of
'NVARCHAR' so are you saying that some people consider that fixed
width nature of NCHAR and/or WITH COMPRESSION should be disregarded
because they are not exposed in the Access user interface? Whatever, I
think there is an issue of awareness he distinct varying- and fixed-
width text data types exist in Jet, NVARCHAR and NCHAR are the
commonly encountered keywords in the wider SQL world, these keywords
are supported in Jet's ANSI-92 Query Mode, and the more we talk about
them the more familiar they will become in the Access world.

Jamie.

--


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.