Access.2007.VBA.Bibl..

Y

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

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 Northwind CS.adp (Access project front end for SQL Server
  tables)

In Windows Vista and Office 2007, it isn’t easy to connect an Access front-
end database to a SQL Server back end. In Office 2003 running on Windows
XP, using the Upsizing Wizard to convert an Access database to a SQL Server
back end was easy — even trivial. This task is now a major chore because
you need to work through a long list of SQL Server settings and then

NOTENOTE

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

Working with

SQL Server Data