Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Aug 2, 2001

Beginning SQL Programming: Pt. 3 - Page 2

By DatabaseJournal.com Staff

Configurations for Using SQL

Before we move on we want to demonstrate some typical installations that use SQL. These drawings demonstrate a range of situations which you might encounter (or design), from a simple desktop to a complex multi-server environment.

How to Study These Configurations

Note that each section has a SQL requester, a back end and a connection. In many cases the requester is a front end (user interface) but not always. Consider the case where there are several servers between the user and the DBMS. In those scenarios, an intermediate server is the SQL requester.

Note the difference between a front end and a SQL requester. The front end deals with humans. The SQL requester is software that generates a SQL statement. Generally the requester uses information from the front end, such as which records to read or what data to write.

In most cases the back end is a DBMS, but not always. Datastores like Microsoft Exchange can also speak the SQL Language.

For most of these scenarios there are certain tasks that must be performed:

  • user interface
  • business logic (which may produce the SQL statement)
  • data storage
  • physical connections and
  • logical connections

However, in each scenario these functions are divided differently across machines and locations. Having a clear image in your mind of where various tasks are performed is particularly useful in troubleshooting. It is useful to be able to follow mentally the path of information from the user all the way through to the data store and back out again. Frequently the first step in solving problems is to isolate which step in the process is creating the problem.


Access, as we have discussed, has objects that function as parts of a front end (forms and reports) and table objects that function as a back end. In a sense Access query objects function like SQL statements, as they are one way to communicate between the front and back end objects. In addition to the query objects, any Access object property that accepts a SQL statement for a value is a SQL Requester, as is Access VBA code that executes a SQL statement. Unlike the other scenarios, Access is able to perform a connection internally between a form and a table without any external coding such as ADO or OLEDB.

Desktop DB Architecture

Two Tier Architecture on a LAN

A very simple system for smaller offices involves just user's desktop PCs, a server and a Local Area Network. The server performs the services of managing network traffic, security and holding the data. For example an office of 20 people may have an Intel-based server running Windows 2000 Server and SQL Server 2000. The front end could be an application created in Visual Basic, which runs on the user's desktop. In response to the user entering some parameters and clicking on a button, the VB code creates a request containing a SQL statement and sends it to the server via a connection such as ADO. The server receives the request and routes it to the DBMS. The DBMS executes the statement and then returns the data to the VB application for display.

Two-Tier architecture diagram

SQL etc Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM