FattyPo
asked on
Windows 64 bit and old Access DB - Issue's Querying
Hi I have a customer who is in the process of upgrading their systems and company DB. The issue we have at present is the customer database was developed in Access 97 and 2000. All the XP machines and W7 32 bit PC's can access the DB fine utilising Access 2003.
The issue we have until their new DB is completed is that the new W7 64 bit PC's with Access 2003 installed can access certain parts of the DB but not others.
Is there any work around to this? Or do I wait until the W7 32 bit CD's arrive from HP and rebuild the platform?
Many thanks
The issue we have until their new DB is completed is that the new W7 64 bit PC's with Access 2003 installed can access certain parts of the DB but not others.
Is there any work around to this? Or do I wait until the W7 32 bit CD's arrive from HP and rebuild the platform?
Many thanks
ASKER
The issue is when they run query's access just hangs and never completes the task. The only difference between the PC's is that the 2 new ones are running W7 64 bit not 32.
But you can access certain parts of the DB no problem. The DB sits on a win2003 server if that makes any difference?
The PC's did have a later version of office installed which included Access, I did uninstall these prior to installing Office 2003 to use the version of access their DB requires.
I would like to avoid downgrading as you suggested for the issue with available ram.
But you can access certain parts of the DB no problem. The DB sits on a win2003 server if that makes any difference?
The PC's did have a later version of office installed which included Access, I did uninstall these prior to installing Office 2003 to use the version of access their DB requires.
I would like to avoid downgrading as you suggested for the issue with available ram.
Except that double-checking the References, I have no more ideas.
/gustav
/gustav
ASKER
The document that is generated when running a report query is generated within access. Could it be this that is the issue?
Very complicated references I believe, the customer doesn't want to invest too much time as they are in final stages of testing their new DB which works on runtime.
Very complicated references I believe, the customer doesn't want to invest too much time as they are in final stages of testing their new DB which works on runtime.
ASKER
I left access running for about 20 mins and it did eventually open the document. Tried it gain and it has hung. Turn off all AV in case the 64 bit version did anything differently. But this made no difference.
Install Windows XP mode on those PC's and run access 2003 from within that until you have upgraded the tool. On he host you can then install a more current version of office. XP mode is can be downloaded for free from the M$ site for Windows 7 pro and above.
http://windows.microsoft.com/en-us/windows7/products/features/windows-xp-mode
http://windows.microsoft.com/en-us/windows7/products/features/windows-xp-mode
ASKER
I am just downloading the,
I did find this, so may be an issue MS knows about.
I lodged a support call with Microsoft and they are aware of an issue with Windows 7/MS Access and multi-core processors. However, despite receiving many reports of the problem, they are yet to fix it or even publish a KB article.
Their explanation of the issue:
When ACE (Jet) tries to allocate page, it runs into the following routine:
msjet40!System::AllocatePa ges:
if (m_pagesInUse > m_workingLimit + 100)
Sleep(50);
This call to the Sleep method is here to flush memory, and try to garbage collect all data pages. In your dump, you can see that the m_pagesInUse (3769) is higher than the m_workingLimit (3584), so we’ll wait for a long time to flush all the data pages.
In multi-core computers, the ACE (Jet) allocate page by one core each time. Other cores are blocked in this time, hence Access need sleep more time in multi-core environment.
I did find this, so may be an issue MS knows about.
I lodged a support call with Microsoft and they are aware of an issue with Windows 7/MS Access and multi-core processors. However, despite receiving many reports of the problem, they are yet to fix it or even publish a KB article.
Their explanation of the issue:
When ACE (Jet) tries to allocate page, it runs into the following routine:
msjet40!System::AllocatePa
if (m_pagesInUse > m_workingLimit + 100)
Sleep(50);
This call to the Sleep method is here to flush memory, and try to garbage collect all data pages. In your dump, you can see that the m_pagesInUse (3769) is higher than the m_workingLimit (3584), so we’ll wait for a long time to flush all the data pages.
In multi-core computers, the ACE (Jet) allocate page by one core each time. Other cores are blocked in this time, hence Access need sleep more time in multi-core environment.
<<However, despite receiving many reports of the problem, they are yet to fix it or even publish a KB article.>>
While they are aware of it, the suggested work around is to increase the MAXBUFFERS setting to 65535.
That takes care of it in most cases, which is why I believe their not treating it with much urgency.
You can test that out easily enough with the dbEngine SetOption method at app startup.
Jim.
While they are aware of it, the suggested work around is to increase the MAXBUFFERS setting to 65535.
That takes care of it in most cases, which is why I believe their not treating it with much urgency.
You can test that out easily enough with the dbEngine SetOption method at app startup.
Jim.
ASKER
Thanks for the info Jim, how do I go about setting the maxbuffers to 65535. I have no experience of Access DB's as the customer wrote it themselves and being the MD is impossible to track down to get any changes.
ASKER
Hi Jim,
I have adjusted the registry. Customer is now running some tests but it looks promising. I will revert in an hour or so.
Thanks
I have adjusted the registry. Customer is now running some tests but it looks promising. I will revert in an hour or so.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<I have adjusted the registry. Customer is now running some tests but it looks promising. I will revert in an hour or so.>>
It doesn't always take care of the issue, but in many cases it does.
I know we had a number of people here who found a re-install of the OS would clear it up. Others did not find that to be the case. Never was able to pin down what was actually happening.
The maxbuffers setting though seems to take care of the multi-core problem pretty well.
I think the people that found a re-install of the OS had other issues.
This was happening a lot when Windows 2008 R2 first came out.
Jim.
It doesn't always take care of the issue, but in many cases it does.
I know we had a number of people here who found a re-install of the OS would clear it up. Others did not find that to be the case. Never was able to pin down what was actually happening.
The maxbuffers setting though seems to take care of the multi-core problem pretty well.
I think the people that found a re-install of the OS had other issues.
This was happening a lot when Windows 2008 R2 first came out.
Jim.
ASKER
Hi Jim, thanks for the fix. Adjusted the max buffer size on the W7 64 bit PC. Customer has run all their tests. And they are happy it is now working.
Rindi - I did try the XPMode and the DB did not even run due to network and resource errors.
Thanks for all your input guys.
Rindi - I did try the XPMode and the DB did not even run due to network and resource errors.
Thanks for all your input guys.
Hi, I had an end user adjust maxbuffers for HKEY_LOCAL_MACHNE\SOFTWARE \Wow6432No de\Microso ft\Jet\4.0 \Engines\J et 4.0 from 0 to 65535 to no avail. The user has Windows 7 64 and has the issue described above using Access 2010 or 2003 32bit and connects to the Access DB using an Oracle 11g 32-bit ODBC connection. Does connecting using an Oracle ODBC connection make a difference or do any other settings need to be adjusted?
ODBC connections don't use the JET Cache, so the setting doesn't apply.
Please ask a new question in regards to your problem.
Jim.
Please ask a new question in regards to your problem.
Jim.
What are the issues?
If at all possibile, you should not downgrade Win7 to 32-bit as you limit the available ram to about 3 GB.
/gustav