Microsoft Access 2010 Bible

1223

CHAPTER

Upsizing Access

Databases to SQL

Server

IN THIS CHAPTER

Considering SQL Server

Express as an Access database

engine

Understanding Access ADP

databases

Working with the Access

Upsizing Wizard

T

he Access Upsizing Wizard provides a quick and easy way to upsize
Access data to a SQL Server database. Either SQL Server Express or
full server-based SQL Server can be the target of an Access upsizing
process. The SQL Server database file created during the upsizing process is
exactly the same, regardless of which edition of SQL Server is used.

The Upsizing Wizard automatically creates an Access Data Project (a special
type of Access data file that allows you to work directly with a SQL Server
database). In Chapters 16 and 37 you saw the simplest and quickest method
of upsizing Access data to SQL Server: simply linking SQL Server data to an
existing Access application (presumably, the SQL Server data was imported
into SQL Server using SQL Server Integration Services [SSIS]). Although this
option moves your data to a client-server architecture, it takes you only part
of the way. Even though the data now resides in a client-server database, the
linked tables in the existing Access front end (the forms, reports, and data-
access pages) continue to use the Microsoft Jet database engine to retrieve
information from the database.

Access Data Projects (ADPs) are frequently used to create and maintain SQL
Server databases (from here on usually referred to simply as SQL Server).
You can also use an ADP to create the user-interface objects and forms,
reports, macros, and modules, which get their data from SQL Server. The
ADP user interface looks very much like the standard Access database win-
dow you’re already accustomed to. In fact, creating the user-interface objects
is virtually the same as creating them in Access.