Solved

MS Access 2007 - Does this product take advantage of multiple processors?

Posted on 2008-10-22
11
679 Views
Last Modified: 2013-11-29
MS Access 2007 - Does this product take advantage of multiple processors? Does SQL Server 2005?  I have a database that now has upto 6 agents using it at once and the database is starting to run slow.  My backend database resides on a dual processor XP machine with 2GB of RAM.  My customer would probably buy a 4 quad processor if I asked them, but does Access even support these extra processors.

Thanks
0
Comment
Question by:SheahaST
11 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781113
It really doesn't that matter much about the CPU on the backend; make sure that you have a decent network connection and lots of RAM and goood CPU on the client though.

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781129
What is the backend database, Access or SQL Server and what version of SQL Server if it is?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781151
Access does no processing on the machine that holds the database. You run the query which is passed to the file server and all the data is returned to the workstation for processing. SQL Server is a different story.
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 300 total points
ID: 22781258
See attached illustration
sqlservervsfileserver.gif
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22781366
A lot has been written - Google - windows xp quad core
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 22781425
>> SQL Server is a different story.

There are two stories for a SQL Server back-end. If you are using an ADO solution then the majority of the processing is done on the back-end and it delivers only the requested records/dataset, provided you take the time to refine the dataset you requested.

If you open an ADO recordset as "SELECT * FROM EmpTable" and have 10K rows in the table it will try to return all 10K rows. Modify it to "SELECT * FROM EmpTable WHERE ActiveEmp = 1" it will only return the current 500 active employees.

If you are using DAO and linked tables it will always return all 10K rows and then it is client side that processes the rest of the query.

Regardless, CPU is generally never the bottle neck in DB processing on server or client side, it is memory. If you crank the memory to 4GB you will always get more bang for the buck than CPU -- note that is my opinion from experience.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781427
What OS is on the client systems?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781576
Access 2007 will not bind to more than one cpu.
0
 

Author Comment

by:SheahaST
ID: 22788937
Thanks for all the help. The client machines and the backend machine are all XPs.  The backend is Access 2007.  Does SQL 2005 use more than one cpu?  How do I know if I am using ADO or DAO?  I am definitely using linked tables.  Thanks again.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22789119
>> Does SQL 2005 use more than one cpu?

Workgroup edition can do 2, Standard edition does 4 and Enterprise is how many you can throw at it. Note that Standard and above can be licensed per physical socket -- not the number of actual CPU's So if you have 2 dou core or a single Quad Core you would need 2 licenses vs 1 license.

SQL Server 2005 Features Comparison
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

>> I am definitely using linked tables.

That means that you should be using DAO. I haven't had a crack at Acc07 though.

Another way to cut down on how much is pulled to the local machine is to create simple views in the SQL db and then from there link to the view. From my example above

Create View Active_Emp
As
 SELECT *
 FROM EmpTable
 WHERE ActiveEmp = 1

and it can be linked in and worked like a table.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22876939
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 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

22 Experts available now in Live!

Get 1:1 Help Now