Access 2010: The Missing Manual

Creating a SQL Server Database

You’ve suffered through the long and grueling installation process. Now it’s time to reap the rewards of your labor and create your first SQL Server database.

Access gives you two ways to create a SQL Server database:

• You can take an ordinary Access database and upsize it. Access creates the tables you need and transfers all your information to SQL Server.

• You can use Access to create a new SQL Server database from scratch.

Generally, upsizing a database is easiest because it lets you design your tables using the tools you’re most familiar with, and then transfer your data. (As you’ll see, creating a SQL Server table in Access is a similar, but subtly different process from creating an ordinary Access table. It’s a bit like you’d feel if you got up in the morning and found someone had swapped your sock drawer with your CD collection. Everything is there—just not where you expect.)

The direct-creation approach also has its advantages. Most importantly, it gives you more control because you don’t have a conversion step. Access databases don’t line up exactly with SQL Server databases (for example, the field data types that you use are slightly different). If you create your database in SQL Server from the start, you’ll dodge any potential conversion issues.

In the following sections, you’ll learn about both approaches.

Upsizing a Database

Access has a handy Upsizing wizard that can convert any Access database into a SQL Server database. ...