Solved

Access WIndows 2012

Posted on 2014-10-30
10
215 Views
Last Modified: 2014-11-05
Hello

I have 2 servers on my network.
Server A: Windows 2008 SP2 32bit (6 years old)
Server B : Windows 2012 R2 Essential 64bit (new server)

I have an old ACCESS application (4 users) running on Access 2003. The application is a single "small" 60K DBF file (but many tables, many queries, high complexity). it has been developed internally many years ago and runs fine on Access 2003 (client) on Windows 2008 SP2 (server)

My Problem:

WHen the DBF is simply moved to server2 (Windows 2012 R2 64bit) it becomes very very slow. About 3 time slower than on Windows 2008. When a single person open the DBF file it is more or less OK to work but when 2 persons open the DBF file at the same time it becomes 5 times as slow. I am not even trying with 3 or 4 persons simultaneously...

Database on Server2 becomes very very sensitive to the fact that 2 (or more) persons are working at the same time on the DBF

Network switch has ben replaced and all PC are now on 1 Gb

I have done many tests and I am out of ideas now...

If someone could help...

Thank you
0
Comment
Question by:gadsad
  • 6
  • 3
10 Comments
 

Expert Comment

by:pressMac
ID: 40413318
My experience that when using any MS Access that uses ACE database engine.  I tested only 2010 and 2013, it is very slow on data access.  The JET database engine benchmarked much faster in my testing.  I don't what engine access uses when connected to a DBF.  ( I thought that was typo at first)

On our tests, even hardware that was way faster, could not make up the difference.  We had to punt and change to SQL Server.
ms-access-bencmark.jpg
0
 

Author Comment

by:gadsad
ID: 40413337
there is no SQL engine, or other database engine except the single DBF file on the Windows 2008/2012 share folder
Nothing else. And the client is an Access 2003 opening this DBF

In your graph what is ACCDB ?

Thanks
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40413338
gadsad,

 You've already asked this question:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2008/Q_28544466.html

 and didn't return to the thread.  Have you tried any of those suggestions?

Jim.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:gadsad
ID: 40413401
Hello Jim

You are right but the problem is still not solved

I replaced switch to 1 Gb, disable antivirus, firewall, made Many many test but still no good.

DBF is VERY very slow to respond in the 2012 server (whereas is it very fast on Windows 2008 server)

I did not disable SMB3 on server (I am a little afraid to mess something). Should I do that?

Regarding your suggestion to do : dbEngine.SetOption "MaxBuffers", 65535
I am not a Access programmer, can I enter this command somewhere in Access 2003?

THanks again and very much
0
 

Author Comment

by:gadsad
ID: 40413452
another idea I had

Could migrate the MDB to Access 2010 (it is not working on Access 2003) could have any effect on performance and could solve my problem?

Thanks
0
 
LVL 57
ID: 40413475
<<I did not disable SMB3 on server (I am a little afraid to mess something). Should I do that? >>

 No.  As I suggested, leave two copies of the app open, then try and have the users use it.  If you still have slowness, then it is not related to OPLOCKS and more than likely, nothing to do with SMB2/3.

<<dbEngine.SetOption "MaxBuffers", 65535 >>

 This expands the JET cache and it can be placed in the app, but what may be simpler for you is a registry edit so you don't have to mess with the app.  The Key is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0.

 The key is MaxBuffers.  Set it to 65535 and see if it makes any difference.  NOTE: this must be done on the machine running MSACCESS.EXE (in other words, the client stations).

Jim.
0
 

Author Comment

by:gadsad
ID: 40413492
Yes registry edit is far simpler for me
I will do it tomorrow and keep you informed

thank you
0
 

Author Comment

by:gadsad
ID: 40415242
The key is MaxBuffers.  Set it to 65535

THis key MaxBuffers did not exist
SO I created the key as a dword 32 bits and set 65535 in decimal
is it the right thing?

On an other Win7 SP1 PC,  I did not found the key in the expected location but in another location in
HKLM\SOFT\Wow6432node\Microsoft\Jet\.....

is it normal ?
Thanks
0
 
LVL 57
ID: 40415449
<<HKLM\SOFT\Wow6432node\Microsoft\Jet\.....
>>

That would be the correct key for a 32 bit install of Office on a 64 bit OS.   The one I gave you would be for 64 bit Office

Jim.
0
 

Author Closing Comment

by:gadsad
ID: 40424744
Unfortunately I did not found another solution but toput the MDB out of the Windows 2012 R2 server and to put it on a share on a WIndows 7 SP1 PC ant it works fine like that

I tried it all but nothing else worked

Thanks you anyway

Regards
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use VBA to update a record in a table in MS Access? 12 35
Restoring a form to its default settings 3 26
sql statement - 9 23
Delete QueryDef IF it Exists: Access VBA 5 27
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now