Part VI: Access as an Enterprise Platform
1200
Note
SQL Server Express is a free download from Microsoft. (www.microsoft.com/express/sql). You can use
it as a development and deployment database server for your Access applications if you don’t have access to a
SQL Server Enterprise installation.
Before beginning this chapter, keep in mind that the architecture discussed in the following sec-
tions uses SQL Server to store data and Access to build the user interface, the application’s reports,
and the logic controlling the application’s behavior and features.This chapter examines a number of different ways to use SQL Server data in Microsoft Access
applications. Although there are no SQL Server object designers (tables, stored procedures, views,
and so on) in the .accdb file format, you can use Open Database Connectivity (ODBC) to link to
SQL Server data objects. However, if you choose to use the Access 2000 .adp data file format you
have access to a full range of tools specifically designed to work with SQL Server data. You can edit
existing or create new SQL Server tables, stored procedures, and views when using the .adp file
format.Microsoft is vitally interested in developers learning and using SQL Server. But, the truth is that acquir-
ing and installing the “full” versions of SQL Server can be a daunting process. As a server application,
SQL Server is relatively expensive to license, and its hardware requirements are rather extensive. Not to
worry! Microsoft has a wonderful gift available to you, free for the downloading.SQL Server Express is a somewhat stripped-down version of SQL Server intended to be used as a data-
base engine for smallish workgroup applications, and as a test platform for developers working on SQL
Server front-end applications. You may freely download and install SQL Server Express, to use on your
computer, and even bundle it with applications you distribute to users.SQL Server Express works and behaves exactly like SQL Server Enterprise, its much bigger brother. SQL
Server Express supports all the data types, stored procedures, triggers, and other database objects used
in SQL Server Enterprise. In fact, migrating a SQL Server Express database to SQL Server Enterprise
involves nothing more than disconnecting from SQL Server Express and connecting the database files
to SQL Server Enterprise.The primary differences between the standard editions of SQL Server and SQL Server Express is that
SQL Server Express databases are limited to 4GB in size (twice that of Access!), and SQL Server Express
doesn’t support some of the more advanced features of SQL Server Standard and Enterprise editions.
Otherwise, the database engines in all editions of SQL Server are identical.You really owe it to yourself and your users to take a look at SQL Server Express. At the time of publica-
tion, the official home of SQL Server Express is http://www.microsoft.com/express/sql, or do a Web
search for “SQL Server Express download.”Downloading SQL Server Express