Access 2010: The Missing Manual

Adding Objects to a SQL Server Database

The nicest thing about the SQL Server support in Access is that it lets you work inside the familiar Access window, even though you’re dealing with a very different database engine. However, you pay a price for this convenience. As you’ll see in the following sections, creating database objects for SQL Server isn’t quite as intuitive as creating them for Access.

Creating a Table

You can create a table in any Access project (.adp file), whether it’s a new database you created from scratch or an existing one that you’re working with. Either way, the process is the same.

First, choose Create→Tables→Table Design. You can’t create a SQL Server table in Datasheet view. Instead, you always need to start in Design view. You can also edit an existing table in Design view in the normal way. Just right-click it, and then choose Design View.

When the Design view appears, you’ll notice that it looks a bit different from the Design view for ordinary Access tables. Fortunately, it still works essentially the same way. You add a list of fields from top to bottom, and configure the name, data type, and size of each one. (SQL Server calls them columns instead of fields, but there’s really no difference.)

Each field has five columns of information (Figure 21-15) for you to fill out:

• Column Name. This column identifies the field (just as it does in a normal Access table). To avoid headaches, don’t use spaces or special characters.

• Data Type. This ...