Access data on a Server

I agree with Jim ...

Something a simple as a poorly constructed SQL query can make an application run very inefficiently giving the impression that your server is slow or not working properly.  If you are having problems ... Unless you have some enormous number of users and records, a properly structured Access application will perform will highly acceptable results.  I would look at the application first, IMO.

It's like buying expensive marble tile ... the end result will only be a good as the contractor who cut and installed it.

ET

In regards to ET' points, I guess one way to sum that up is that performance is only as good as your weakest link and it's always something your chipping away at.

As a little more direct answer to your question, I would put a few users on and then monitor bandwidth on the server right from task manager.   I'd also probably check and make sure the server has free physical memory.

 In short, with a half dozen or so users, you should be able to get a fair idea of how things will run just by looking at task manager.

Jim.

I rarely use sql code. I find the access QBE grid almost always gets the result that I want.

Here is something I gleaned from my brother. (Hardware fanatic)

Old servers are 10MGb (0.8Mbite)
Average Servers are 10 times as fast
Modern servers are 10 times faster than that.

But a server can be likened to a motorway with 6 lanes. If set up badly, traffic can be directed mainly through one lane, with inevitable results. Also if using a hub (whatever that is) to send data to different computers where there maybe say 5 end users, with each packet of data sent the server asks each computer if it is the one it should be sending data to. However if a switch is used then the switch learns which computer is which and avoids this waste of time. So if like myself you are installing an app and wonder what the hell is going wrong it's worth noting that a lot of other stuff has to be right before you can expect a smoothly opperating application.

<<I rarely use sql code. I find the access QBE grid almost always gets the result that I want.>>

Using the QBE grid still generates the SQL code needed to run your queries in Access.  It's just a more user friendly interface.

You can still design inefficient queries using the QBE grid.  

As mentioned earlier there's a lot of variables that can determine how an Access app functions in a networked environment.

ET

<<Modern servers are 10 times faster than that.>>

  I was going to add to my last comment but didn't that with modern hardware, it's almost always the app that's an issue rather then the hardware.  That's an over generalization to some extent, but mostly true.

  <<Also if using a hub (whatever that is) to send>>

 and that's a good example; a hub and a switch are almost identical except for the fact that you noted (a hub sends traffice to every port, needed or not - a switch only sends the traffic to the port that should get it).

 Years ago, switches were expensive, so often you use a switch only at critical points and hubs everywhere else.

 But today, switches are common place and it's rare to find a hub any more as you can get a 16 port GB switch for $125.  

 Unless your talking hundreds of users or tables with millions of rows, even entry level hardware is not going to be your problem.

 What you do in development will have far greater impact.

Jim.

Thanks ET

This slow server (Or my App) happened recently where I had no problems with other servers.

All that was required from the Data was enough info to fill a combo box. I have checked the sql and it looks pretty straight forward.

Here it is:
SELECT FrameType.ID, FrameType.FrameMaterial AS Material, FrameType.Supplier, FrameType.FrameType, FrameType.FF, FrameType.Depth AS Width, FrameType.TotalFT AS Thickness, FrameType.TimberMargin AS MarkUp, FrameType.FrameAssembly AS [Plus/Mtr], FrameFR.FireRating, (Round(((1+([Waste%]/100))*[TimberThick]*[TimberWidth]*[TimberCostM³])/10000)/100)+[FrameAssembly] AS Cost, Round((100+[TimberMargin])*[Cost]/100,2) AS Sell
FROM FrameType INNER JOIN FrameFR ON FrameType.FrameMaterial = FrameFR.FrameMaterial
WHERE (((FrameType.FrameMaterial) Like nz([Forms]![ProjectForm]![Details].[Form]![FM],"*")) AND ((FrameType.Supplier) Like nz([Forms]![ProjectForm]![Details].[Form]![FSupplier],"*")) AND ((FrameType.FF) Like nz([Forms]![ProjectForm]![Details].[Form]![FF],"*")) AND ((FrameFR.FireRating) Like Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4) & "*"))
ORDER BY FrameType.FrameMaterial, FrameType.Supplier, FrameType.Depth, FrameType.TotalFT, (Round(((1+([Waste%]/100))*[TimberThick]*[TimberWidth]*[TimberCostM³])/10000)/100)+[FrameAssembly];

Can you see any obvious problem here?

I can see where the Calculations, Join and Wildcard Conditional Parameters can take a while to fill a combo box.  Is this a Front-End / Back-End setup where the tables only reside on the server?  If so, is it a Access Back-End or SQl Server Back-End??

Here is an easy test ... Take the query above and change it to a Make Table query creating TblTemp1.  Then change the query of your Combo Box to use TblTemp1 and see how the response is over your network.  This will confirm if the delay is created by waiting for the SQL to run or if it is due to network traffic.

ET

This is an Access (I believe Jet engine) back end on server front end on local pc.

The maximum number of records is likely to be well below 3000

That test is a good idea.

Ok, with 3000 records you should not see the type of slow response.  Your query for the combo box has a lot of Wildcards which will perform slower ... it has to look through everything.  Going back to what we were discussing earlier relating to how you SQL is structured can mak a lot of difference.  

I use a lot of temp tables and clear them out when finished to avoid that specific issue.

ET

<<The maximum number of records is likely to be well below 3000>>

Like I said, what you do in development will have the greatest impact.

Any of todays servers/networks will be more then capable of handling that.

Jim.

Great help thank you both.