Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-13-2018, 07:14 AM   #1
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,179
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Exclamation MS Access Disk Buffer Size

Researching a question for another thread, I came across an interesting article. All this time, I thought that an Access disk buffer was 2048 bytes. But that appears to be wrong.

https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx

Read the description for MaxBufferSize and see if that matches what you remember about it. It is not clear from the discussion that this applies to Access BE files because it is under an ODBC heading. However, the mere fact that Access CAN vary this buffer size is something I had not seen before.

So I did some more digging and here is how you can change buffer size on a "native" Access BE file.

http://www.utteraccess.com/forum/ind...wtopic=1982916

Don't know if anyone even cares, but I just found it interesting.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 03-13-2018, 08:01 AM   #2
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,189
Thanks: 83
Thanked 1,530 Times in 1,426 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: MS Access Disk Buffer Size

If that will help prevent a system resource exceeded error when handling huge external files e.g. JSON file of 152MB, I would be very interested.

I will look into this further. Thanks
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-13-2018, 09:17 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,442
Thanks: 51
Thanked 944 Times in 913 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: MS Access Disk Buffer Size

Offhand, I thought the buffer/page size was related to the size of the hard disk.

ie, any hard disk had a limit of n pages, so as the disk got bigger, the page size increased as well.

I may be wrong with this, though, but on that basis, it may well have been 2K at some point in time.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 03-13-2018, 01:35 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,179
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: MS Access Disk Buffer Size

Dave, to my (sometimes limited) understanding, the buffer size is often tied to the memory management granularity in some bizarre way, not to the disk size. However, I believe that originally, on smaller (and older) systems, a memory page and a disk block were the same size (512 bytes or 1/2 KB). As disks got larger and memory got larger, the page size grew and it became possible to make disk buffers bigger (large enough to fill a page in a single disk transfer).

The part that got me searching was the simple fact that if Access could modify its disk buffer size for an ODBC driver, it was POSSIBLE that it could modify its disk buffer size for a native Access BE file. Hence the reference to that Utter Access article.

Colin, if you use this, note that the Utter Access article discusses issues when you make the buffer too big. So if you play around with it, take baby steps.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 03-13-2018, 01:51 PM   #5
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,189
Thanks: 83
Thanked 1,530 Times in 1,426 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: MS Access Disk Buffer Size

Thanks for warning.
I was going to try small changes anyway as I did for MaxLocksPerFile settings

I have one monster JSON file that always creates errors when I try to load into Access. Either 'out of memory' or 'system resource exceeded'. I think it's a badly designed file but as it's so big I can't load it so I can fix it.

It's worth a try anyway.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-15-2018, 11:27 AM   #6
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,189
Thanks: 83
Thanked 1,530 Times in 1,426 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: MS Access Disk Buffer Size

I've just looked at the registry setting I had for MaxBufferSize
I got a surprise - it was zero!



I also found the MaxLocksPerFile had reverted from 15000 that I set a few months ago back to the default 9500. I've switched this back again

I've now set the MaxBufferSize to 4096 as suggested in the link given by the Doc
However, it hasn't solved my issue with reading a massive JSON file into Access. I'm now getting error 7 (Out of memory) instead

There a useful link on the FMS site. Does anyone have any useful advice on fixing my out of memory error (Access 2010 & 2016).

I've already tried all the usual methods of checking databases and its only a problem for the one huge JSON import file
Attached Images
File Type: png MaxBufferSize.PNG (40.5 KB, 257 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-15-2018, 07:21 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,179
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: MS Access Disk Buffer Size

Out of memory? I sincerely hope it didn't mean "Out of physical memory." It is possible that you ran out of virtual memory, which might mean that your swap file isn't big enough.

Here is an article for possible causes of "Out of Memory" error:

https://msdn.microsoft.com/en-us/vba...memory-error-7

IF the problem is virtual memory, you fix that here:

Start >> Control Panel >> System >> Performance >> (Tab) Advanced >> Performance >> Advanced >> Virtual Memory >> Change ... and then adjust the amount of virtual memory you will allow.

However, if that isn't it, there is the issue that no memory segment can exceed 64 KB, so do you have big arrays in memory or do you have a subroutine in any module where the size of the array might exceed 64 KB? Do you have a few large modules or a lot of smaller ones? If you have exceptionally large modules with lots of piddly-squat small subs and functions, supposedly that can be bad (though to be honest, unless you are dinking around with arrays, that would be hard to reach).

As to having registry entries reset, for Win 10 there is chatter regarding Windows Update services doing this. No guaranteed fix that doesn't involve physical violence against the Windows Product Manager.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 03-16-2018, 07:35 AM   #8
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,189
Thanks: 83
Thanked 1,530 Times in 1,426 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: MS Access Disk Buffer Size

Doc
Thanks for the link - I don't think any of the points raised apply in this case

Error 7 - Out of memory
It's a 6 year old PC but I'd be VERY surprised if its an issue with physical memory (4GB) or virtual memory - range 4096kB to 8192kB

The ONLY time I see this error is with this one file where it almost always occurs.
However occasionally I get error 3055/3058 - System resource exceeded & yesterday I got a new one (to me) - error 14 - out of string space

At least I no longer get error 3052 since increasing max locks per file



I think the issue is probably with the file structure rather than my machine.
Having said that I just tested the same file on my equally old & less powerful laptop - it opened without error - took almost 100 seconds but it worked!
Attached Images
File Type: png ErrorCodes.PNG (36.3 KB, 238 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 03-21-2018, 10:25 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,179
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: MS Access Disk Buffer Size

Quote:
error 14 - out of string space
Colin, this indicates a really complex problem that may be due to a badly chosen string manipulation pattern. Here is what I understand about "string space" - and it will probably be about as clear as space-time string theory. I hope it is better than that...

String space is part of a process memory that is pre-allocated as a working area. When you create and dissolve strings in VBA, the intermediates have to go somewhere, and this "somewhere" is part of a program structure known as the heap. The string VARIABLE actually doesn't change places for the life of the string - but a component of that variable DOES change. You see, a string variable is actually a BSTR structure, which you can look up online.

http://bytecomb.com/vba-internals-st...ters-in-depth/

There is a string count and a string location in the actual structure. The important thing to know about this is, you can completely replace the value of a string without moving the string variable - which is good, because for subroutines, almost all variables (excluding those declared as Static) are on the stack.

The way that VBA (and many other languages) optimize string usage is that there is this area where if you have a string, the variable has a string length and a string "actual location," and that actual location is a working memory area that is part of your process virtual memory.

If you modify a string, rather than diddle the bytes, VBA makes a new copy of the modified string elsewhere and then change the length and location fields. Then VBA releases the previous set of bytes for possible re-use. Possible - but due to potential size mismatches, unlikely. Look up "memory + garbage collection" for a discussion on the perils of that particular procedure.

This working area in the heap cannot be infinite in size, obviously. A certain amount of memory space has to be set aside for it - and the starting size of course is some obscure tuning parameter. When you create and destroy strings A LOT, particularly with size changes, you run into the problem of "thrashing" the working area and causing it to be badly fragmented. Once it gets fragmented badly enough, you cannot create new strings of any appreciable length because you can't allocate contiguous bytes for it.

THEREFORE, if you see the "out of string space" message, you have an issue with the way strings are being created and destroyed in your program. Note also that the stack depth IS NOT an issue because all routines in your app share the same heap area. When the total of all functions and subs "blows out" your heap space, you get that error.

https://support.microsoft.com/en-us/...of-the-desktop - is an article on how to change the size of your program heap. Note also that an x32 system and an x64 system will have vastly different heap sizes because x64 systems have more room to "play with" in terms of physical memory allocation.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
access 97 xpmode disk error dmm_nyc Windows 0 08-16-2012 08:08 PM
Stringbuilder buffer overflowed by unmanaged code oumahexi Crystal Reports 4 02-04-2010 08:15 AM
Print buffer issue- printing Access labels jancliff Reports 0 06-20-2005 05:28 AM
Optimising memory use and disk access to improve speed DanR General 7 05-28-2003 09:02 PM
[SOLVED] ???EMPTY BUFFER??? fred Modules & VBA 0 04-12-2000 12:32 PM




All times are GMT -8. The time now is 07:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World