Access network traffic
Posted on 2009-12-21
I have a MS-A db in use by an organisation where IT dept. have raised concerns about possible future (not current) network traffic. They're keen for me to re-write to use SQL server as backend. System already split FE (on user's PC) + BE on server. Approx 15 potential users maybe 3 active at any one time. BE MDB approx 30MB but could grow to 150MB over next couple of years. 50 tables most with <100 records; 2 or 3 with potentially 10000; and 2-3 with 1M+. All tables linked with RI PKs etc. Converting the tables seems fairly straigtforward (I haven't used any fancy Access features) and all field names are already SS compliant. Main change would be Autonumber (to int(identity) in SS I think). All code (c.85000 lines in total) would need to be reviewed and edited to change DAO to ADO.
The change to SS may or may not happen (= be imposed upon me).
Meanwhile I'm seeking to make the system as efficient as I can so my question is: if a query in the FE includes a table from the BE with 1M records are all 1M records pulled across the network every time, even if only 200 are in the result? If so is there any way I can re-wite to mitigate this, anything I definitely should / should not do in the SQL?
I'm well aware MS-A isn't client server but are there any other options I might explore while remaining in MS-A? I have 15+ years working with MS-A, couple of weeks looking at SS.
Looking forward to reading any ideas.