[AccessD] Memo field corruption

[Bill Benson]

Oh ok, the Jim (s)

On Thu, Jan 31, 2019, 10:24 AM Jim Dettman <jimdettman at verizon.net> wrote:

>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>