[AccessD] Memo field corruption

[Jim Dettman]

I believe what he was saying is that if data was not in the DB and in a
file, then the backup time for the DB itself would be reduced.   But that
doesn't mean you have a "complete" backup as your thinking.

The technique though of pushing data to a file and then storing a pointer
was mainly used to avoid database bloat.   And that was in the past when
Access stored objects so inefficiently.    You can do better now, but memo's
are still problematic for all the reasons that have been pointed out.

 The other point here is the memory usage; JET handles everything by its
page cache for actual data fetches, so it would not consume 200MB to
process.   But there'd be a lot of turn-over in the cache.

Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Thursday, January 31, 2019 9:35 AM
To: Access Developers discussion and problem solving
Cc: James Button
Subject: Re: [AccessD] Memo field corruption

RE:  >>>backup requirements if the entries are held in separate files

I don't get this remark either. If the entries are in "separate files" then
that implies they are not in the database. In which case, what good would
backing up the database do you?

On Sat, Jan 26, 2019 at 12:43 PM James Button via AccessD <
accessd at databaseadvisors.com> wrote:

> Add to the avoiding corruption of masses of data
>
> Allowing for easier changes of associated apps when the memo contains RTF
> or
> other things such as images
> Also reduces the time taken for the DBMS to load up the data in the table
> for a
> SELECT statement, or just a scan through the file for backups etc.
> If you have 10,000 rows, and each has 1000 bytes of data that's a 10MB
> table
> And maybe 20MB of Temp files
> With a Memo of - say 4000 bytes in each that means any process of the
> table will
> have to read 100MB and possibly use 200MB of RAM for that process
> Complete with the flushing of memory to pagefile and the flushing of other
> data
> from the storage cache, and   the RAM allocated by the OS for I/O
> buffering.
>
> So - separate out the memo and blobs and you avoid massive processing
> overheads
> as well as backup requirements if the entries are held in separate files
> And that is without even considering the increasing size of images and
> commentary that may be in memo fields
> Cut and paste of text from reports and filled-in forms - as in as well as
> describing the actual incident, detail your understanding of the cause,
> and the
> effect of the incident on yourself.
>
>
> JimB
>
>
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Susan
> Harkins
> Sent: Saturday, January 26, 2019 5:18 PM
> To: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Memo field corruption
>
> The main reason was to protect the rest of your data -- if the memo field
> is
> corrupted, you're dealing with just the memo fields in that table. It
won't
> prevent corruption, just make it easier to fix if you do.
>
> Susan H
>
>
> In this page
>
>
https://bytes.com/topic/access/answers/190112-query-memo-field-cuts-off-255-
> characters at the bottom it says this:
>
> The reason for these limitations is that memo content is not actually
> stored
> in the table. Only a pointer to its location on disk is. This makes
> features
> like sorting and grouping _very_ inefficient because the query has to use
> the pointer to go "get" the text, evaluate it, and then apply the sorting
> and grouping.
>
> So - if the data is already NOT stored in the table, why create another
> related table for memo fields?
>
> I'd suggest trying to track down the source of this 'rule' before you go
> further with it.  Maybe this was helpful for much older versions of Access
> for some reason?
>
> Good Luck,
> Dan
>
>
> I think a lot of "expert" database developers suggest keeping the memo
> field
> in a separate table using a 1:1 or 1:n relationship -- just in case.
>
> Susan H.
>
>
> I always include memo fields in the same table.  I haven't heard of what
> you
> described - have you seen that done somewhere?
>
> Dan
>
>
> When you include a memo field, do you include it in the table or do you
> relate to a second table that stores just the memo field?
>
> Susan H.
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com