ODBC connections to avoid bandwidth bottleneck on split database

[notableaudio]

Hello all,

I am currently working on improving my company's Access database performance. Here is a quick outline of the environment:

P2P network, with a dedicated "server" storing the Access BE and running Windows XP (will be upgrading to W7 soon)
~15 workstations are linked through cat 5 ethernet cables and a 24 port gigabit Netgear switch, but not all are active simultaneously (usually 10-12 active workstations at a time)
All workstations running Access are running Windows 7 64-bit.
The database in question barely exceeds 100MB in size.

I used the performance monitor to track performance of the dedicated "server". What I realized was the "server" had ample processing power and memory in the current configuration (no sustained hard faults, processor peak around 60% capacity and averages around 18%), but the lag in processing time seems to be coming from a bottleneck with the network (% bandwidth when running a query jumps to 100% and sits there until the query finishes executing). Given the large bandwidth, I realized that every time I run a query in Access, the BE is pushing a lot of raw data to the workstation over the network for processing in the Jet engine, rather than executing the query on the "server" and only sending the results to the workstation.

I'd like to set up pass through queries to keep the processing at the "server" to mitigate unnecessary network traffic. However, I don't know a lot about DSN's and ODBC connections. Is this a reasonable direction to improve lags when using the FE? I understand we will need to upgrade the "server" hardware given it will have an increased processing load, but assuming we have the hardware to keep up, should using pass-through's fix the network bottleneck?

Does it sound like I am on the correct path?

Thanks for any advice you may have on the subject. I apologize if I missed any tech details that would be helpful, just let me know if I missed something.

[InsuranceGuy]

You will have the same problem because your ODBC connection will only point to the file on the "server". If your queries are that intensive, could you push them to tables on the server computer itself on a frequent enough basis not to need to push large data to the clients for processing? If not, start considering SQL Server for data management.

[notableaudio]

You will have the same problem because your ODBC connection will only point to the file on the "server". If your queries are that intensive, could you push them to tables on the server computer itself on a frequent enough basis not to need to push large data to the clients for processing? If not, start considering SQL Server for data management.

Ok perfect, good to know ODBC won't really help. Most of the intensive queries are due to poor architecture, so I will just fix the issues with the design first and see if that alleviates enough traffic.

I have started saving query results as tables to increase performance, but that seems to be contrary to best practice since I'm really just calculating yields to use in production reports.

Unfortunately my manager only knows Access and will be resistant to move to a SQL Server BE, though I agree that would help.

Thanks for the info!