Access® 2010 Bible

Chapter 38. 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

The 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 ...