Need way to programmatically calculate the site of a MS Access record

I'm using MS Access 2K and my staff occasionally enters too much data for a specific record.  I suspect the max record length for a record is being exceeded, however, a problem does not show up until the employee runs a query to append the table to a table on the back end.

Access fails when the append query is run and produces a "record too large" error, without any indication of which record has failed.

The table being appended may contain 50K or more records, so we need a programmatic way to identify the record that is too large.

I believe the max record size for Access 2K is 2018 without Memo fields but I'm not sure what that means.  For example, how many characters are in a double field, date, etc?


Can anyone help?
pcalabriaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There's no easy way to do that.

  Your best bet is to execute the append query with:

  CurrentDB().Execute <sql>, dbFailOnError

 and then trap the error, or open a recordset and append the record, again trapping the error.

Jim.
0
pcalabriaAuthor Commented:
@Jim... I'm hoping I can programmatically test the record size as part of the event procedure I use to save button.

I wrote a routine that loops through each record in the database and attempts to calculate the record size for each.

I loop through the fields collection and append the field (if its not a memo field) to a string.

The record that is causing trouble results in a string of 1528 characters which I do not understand.  With this said, this is the largest record in the table... and deleting the record solves the problem...

So why 1528??? and not 2048?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<@Jim... I'm hoping I can programmatically test the record size as part of the event procedure I use to save button.>>

  There's no real way to do that though; there are too many variables involved.

  The only way to "test" is to attempt a save, and trap an error if it occurs.

<<So why 1528??? and not 2048?>>

  There are a number of factors you can't account for.   For example, uni-code compression.   Memo data being stored with the main record rather than being moved to Long Value Pages.

<<I believe the max record size for Access 2K is 2018 without Memo fields but I'm not sure what that means.  For example, how many characters are in a double field, date, etc?>>

 This was as of JET 3.5.   Your in 4.0 format most likely, and it would be similar.

  https://www.experts-exchange.com/questions/20078308/Problem-with-record-locking-in-Access.html#a5849926

Jim.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The one change from that for 4.0 is the memo and OLE filed pointers.    In a quest to enhance performance, they are only stored on LVP's (Long  Value Pages) if the data exceeds 30 bytes.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The table being appended may contain 50K or more records, so we need a programmatic way to identify the record that is too large.>>

 I'm not sure how I missed this; I thought this was a single record save.

 What does the table look like?

 I would just focus on text fields.   For the reset of the fields, you can do a one time rough estimate of the fields and how much space they will take.

 The only other factor then is uni-code compression as it's one byte per character vs two if it's turned on and your using a Western European language (text would be compressed).   So you need to know if it's on or off.

  After totaling the length of the text fields and adding the "base" record size, anything over 4000 will get you in trouble most likely.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcalabriaAuthor Commented:
@Jim. That's my plan.. but I need to figure out what length to Test for... the record that causes the problem is 1908 characters... much less than your estimate.. possibly because I'm using access 2K....

But please explain Unicode... what do I set Unicode to in order to use the least space... compression On?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<problem is 1908 characters... much less than your estimate.. possibly because I'm using access 2K....>>

 Not really.   Your using Len() and not seeing strings as stored. You may have to use a max limit much less than 4000 depending on how your doing the calculations.   Also if you have a DB that is in older format, then your page size would be 2048 characters.

<<But please explain Unicode... what do I set Unicode to in order to use the least space... compression On?>>

Compression on, yes.

A unicode character in Access is coded as UTF-16, or with 16 bits.    But If your using a Western European language (such as English, Spanish, or German), then the leading byte will be 00.   This means it can be stripped off and the character is stored as UTF-8 with 8 bits.

 So a text string can be stored in just about half from what it normally would be (there are some additional flag characters in the string that indicate if compression is on or off for what follows).

 If your not using a Western European language though, it's not going to matter as it will not be able to compress it down.  Each character will get stored as two bytes.

 In short, compression on, use Len(), and use 4000 as your limit.

Jim.
0
pcalabriaAuthor Commented:
@jim. jim.. all my data is English with no support for other languages necessary.. are you suggesting that I can substantially reduce the size of my mDB files by turning on Unicode Compression?  I have many text fields set to 255 characters not using Unicode.

My database consists of eight or so back end MDBs which are between 1 and 1.4 GBs... so size is definitely an issues.

Is their a global way to make a change in an entire database?
I have one MDB file that contain 200+ tables.. each table with 200+ fields.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< are you suggesting that I can substantially reduce the size of my mDB files by turning on Unicode Compression?  >>

 You can possibly.   A page is still 4096 bytes, and it comes down to how many records can fit on a page.   Even with Unicode compression turned on, if only one record fits on a page, then nothing will change.

<<Is their a global way to make a change in an entire database?>>

 Yes.   There is virtually nothing you can't change in code.

 You would loop through the tableDefs collection, then the field collection, look for the text fields, then add the Unicodecompression property if it didn't already exist, and set it.

 However with that said, I don't believe anything will change until the records are edited.   Of course, you could code that as well<g>

Jim.
0
pcalabriaAuthor Commented:
Thanks again Jim... that did it!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Good to hear!

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.