Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications

Chapter 18. Working with SQL Server Data

IN THIS CHAPTER

• SQL Server 2005 versus SQL Server 2005 Express

• Configuring SQL Server 2005

• Preparing an Access database for upsizing

• Using the Upsizing Wizard to upsize an Access database to SQL Server 2005

• Linking an Access front end to data in a SQL Server database

You can store data directly in an Access database, but Access also has the capability of storing and retrieving data using other database engines, including Microsoft SQL Server. Storing data directly in an Access database works fine for most database applications that would be used by individuals or small businesses. However, an organization that needs to store gigabytes of data (say, data on the entire population of the U.S. for marketing purposes or scientific data from thousands of studies) needs to use a SQL Server back end, rather than storing data in an Access database. Additionally, any organization concerned about keeping its data secure can benefit from the extra security features that SQL Server provides. But storing your data in SQL Server doesn't mean that you have to abandon the familiar Access interface: even when you use SQL Server for data storage, you can still use Access to develop the application's interface.

Note

The sample databases for this chapter are:

• AdventureWorks SQL.accdb (Access front end to SQL Server tables)

• Basic Northwind v 1 (linked SQL Server tables).accdb (Access front end to SQL Server tables)

• Basic Northwind.accdb (Access database for upsizing)

• Basic ...