Microsoft Access + SQL Server Development

What Is Microsoft Access?

MS Access is a very powerful and flexible program that is used to create databases. It is highly unique as a software tool because it contains features providing for development of data storage as well as the user interface for finding and manipulating data. To find out more, go to the Introduction to Microsoft Access page. Because Access is so versatile, it can be used to create data applications for any sized business. Access is frequently used as the front end or user interface for large enterprise database programs, like Microsoft SQL Server. Before developing your database solution using MS Access, we will analyze your individual needs, then make recommendations.

Got Questions Or Need Help Now?

If you need answers to questions or your database is crashing:
CALL US AT (323) 285-0939 or use our Contact Us email form.

MS Access For Small Business Applications*

Microsoft Access is an excellent platform to for developing an application that can run a small business. Access Wizards let developers quickly and easily build the application’s foundation. The ability to build code modules enables developers to create code libraries of reusable functions. Access also has features to add code behind forms and reports and enables them to create powerful custom forms and reports. The main limitation of using Access for developing a custom small business application is the time and money involved in the development process.

Many people use Access Wizards to begin the development process but find they need to customize their applications in ways they can't accomplish on their own. Small business owners often experience that on an even greater scale.The demands of a small business application are usually much higher than those of a personal application. Many doctors, attorneys, and other professionals call us after they reached a dead end in the development process. They are always dismayed how much money it will cost to make their application usable.

Access Development For Departmental Applications

Access is perfect for developing applications for departments in large corporations. It is relatively easy to upgrade department users to the appropriate hardware. For example it's much easier to buy additional RAM for fifteen users then it is for 4000. Also, Access’s performance is adequate for most departmental applications without the need for client/server technology.

Most companies have a PC Guru who is more than happy to help design forms and report this gives the department a sense of ownership because they have contributed to the development of their Apple application it also makes our life as developers much easier we can focus on Hardcore development issues leaving some of the form and Report design tasks to your local Department.

Access Development For Corporate Applications

Although Access is excellent for building departmental applications you can also use it to produce applications that you distribute throughout the organization. A successful deployment of an Access application in this situation depends on your corporation. There is a limit to the number of users who can concurrently share an Access application while maintaining acceptable performance, and there is also a limit to the number of records that each table can contain without a significant performance drop.

These numbers vary depending on factors such as the following:

• How much traffic already exists on the network?
• How much RAM and how many processors does the server have?
• How is the server already being used?
• What types of tasks are the users of the application performing?
• Where are Access and your Access application run from, the server or workstation?
• What network operating system is in place?

For example: Are applications such as Microsoft Office being loaded from the server or from local workstations? Are users clearing, entering data, running reports, and other tasks?

Our general rule of thumb for an Access application that's not client/server based is that poor performance results with more than 10 to 15 concurrent users and more than 100,000 records. Remember these numbers very immensely depending on the factors mentioned, as well as the definition of acceptable performance by you and your users.

At MS Access Solutions - We primarily develop Access front end client/server database systems using SQL Server or Azure SQL for data stores.

The developers often misunderstand what Access is and what it isn't when it comes to being a client server database platform people ask us isn't Access a client server database? The answer is that Access is and unusual application because it's a file server program right out of the box but it can act as a front end to a client server database. If you buy Access and develop an application that stores the data on a file server database, the workstation performs all data processing.

This means that every time the user runs a query or report, the file server returns all the data to the workstation. The workstation machine then runs the query and displays the results in a datasheet or a report. This process generates a significant amount of network traffic, particularly if multiple users are running reports inquiries at the same time on large Access tables. In fact, such operations can bring the entire network to a crawl.

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.

Microsoft Access + SQL Server

Access As User Interface and SQL Server As Database

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.

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.