There is a lot of discussion by the network group where I work stating that when you have a split database Access creates a lot of network traffic in terms of the amount of data sent across the network as opposed to other database products, there is a project to move the backend tables to SQL Server which will improve performance on the user side, but does it change the amount of data sent across the network? I understand that you can use functions with SQL Server to improve the amount of data sent with existing Access front ends, but is the amount of data sent significant enough to justify moving all of the front ends to .NET? The databases are small in terms of user numbers and in terms of data, no huge amounts of anyting. My personal experience is that Access front ends coupled with SQL Server back ends is a nice solution for smaller databases, but I don’t know how Access sends data to client front ends from .mdb backends or SQL or how other products might work to justify my position with the network people. I have not considered using .adp front ends I have not had any experience with them and Microsoft is now recommending not to use them. Any thoughts or experience would be appreciated.
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
data sent from backend file (Access 2002)
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » data sent from backend file (Access 2002)
- This topic has 4 replies, 4 voices, and was last updated 16 years, 6 months ago.
Viewing 1 reply threadAuthorReplies-
WSpatt
AskWoody LoungerNovember 19, 2008 at 3:16 pm #1133679From my limited understanding when you have a Access backend there is a high amount of network traffic as all processing is done in the front end.
With SQL server as the backend the amount of data going across the network is minimal since the back end is doing the processing before it sends just the data it needs to back to the front end.
Again microsoft seems to recommend an Acces front end for SQL server backends. I have heard it takes a lot longer to develop .net front end than it does in Access.
As I said, this is mainly what i have heard.
-
WScsimms
AskWoody LoungerNovember 19, 2008 at 4:03 pm #1133680 -
WSHansV
AskWoody LoungerNovember 19, 2008 at 6:08 pm #1133691Simply migrating the backend to SQL Server will not necessarily diminish network traffic, since the Access frontend will still be doing most of the data processing. To let SQL Server do the hard work, you need to convert your queries to pass-through queries, or define views in the SQL Server database.
For example, let’s say that you have a table with 100,000 records, and you have a query that uses selection criteria to return 100 of those records (to be displayed on a form or report).
With a normal query, Access will sequentially transfer all 100,000 records across the network into the memory of the PC running the query, and discard the 99,900 records that don’t meet the criteria.
With a pass-through query, SQL Server will apply the criteria, and send only the 100 records that meet the criteria across the network to the PC running the frontend – an enormous reduction of networtk traffic.
See:
How to create an SQL pass-through query in Access
Microsoft Access Pass-Through Queries to SQL Server
ACC: How to Simulate Parameters in an SQL Pass-Through Query
-
-
-
WSMarkLiquorman
AskWoody LoungerNovember 20, 2008 at 1:04 am #1133755I’ve had alot of experience working with Access using an SQL backend, and the answer is “it depends”.
As has been stated, under normal circumstances Access does all its processing in the frontend. However, this does not mean that if you have a 100,000 record table that every time you query this table all 100,000 records come across the network to the frontend. Access can use the indexes to reduce the # of records based on the selection criteria. This happens regardless of the backend.
However, using pass-through queries with an SQL Server backend will DRAMATICALLY improve performance. In these situations, all the processing is done by the SQL Server (which is much faster) and only the results come back across the network (reducing network traffic).
However, you can’t use pass-through queries for everything; one primary reason being that they are not updatable. They are great for filling listboxes and in reports Therefore, you have to adopt preventive measures to minimize the need for Access to work with large recordsets that you need to update. For example, let’s say you have the usual Customer form. Many such forms are bound to a table so Access always has to pull-down all the records in the table. Instead, you can create a recordset with only a single record! (See Create Powerful Bound Forms ) Another thing to do is only populate listboxes and subforms when they are needed. For example, you may have that Customer form with several tab pages, one of which contains a Transaction History subform. Don’t load that subform immediately, only do it when the tab page is displayed. And don’t use the normal Master/Child properties. This will requery the subform every time another customer is displayed (even if the tab page isn’t being displayed). Put the selection criteria in the subform’s recordset query, then manually requery the subform whenever the tabpage is selected.
Viewing 1 reply thread -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Windows 11 Insider Preview build 26200.5651 released to DEV
by
joep517
3 hours, 55 minutes ago -
Windows 11 Insider Preview build 26120.4441 (24H2) released to BETA
by
joep517
3 hours, 56 minutes ago -
iOS 26,, MacOS 26 : Create your own AI chatbot
by
Alex5723
7 hours, 51 minutes ago -
New PC transfer program recommendations?
by
DaveBoston
2 hours, 12 minutes ago -
Windows 11 Insider Preview Build 22631.5545 (23H2) released to Release Preview
by
joep517
11 hours, 56 minutes ago -
Windows 10 Build 19045.6029 (22H2) to Release Preview Channel
by
joep517
11 hours, 57 minutes ago -
Best tools for upgrading a Windows 10 to an 11
by
Susan Bradley
16 minutes ago -
The end of Windows 10 is approaching, consider Linux and LibreOffice
by
Alex5723
6 hours, 13 minutes ago -
Extended Windows Built-in Disk Cleanup Utility
by
bbearren
10 hours, 6 minutes ago -
Win 11 24H2 June 2025 Update breaks WIFI
by
dportenlanger
1 day, 6 hours ago -
Update from WinPro 10 v. 1511 on T460p?
by
CatoRenasci
4 hours, 50 minutes ago -
System Restore and Updates Paused
by
veteran
1 day, 9 hours ago -
Windows 10/11 clock app
by
Kathy Stevens
20 hours, 32 minutes ago -
Turn off right-click draw
by
Charles Billow
1 day, 12 hours ago -
Introducing ChromeOS M137 to The Stable Channel
by
Alex5723
1 day, 16 hours ago -
Brian Wilson (The Beach Boys) R.I.P
by
Alex5723
10 hours, 5 minutes ago -
Master patch listing for June 10, 2025
by
Susan Bradley
1 day, 17 hours ago -
Suggestions for New All in One Printer and a Photo Printer Windows 10
by
Win7and10
20 hours, 41 minutes ago -
Purchasing New Printer. Uninstall old Printer Software First?
by
Win7and10
1 day, 23 hours ago -
KB5060842 Issue (Minor)
by
AC641
11 hours, 55 minutes ago -
EchoLeak : Zero Click M365 Copilot leak sensitive information
by
Alex5723
2 days, 6 hours ago -
24H2 may not be offered June updates
by
Susan Bradley
23 hours, 32 minutes ago -
Acronis : Tracking Chaos RAT’s evolution (Windows, Linux)
by
Alex5723
2 days, 19 hours ago -
June 2025 updates are out
by
Susan Bradley
55 minutes ago -
Mozilla shutting Deep Fake Detector
by
Alex5723
3 days, 10 hours ago -
Windows-Maintenance-Tool (.bat)
by
Alex5723
2 days, 19 hours ago -
Windows 11 Insider Preview build 26200.5641 released to DEV
by
joep517
3 days, 12 hours ago -
Windows 11 Insider Preview build 26120.4250 (24H2) released to BETA
by
joep517
3 days, 12 hours ago -
Install Office 365 Outlook classic on new Win11 machine
by
WSrcull999
3 days, 12 hours ago -
win 10 to win 11 with cpu/mb replacement
by
aquatarkus
3 days, 4 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.