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
FattyPoAsked:
Who is Participating?
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.

Gustav BrockCIOCommented:
Access 2003 runs fine under Win7 64-bit.
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
0
FattyPoAuthor Commented:
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.
0
Gustav BrockCIOCommented:
Except that double-checking the References, I have no more ideas.

/gustav
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

FattyPoAuthor Commented:
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.
0
FattyPoAuthor Commented:
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.
0
rindiCommented:
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
0
FattyPoAuthor Commented:
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::AllocatePages:
        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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
FattyPoAuthor Commented:
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.
0
FattyPoAuthor Commented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
MaxBuffers is a jet engine setting, which is in the registry.   It also can be set at app level.

If you don't have access to the app, then you'll need to change the registry setting on this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0

All the settings are described here:

http://office.microsoft.com/en-us/access-help/initializing-the-microsoft-jet-4-0-database-engine-driver-HP001032161.aspx

Maxbuffers is about half way down the page if your interested in reading it.  The formula given is used when the setting is 0, which is the default.

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
FattyPoAuthor Commented:
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.
0
randy ftechCommented:
Hi, I had an end user adjust maxbuffers for HKEY_LOCAL_MACHNE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 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?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
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
Windows 7

From novice to tech pro — start learning today.