A client/server database such as Microsoft SQL Server processes queries on the server machine and returns results to the users' computer (workstation).
The server software itself can't display data to the user and this is a major reason for connecting client server data to Microsoft Access. Access serves
as a front-end user interface for SQL Server by displaying data retrieved from the database server in forms, data sheets, and reports.
For example: If the user updates the data in an Access form, the user's workstation sends the update to the back-end database in SQL Server.
You can accomplish this by process either by linking to these external databases so they appear to both you and the user in Access tables or by using techniques
that access client/server data directly.
When you reduce the volume of network traffic by moving the processing of queries to the back end (the SQL Server) Access becomes a much more powerful
development solution. MIcrosoft Access can now handle huge volumes of data and a large number of concurrent users. The main issues usually faced by
developers who want to deploy such a wide-scale MS Access application are the following:
The variety of operating systems used by each user
Difficulties with deployment
The method by which each user is connected to the application and data
The type of Hardware each user has
Advantage Of Server Processing
Processing of queries in an SQL Server application is done at the server, which significantly reduces network traffic. The application itself, however, must
continue to reside in the memory of each user's PC. This means that each client machine must be capable of running the appropriate operating system and the correct
version of Microsoft Access. Even when the correct operating system and version of Access are in place, problems can still exist.
SQL Server Performance
SQL Server consistently leads in performance benchmarks, such as TPC-E and TPC-H, and in real-world application performance. With SQL Server 2016, performance
is enhanced with a number of new technologies, including in-memory enhancements, Query Store, and temporal support. SQL Server continues to lead the way - Gartner
rated SQL Server as having the most complete vision of any operational database management system.
SQL Server 2016 Enchancements
SQL Server 2016 introduces many new features and enhancements, including:
Performance improvements via In-Memory OLTP (online transaction processing), Real-Time
Analytics, Query Store, and more
Security improvements via Always Encrypted and Row-Level Security
Availability improvements via failover clustering, availability groups, and AlwaysOn improvements
Scalability, security, and availability improvements through support for Windows Server andServer Core
Dynamic link Library conflicts often result in difficult to find errors and idiosyncrasies in an Access application. Furthermore Access is not the best solution
for disconnected users who must access the in an application and its data over the Internet.
Before you decide to deploy a wide-scale Access application, you need to know the hardware and software configuration of all your systems users. You must
also decide whether the desktop support required for the typical Access application is feasible, given the number of people who will use the system that
you are creating.
Microsoft Smoothing The Process
The issues related to hardware and network capabilities, although still present, are not the obstacles they presented when Access was first developed. Powerful
and inexpensive memory and other computer components now make Access to SQL Server connectivity much easier. Additionally, Microsoft has continuously refined
both MS Access and SQL Server to create an easier path to connectivity.
Got Questions Or Need Help Now?
If you need answers to questions or your database is not working for you CALL US AT: (323) 285-0939 or use our Contact Us email form.
*Attribution: Text reprinted by permisssion of author from Alison Balter's
Mastering Microsoft Office Access 2007 Development.