Hands on: From Access to SQL Server

Access project files
This month we’ll take a look at using Access to manipulate both the data and structure in SQL Server (the third and final option) and use an Access project file to do so. As a general rule you will need a version of Access that is more recent than the version of SQL Server; here I’ll use Access 2007 and SQL Server 2005.

Older Access versions will happily connect to younger SQL Server versions to manipulate the data but the combination of Access 2003 and SQL Server 2005 that I used in the Christmas issue won’t work now we want to create a new data structure.

Fire up Access and select New Blank Database. In the right-hand pane a default filename is shown with the .accdb extension for a normal database file. We want a project file so, with sublime disregard for intuitive use, you click on the tiny folder icon to the right (‘Browse for a location to put your database’).

In the File New Database window you can change the filename if you wish (I’m using AccessTest) and choose a project file by popping down the list of file types, selecting Microsoft Office Access Projects (*.adp) and clicking OK. Back on the Getting Started screen, click Create. When you’re asked whether you want to connect to an existing SQL Server database, answer No.

A dialogue opens asking ‘What SQL Server would you like to use for this database?’. It wants the name of the server to which you want to connect. You can use the server name or type:

(local)

if the server is on the same machine at which you are typing (but see below for more on this).
The letters SQL have been added to the filename you specified. Click Next and then Finish in the final dialogue. A progress bar is displayed while the project is created.

You’re now in Access with everything looking normal, except for the word ‘Project’ in the header at the top of the ribbon. But differences will start to appear as you work, so create a new Customer table. Enter a column name ­ CustomerID ­ and move on to setting its data type. The list of available types is much longer than the usual Access one, with many unfamiliar entries. It’s the first real indication we are using Access as a window into SQL Server.

Differences continue to appear: the CustomerID column is to be the primary key column in this table, but if you click the Primary Key button an error message appears, saying ‘Primary key cannot be created on column ‘CustomerID’ because it allows null values’. Sure enough, the Allow Nulls column in the table design view has been checked automatically. Why? Because that’s the default in SQL Server. You have to deselect the Allow Nulls setting before you can make the column a primary key column.