Alternative to Access 2007 Memo Fields

[JamesJ]

One of my tables data requires much text in an access 2007 memo a field.
I'm having problems being able to place all this text in the memo field even
though they are supposed to
hold 1 gigabyte of data I can't get half that without corrupting the field.
I posted concerning this but so far no takers, probably because it's a bug
and no one seems to have a
fix for it. Or no one has the balls to admit that it is a bug. What a shame.
Now, I thinking about abandoning these useless memo fields in access 2007
with an alternative but
so far I haven't found on. I need a way more than 255 characters so text
boxes are out of the question.
If anyone can think of an alternative to storing this data it will be
appreciated. I give up with this garbage.

Thanks,
James

 

[John W. Vinson]

One of my tables data requires much text in an access 2007 memo a field.
I'm having problems being able to place all this text in the memo field even
though they are supposed to
hold 1 gigabyte of data I can't get half that without corrupting the field.
I posted concerning this but so far no takers, probably because it's a bug
and no one seems to have a
fix for it. Or no one has the balls to admit that it is a bug. What a shame.
Now, I thinking about abandoning these useless memo fields in access 2007
with an alternative but
so far I haven't found on. I need a way more than 255 characters so text
boxes are out of the question.
If anyone can think of an alternative to storing this data it will be
appreciated. I give up with this garbage.

Thanks,
James

Memo fields can theoretically store up to 2Gbyte if they are filled
programmatically; if you're filling it by copying and pasting (or, shudder,
typing) you're limited to 65536 characters.

HOWEVER - a .mdb or .accdb file as a whole is limited to 2GByte, so if you
have two records with a gigabyte of memo field in each, your entire database
is toast!

For text of this size I think your only options are to store the data in
SQL/Server (and not SQL Express either, since it is also limited); or store
the data in external text (or Word or some other suitable) files, and store a
path and filename or a hyperlink to the file in your table.

I'd agree that it is a bug, at the very least a bug in the documentation; but
you could also argue that it's simply going beyond the bounds of what Access
was designed and intended to handle.

 

[Jeff Boyce]

James

If you'll describe a bit more about the nature of such large amounts of
text, perhaps folks here can offer alternate suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JamesJ]

I have a DVD library and some are tv series. I decided to put all
dvd descriptions in 1 record per season. I'm regarding
each disc of a season as a record. Season 1 might have 15
episodes on 5 discs so I have 5 records for season 1.
I want to combine this into 1 record and place the episode titles all in the
same record for
the season. But When I try to paste from the other existing season records
into
the memo field I get so far and then the memo field gets corrupted.
I'm able to type everything in manually with no problem provided the memo
field isn't corrupt.
That's a pain!
This is the error:

'Could not update; currently locked by another session on this machine.
(Error 3188)'

James

 

[JamesJ]

I was considering using Wordpad but I not sure how to link the pertinent
record to the
text file.
I'm able to type all the text into the memo field manually but that's a
pain.
I just might go that route, not sure.

James

 

[Jeff Boyce]

James

I'm not sure I understand yet...

Are you saying that a DVD can contain multiple episodes of a TV series, and
that you wish to keep a description of each episode? If so, why "stuff" all
the descriptions into one "record"?

It sounds like the relationship in your data is one (physical) DVD can
contain one-to-many "episodes", and each episode has a description. That
sounds to me like a classic parent/child relationship between two tables.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JamesJ]

Nothing to do with parent/child.
A tv series season has say 5 discs with 4 episodes on each disc.
Originally I created 5 records (one for each disc) with 4 episode
descriptions in each record
that I put into the memo field.
Instead of having 5 records I want to make 1 record and in the memo field
have: Disc 1, with the
4 episode descriptions, Disc 2 with the 4 episode descriptions and so on
all in the memo field.
But, after pasting the existing descriptions from the existing records the
memo field gets corrupted.

Am I making sense?

James

 

[John W. Vinson]

Nothing to do with parent/child.
A tv series season has say 5 discs with 4 episodes on each disc.
Originally I created 5 records (one for each disc) with 4 episode
descriptions in each record
that I put into the memo field.
Instead of having 5 records I want to make 1 record and in the memo field
have: Disc 1, with the
4 episode descriptions, Disc 2 with the 4 episode descriptions and so on
all in the memo field.
But, after pasting the existing descriptions from the existing records the
memo field gets corrupted.

Am I making sense?

Your actions make sense... but your table structure doesn't!

If you want to be able to search for a description and identify which episode
had the text for which you're searching, you're defeating your own purpose by
creating one enormous memo field for the entire season. You'ld do much better
to have a set of related tables: Shows (e.g. "Dragnet"); Seasons (e.g. 1962);
Episodes (e.g. 5). The episode description would be in a single record in the
Episodes table.

(Yes, I'm showing my age <g>)

You'll still have trouble if in fact your indefagitable fingers are indeed
typing over TWO BILLION BYTES of description... that's hundreds of books
worth, and your database will crash regardless of whether this is in one memo
field in one record or in scores of smaller memo fields in scores of records.
How big is your database compacted?

I think you're right, there is a bug - but it has to do with copy and pasting
data. I've heard of (and experienced) multiple problems with entering data in
that manner.

 

[JamesJ]

I remember Dragnet.
I don't think the episode titles and descriptions are actually two billion
bytes.
I also have successfully copied and pasted all episodes of a tv series -
X-Files
season 1. Only problem is at this time I'm unable to make changes to the
data
in the memo field. Keep getting that error.
Again, when I type them in manually I don't get the error.
Maybe I'll just put the episode titles in the memo field.

Thanks much for the assistance and sorry 'bout my tirade,
James

 

[Jeff Boyce]

Both John and I have pointed you in the direction that could alleviate the
problem you're seeing ... by not using that approach!

If you elect to continue stuffing a memo field rather than using one record
for each description, you can expect to have to keep developing
work-arounds. Access expects well-normalized data, and it doesn't sound
like your table structure is well-normalized.

The example you provided could be (alternatively) structured something like
(untested):

tblDVD
DVD_ID
DVD_Title

trelDisc
DiscID
DVD_ID (the 'parent')
?other disc-related data

trelEpisode
EpisodeID
DiscID
EpisodeNumber
EpisodeTitle
EpisodeDescription

With this design, I can envision finding all the descriptions
([EpisodeDescription] for the DVD entitled "X-Files - Season 1", no matter
how many discs it is spread across.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I remember Dragnet.
I don't think the episode titles and descriptions are actually two billion
bytes.
I also have successfully copied and pasted all episodes of a tv series -
X-Files
season 1. Only problem is at this time I'm unable to make changes to the
data
in the memo field. Keep getting that error.
Again, when I type them in manually I don't get the error.
Maybe I'll just put the episode titles in the memo field.

Thanks much for the assistance and sorry 'bout my tirade,
James

 

[JamesJ]

My tblDvd has 4 fields:

DvdID (autonumber)
DvdMovieTitle
DvdMovieTypeID (number to match lookup table for filtering)
DvdSynopsis

I'm a bit confused about trelDisc and trelEpisode.
These are tables? and I need to create relationship with
DVD_ID---DVD_ID
DiscID---DiscID


Thanks much,
James

Both John and I have pointed you in the direction that could alleviate the
problem you're seeing ... by not using that approach!

If you elect to continue stuffing a memo field rather than using one
record for each description, you can expect to have to keep developing
work-arounds. Access expects well-normalized data, and it doesn't sound
like your table structure is well-normalized.

The example you provided could be (alternatively) structured something
like (untested):

tblDVD
DVD_ID
DVD_Title

trelDisc
DiscID
DVD_ID (the 'parent')
?other disc-related data

trelEpisode
EpisodeID
DiscID
EpisodeNumber
EpisodeTitle
EpisodeDescription

With this design, I can envision finding all the descriptions
([EpisodeDescription] for the DVD entitled "X-Files - Season 1", no matter
how many discs it is spread across.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

James

I believe one of the reasons you are running into issues is that you are
"trying to drive nails with a chainsaw". A single spreadsheet might be how
you'd handle this in Excel, but in a relational database, you'd use one
table for each "thingie", and relate them together via primary-foreign keys.

If I'm understanding your situation, one Title (I believe this is your
"DVD") can include multiple discs, and each Disc can contain multiple
Episodes. I would use one table for the Title (sorry, I named it "DVD"
before thinking enough), one for the discs (to show which Title they belong
to ... like children to a parent), and one table for the Episodes, to show
which disc they belong to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My tblDvd has 4 fields:

DvdID (autonumber)
DvdMovieTitle
DvdMovieTypeID (number to match lookup table for filtering)
DvdSynopsis

I'm a bit confused about trelDisc and trelEpisode.
These are tables? and I need to create relationship with
DVD_ID---DVD_ID
DiscID---DiscID


Thanks much,
James

Both John and I have pointed you in the direction that could alleviate
the problem you're seeing ... by not using that approach!

If you elect to continue stuffing a memo field rather than using one
record for each description, you can expect to have to keep developing
work-arounds. Access expects well-normalized data, and it doesn't sound
like your table structure is well-normalized.

The example you provided could be (alternatively) structured something
like (untested):

tblDVD
DVD_ID
DVD_Title

trelDisc
DiscID
DVD_ID (the 'parent')
?other disc-related data

trelEpisode
EpisodeID
DiscID
EpisodeNumber
EpisodeTitle
EpisodeDescription

With this design, I can envision finding all the descriptions
([EpisodeDescription] for the DVD entitled "X-Files - Season 1", no
matter how many discs it is spread across.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I remember Dragnet.
I don't think the episode titles and descriptions are actually two
billion bytes.
I also have successfully copied and pasted all episodes of a tv series -
X-Files
season 1. Only problem is at this time I'm unable to make changes to the
data
in the memo field. Keep getting that error.
Again, when I type them in manually I don't get the error.
Maybe I'll just put the episode titles in the memo field.

Thanks much for the assistance and sorry 'bout my tirade,
James

On Thu, 30 Jul 2009 21:32:57 -0400, "JamesJ"
<jjy@darwin_roadrunner.com>
wrote:

Nothing to do with parent/child.
A tv series season has say 5 discs with 4 episodes on each disc.
Originally I created 5 records (one for each disc) with 4 episode
descriptions in each record
that I put into the memo field.
Instead of having 5 records I want to make 1 record and in the memo
field
have: Disc 1, with the
4 episode descriptions, Disc 2 with the 4 episode descriptions and so
on
all in the memo field.
But, after pasting the existing descriptions from the existing records
the
memo field gets corrupted.

Am I making sense?

Your actions make sense... but your table structure doesn't!

If you want to be able to search for a description and identify which
episode
had the text for which you're searching, you're defeating your own
purpose by
creating one enormous memo field for the entire season. You'ld do much
better
to have a set of related tables: Shows (e.g. "Dragnet"); Seasons (e.g.
1962);
Episodes (e.g. 5). The episode description would be in a single record
in the
Episodes table.

(Yes, I'm showing my age <g>)

You'll still have trouble if in fact your indefagitable fingers are
indeed
typing over TWO BILLION BYTES of description... that's hundreds of
books
worth, and your database will crash regardless of whether this is in
one memo
field in one record or in scores of smaller memo fields in scores of
records.
How big is your database compacted?

I think you're right, there is a bug - but it has to do with copy and
pasting
data. I've heard of (and experienced) multiple problems with entering
data in
that manner.

 

[JamesJ]

This will take some doing but I think I get the point.
I'll print out these threads for reference.

Thanks,
james

James

I believe one of the reasons you are running into issues is that you are
"trying to drive nails with a chainsaw". A single spreadsheet might be
how you'd handle this in Excel, but in a relational database, you'd use
one table for each "thingie", and relate them together via primary-foreign
keys.

If I'm understanding your situation, one Title (I believe this is your
"DVD") can include multiple discs, and each Disc can contain multiple
Episodes. I would use one table for the Title (sorry, I named it "DVD"
before thinking enough), one for the discs (to show which Title they
belong to ... like children to a parent), and one table for the Episodes,
to show which disc they belong to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My tblDvd has 4 fields:

DvdID (autonumber)
DvdMovieTitle
DvdMovieTypeID (number to match lookup table for filtering)
DvdSynopsis

I'm a bit confused about trelDisc and trelEpisode.
These are tables? and I need to create relationship with
DVD_ID---DVD_ID
DiscID---DiscID


Thanks much,
James

Both John and I have pointed you in the direction that could alleviate
the problem you're seeing ... by not using that approach!

If you elect to continue stuffing a memo field rather than using one
record for each description, you can expect to have to keep developing
work-arounds. Access expects well-normalized data, and it doesn't sound
like your table structure is well-normalized.

The example you provided could be (alternatively) structured something
like (untested):

tblDVD
DVD_ID
DVD_Title

trelDisc
DiscID
DVD_ID (the 'parent')
?other disc-related data

trelEpisode
EpisodeID
DiscID
EpisodeNumber
EpisodeTitle
EpisodeDescription

With this design, I can envision finding all the descriptions
([EpisodeDescription] for the DVD entitled "X-Files - Season 1", no
matter how many discs it is spread across.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I remember Dragnet.
I don't think the episode titles and descriptions are actually two
billion bytes.
I also have successfully copied and pasted all episodes of a tv
series - X-Files
season 1. Only problem is at this time I'm unable to make changes to
the data
in the memo field. Keep getting that error.
Again, when I type them in manually I don't get the error.
Maybe I'll just put the episode titles in the memo field.

Thanks much for the assistance and sorry 'bout my tirade,
James

On Thu, 30 Jul 2009 21:32:57 -0400, "JamesJ"
<jjy@darwin_roadrunner.com>
wrote:

Nothing to do with parent/child.
A tv series season has say 5 discs with 4 episodes on each disc.
Originally I created 5 records (one for each disc) with 4 episode
descriptions in each record
that I put into the memo field.
Instead of having 5 records I want to make 1 record and in the memo
field
have: Disc 1, with the
4 episode descriptions, Disc 2 with the 4 episode descriptions and so
on
all in the memo field.
But, after pasting the existing descriptions from the existing records
the
memo field gets corrupted.

Am I making sense?

Your actions make sense... but your table structure doesn't!

If you want to be able to search for a description and identify which
episode
had the text for which you're searching, you're defeating your own
purpose by
creating one enormous memo field for the entire season. You'ld do much
better
to have a set of related tables: Shows (e.g. "Dragnet"); Seasons (e.g.
1962);
Episodes (e.g. 5). The episode description would be in a single record
in the
Episodes table.

(Yes, I'm showing my age <g>)

You'll still have trouble if in fact your indefagitable fingers are
indeed
typing over TWO BILLION BYTES of description... that's hundreds of
books
worth, and your database will crash regardless of whether this is in
one memo
field in one record or in scores of smaller memo fields in scores of
records.
How big is your database compacted?

I think you're right, there is a bug - but it has to do with copy and
pasting
data. I've heard of (and experienced) multiple problems with entering
data in
that manner.

 

[Jeff Boyce]

James

You might try posting back to the access.tablesdbdesign newsgroup with your
notions for table structure. Folks there tend to focus more on helping get
the data right (it all starts with the data!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

This will take some doing but I think I get the point.
I'll print out these threads for reference.

Thanks,
james

James

I believe one of the reasons you are running into issues is that you are
"trying to drive nails with a chainsaw". A single spreadsheet might be
how you'd handle this in Excel, but in a relational database, you'd use
one table for each "thingie", and relate them together via
primary-foreign keys.

If I'm understanding your situation, one Title (I believe this is your
"DVD") can include multiple discs, and each Disc can contain multiple
Episodes. I would use one table for the Title (sorry, I named it "DVD"
before thinking enough), one for the discs (to show which Title they
belong to ... like children to a parent), and one table for the Episodes,
to show which disc they belong to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My tblDvd has 4 fields:

DvdID (autonumber)
DvdMovieTitle
DvdMovieTypeID (number to match lookup table for filtering)
DvdSynopsis

I'm a bit confused about trelDisc and trelEpisode.
These are tables? and I need to create relationship with
DVD_ID---DVD_ID
DiscID---DiscID


Thanks much,
James

Both John and I have pointed you in the direction that could alleviate
the problem you're seeing ... by not using that approach!

If you elect to continue stuffing a memo field rather than using one
record for each description, you can expect to have to keep developing
work-arounds. Access expects well-normalized data, and it doesn't
sound like your table structure is well-normalized.

The example you provided could be (alternatively) structured something
like (untested):

tblDVD
DVD_ID
DVD_Title

trelDisc
DiscID
DVD_ID (the 'parent')
?other disc-related data

trelEpisode
EpisodeID
DiscID
EpisodeNumber
EpisodeTitle
EpisodeDescription

With this design, I can envision finding all the descriptions
([EpisodeDescription] for the DVD entitled "X-Files - Season 1", no
matter how many discs it is spread across.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I remember Dragnet.
I don't think the episode titles and descriptions are actually two
billion bytes.
I also have successfully copied and pasted all episodes of a tv
series - X-Files
season 1. Only problem is at this time I'm unable to make changes to
the data
in the memo field. Keep getting that error.
Again, when I type them in manually I don't get the error.
Maybe I'll just put the episode titles in the memo field.

Thanks much for the assistance and sorry 'bout my tirade,
James

On Thu, 30 Jul 2009 21:32:57 -0400, "JamesJ"
<jjy@darwin_roadrunner.com>
wrote:

Nothing to do with parent/child.
A tv series season has say 5 discs with 4 episodes on each disc.
Originally I created 5 records (one for each disc) with 4 episode
descriptions in each record
that I put into the memo field.
Instead of having 5 records I want to make 1 record and in the memo
field
have: Disc 1, with the
4 episode descriptions, Disc 2 with the 4 episode descriptions and
so on
all in the memo field.
But, after pasting the existing descriptions from the existing
records the
memo field gets corrupted.

Am I making sense?

Your actions make sense... but your table structure doesn't!

If you want to be able to search for a description and identify which
episode
had the text for which you're searching, you're defeating your own
purpose by
creating one enormous memo field for the entire season. You'ld do
much better
to have a set of related tables: Shows (e.g. "Dragnet"); Seasons
(e.g. 1962);
Episodes (e.g. 5). The episode description would be in a single
record in the
Episodes table.

(Yes, I'm showing my age <g>)

You'll still have trouble if in fact your indefagitable fingers are
indeed
typing over TWO BILLION BYTES of description... that's hundreds of
books
worth, and your database will crash regardless of whether this is in
one memo
field in one record or in scores of smaller memo fields in scores of
records.
How big is your database compacted?

I think you're right, there is a bug - but it has to do with copy and
pasting
data. I've heard of (and experienced) multiple problems with entering
data in
that manner.

 

[laskowv]

If you will "Google" for sample access libraries; I know that there is one
written already for this called "movies library" or something like that.
I'll look for the link in my different browsers. I know I saved it or even
might have downloaded it a while back just for reference.

Once downloaded, you can just enhance what they have. I know one is on the
Microsoft website for 2007. I would try there first.

Valerie

James

You might try posting back to the access.tablesdbdesign newsgroup with your
notions for table structure. Folks there tend to focus more on helping get
the data right (it all starts with the data!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

This will take some doing but I think I get the point.
I'll print out these threads for reference.

Thanks,
james

James

I believe one of the reasons you are running into issues is that you are
"trying to drive nails with a chainsaw". A single spreadsheet might be
how you'd handle this in Excel, but in a relational database, you'd use
one table for each "thingie", and relate them together via
primary-foreign keys.

If I'm understanding your situation, one Title (I believe this is your
"DVD") can include multiple discs, and each Disc can contain multiple
Episodes. I would use one table for the Title (sorry, I named it "DVD"
before thinking enough), one for the discs (to show which Title they
belong to ... like children to a parent), and one table for the Episodes,
to show which disc they belong to.

Regards

Jeff Boyce
Microsoft Office/Access MVP



My tblDvd has 4 fields:

DvdID (autonumber)
DvdMovieTitle
DvdMovieTypeID (number to match lookup table for filtering)
DvdSynopsis

I'm a bit confused about trelDisc and trelEpisode.
These are tables? and I need to create relationship with
DVD_ID---DVD_ID
DiscID---DiscID


Thanks much,
James

Both John and I have pointed you in the direction that could alleviate
the problem you're seeing ... by not using that approach!

If you elect to continue stuffing a memo field rather than using one
record for each description, you can expect to have to keep developing
work-arounds. Access expects well-normalized data, and it doesn't
sound like your table structure is well-normalized.

The example you provided could be (alternatively) structured something
like (untested):

tblDVD
DVD_ID
DVD_Title

trelDisc
DiscID
DVD_ID (the 'parent')
?other disc-related data

trelEpisode
EpisodeID
DiscID
EpisodeNumber
EpisodeTitle
EpisodeDescription

With this design, I can envision finding all the descriptions
([EpisodeDescription] for the DVD entitled "X-Files - Season 1", no
matter how many discs it is spread across.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I remember Dragnet.
I don't think the episode titles and descriptions are actually two
billion bytes.
I also have successfully copied and pasted all episodes of a tv
series - X-Files
season 1. Only problem is at this time I'm unable to make changes to
the data
in the memo field. Keep getting that error.
Again, when I type them in manually I don't get the error.
Maybe I'll just put the episode titles in the memo field.

Thanks much for the assistance and sorry 'bout my tirade,
James

On Thu, 30 Jul 2009 21:32:57 -0400, "JamesJ"
<jjy@darwin_roadrunner.com>
wrote:

Nothing to do with parent/child.
A tv series season has say 5 discs with 4 episodes on each disc.
Originally I created 5 records (one for each disc) with 4 episode
descriptions in each record
that I put into the memo field.
Instead of having 5 records I want to make 1 record and in the memo
field
have: Disc 1, with the
4 episode descriptions, Disc 2 with the 4 episode descriptions and
so on
all in the memo field.
But, after pasting the existing descriptions from the existing
records the
memo field gets corrupted.

Am I making sense?

Your actions make sense... but your table structure doesn't!

If you want to be able to search for a description and identify which
episode
had the text for which you're searching, you're defeating your own
purpose by
creating one enormous memo field for the entire season. You'ld do
much better
to have a set of related tables: Shows (e.g. "Dragnet"); Seasons
(e.g. 1962);
Episodes (e.g. 5). The episode description would be in a single
record in the
Episodes table.

(Yes, I'm showing my age <g>)

You'll still have trouble if in fact your indefagitable fingers are
indeed
typing over TWO BILLION BYTES of description... that's hundreds of
books
worth, and your database will crash regardless of whether this is in
one memo
field in one record or in scores of smaller memo fields in scores of
records.
How big is your database compacted?

I think you're right, there is a bug - but it has to do with copy and
pasting
data. I've heard of (and experienced) multiple problems with entering
data in
that manner.