Part VI: Access as an Enterprise Platform
1224
On the CD-ROM
This chapter uses Chapter38.accdb database on this book’s CD. If you haven’t already copied it onto your
machine from the CD, you’ll need to do so now. You’ll also need access to some version of SQL Server
(Standard, Enterprise, or Express) if you intend to practice upsizing Access databases to SQL Server.
Before beginning this chapter in earnest, you need to keep in mind a few things about transferring
data to SQL Server. When upsizing an Access database to SQL Server, SQL Server takes over data-
management tasks. All the tables, queries, and other data-oriented objects are contained within the
SQL Server database. An Access .adp file is not much more than an interface to the SQL Server
database, and contains the forms, reports, VBA code, and other user-interface components.The good news is that if you’re moving from an existing Access front end to SQL Server, you don’t
have to build these objects from scratch. The Access Upsizing Wizard does most of the work for
you, preserving the work you’ve already invested in the user interface of your Access application.Using linked SQL Server tables in an Access front end can be an acceptable solution for many
small-workgroup environments. However, for environments with large numbers of users or where
large volumes of data are processed, you need a solution that utilizes client-server architecture in
both the front-end and back-end databases.Cross-Reference
Although this chapter focuses on upsizing an Access database to SQL Server, a very common technique for
using SQL Server data in an Access application is to use ODBC to link to SQL Server tables. Chapter 37 dis-
cusses this process, and shows how to access SQL Server data from a standard Access .accdb file. The .adp
file created by the Upsizing Wizard is actually an Access 2000 format data file, but it features all the user-
interface enhancements seen in the .accdb data file type.
Introducing SQL Server Express
This chapter makes many references to SQL Server Express Edition. Client-server databases, and
their differences from a file-based database system like Microsoft Access, are discussed in Chapters
36 and 37. But those chapters really didn’t describe SQL Server in detail or explain why SQL
Server is so important to Access developers.SQL Server is Microsoft’s premier database engine for enterprise, Web, and large database
systems. SQL Server is just an engine, with a minimal interface necessary to create and maintain
databases and database objects. There is no provision for building user interfaces or reporting fea-
tures in SQL Server.Because SQL Server is scalable — from tiny individual desktop list managers to multi-terabyte
databases serving thousands of simultaneous users — it’s the database engine of choice for many
small and large companies. Large SQL Server installations can be clustered, allowing multiple serv-
ers to work together as a single, huge computer system. There is no practical limit on the number
of tables, stored procedures, and other objects in a SQL Server database, and each table can have a
virtually unlimited number of records.