Hands on: Connect Access and SQL Server

I’ve looked at both and for upsizing a database with any degree of complexity, I’d choose Must, one of its most compelling features being its intelligent handling of view/queries. However, it’s probably worth having a look at SSMA first to see if it does everything you want.

Practical stuff
I’m strangely comforted by the thought that, no matter where I decide to start discussions of the practicalities, it will be wrong. There are too many variables: you may be using Access 2003 or 2007, and SQL Server 2000, 2005 or 2008 in its umpteen different flavours.

But I’ll think positive and over the next few months I’ll try to cover what seem to me are the main ones. This month, I’ll start with Access 2003 and SQL Server 2005, and the requirement to connect to an existing SQL Server database (option 1 above).

Open Access, select File, New, Blank database… and give it a name. (Note, this should be a standard Access .mdb file, not a project file). We want to link to tables in SQL Server, so click File, Get External Data, Link Tables. At the bottom of the dialogue window under ‘Files of type’, select ‘ODBC databases ()’; a further dialogue opens labelled ‘Select Data Source’. On the File Data Source tab, click New.

You have now been diverted into a series of dialogues that allow you to set up a new data source. Once this has been done, the next time you want to link tables it will be faster because you usually re-use the data source created here. In the ‘Create New Data Source’ dialogue window, scroll down through the list of drivers until you can choose SQL Server. Click Next, give your source a name and click Next again. A summary of your choices is shown. Click Finish.

The diversion continues with a ‘Create a New Data Source to SQL Server’ dialogue: start by entering a brief description of the data source. For the Server, if you’re using SQL server on the same machine as client and server, type:
(local)
Yes, that has to be open bracket, lower case local, close bracket: nothing else will do. If it isn’t local, then type in the name of the server.

Click Next. You’ll now be asked how SQL Server is to verify the authenticity of the login ID: you can use one that has been set up for you by the SQL Server administrator or choose Windows NT authentication. (As noted previously, your local security conditions will determine what you do here). Click Next.