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?
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?
ASKER
@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?
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?
<<@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?anchorAnswerId=5849926#a5849926
Jim.
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?anchorAnswerId=5849926#a5849926
Jim.
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.
Jim.
I found a comment that was a bit clearer on calculating the record size:
https://www.experts-exchange.com/questions/27403439/microsoft-access-vba-code-to-see-if-a-record-is-being-edited-in-a-multiuser-environment.html?anchorAnswerId=36992560#a36992560
Jim.
https://www.experts-exchange.com/questions/27403439/microsoft-access-vba-code-to-see-if-a-record-is-being-edited-in-a-multiuser-environment.html?anchorAnswerId=36992560#a36992560
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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?
But please explain Unicode... what do I set Unicode to in order to use the least space... compression On?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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.
<< 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.
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.
ASKER
Thanks again Jim... that did it!
Good to hear!
Jim.
Jim.
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.