Access 2007: The Missing Manual

Chapter 20. Connecting Access to SQL Server

In Chapter 18, you considered how you to share your prize database with other people. For some, this is Access paradise. Teams of people can collaborate, businesses can take care of day-to-day workflow, and everyone works happily ever after. But for others, database sharing is a big-time headache, because Access just can’t keep up with everyone who wants to make changes at the same time.

In this chapter, you’ll learn another approach, which lets you break through the limits of Access and share your databases with much larger groups of people who can use it much more intensively. To do so, you’ll plug Access into SQL Server, Microsoft’s hard-core, server-side database. (Flip back to Section 3.1.2 for a refresher on the difference between client-side databases and server-side databases.)

This combination gives you the best of both worlds. You get to use a supremely reliable database engine (that’s SQL Server), without giving up the friendly user interface that makes it easy to get things done (that’s Access). Best of all, you can start using a version of SQL Server without shelling out a single cent.

Should You Switch to SQL Server?

As you learned in Section 18.1.1, there’s no absolute rule that decides who can use Access’s built-in sharing feature successfully and who can’t. You find extremes—a Fortune 500 company with thousands of workers probably can’t use Access sharing, while a five-person interior design team won’t ...