Microsoft Office Tutorials and References
In Depth Information
Chapter 2: Using Access As a Front End to SQL Server
Chapter 2: Using Access As a
Front End to SQL Server
In This Chapter
✓ Understanding SQL Server
✓ Using ODBC to connect to SQL Server
✓ Living without Access Data Projects
Access is more than just a relational database; it’s also a robust
development environment where you can create queries to view and manipulate
subsets of data, build forms to view your data in an easy-to-read format, and
generate reports to print your data on paper. You can even create macros
and Visual Basic for Applications (VBA) code to perform common tasks and
routines automatically. The data itself, however, resides in the Access tables.
When you’re building your beautiful applications, you aren’t limited to
working exclusively in Access. You can use Access as a front end to several
data sources. In Book II, Chapter 4, we show you how to import and link to
data from several data sources, such as Excel and Outlook. In this chapter,
we demonstrate how to connect to and use data from other relational
databases, such as Microsoft SQL Server.
What Is SQL Server?
SQL Server is a relational database management system (RDBMS) produced
by Microsoft, just like Access. SQL Server was built to serve primarily as a
database engine, and it has much higher hardware requirements to achieve
faster performance than a database designed for the desktop, such as Access.
You must purchase SQL Server from Microsoft — usually, at a high cost —
to achieve maximum benefits. For large companies with lots and lots of
data, the purchase is a no-brainer. For individuals and smaller companies,
the cost involved will probably break the bank.
SQL Server is a client/server database, which means that at least two
computers are involved — sometimes three, four, or a hundred. The network
computer on which SQL Server is installed and running is (you guessed
it) the server; the computers running Access (or another program that’s
retrieving data) are the clients. The client/server architecture allows the
client computers to focus on displaying the data to the end user while the
server computer retrieves, inserts, updates, and deletes data. Client/server