Hands on: Connect Access and SQL Server

Following feedback and comments from readers, this month we’re going to look at the connections you can make between Access and SQL Server. Esmond Hamilton, among others, asked for more on this topic. Microsoft has made this reasonably straightforward, although as with many new tasks, it can initially appear taxing. But once you’ve done it a time or two, it’ll seem a doddle.

My premise for the description that follows is that you have Access and SQL Server installed, as well as reasonable privileges. What might ‘reasonable’ mean? If I was working with both packages installed on the same machine ­ say, on a PC or laptop that is isolated from the outside world ­ then I would award myself administrator privileges for SQL Server. (The login with admin rights used by SQL Server is called sa, which is short for System Administrator).

If you’re embedded in the middle of a business and/or there are security issues, then the story will be very different and you’ll need to talk to your SQL Server administrator. Security is complex and thousands of words could be written about it, but this is a database column, so I’ll only offer these sweeping generalisations.

The ‘one isolated machine’ setup described above, with both the client and server software on one box, provides an ideal playground for experimentation, and you can be confident you’re not about to send anyone’s hard work down the tubes.

Why connect?
Why would you want to work with both Access and SQL Server? Broadly speaking, there are three reasons you’d wish to do it:

1. You want to use Access to connect to an existing SQL Server database and to build forms based on the tables in that database, to enter data and to query it. This allows you to work with Access, which you know and love, but the data is held in SQL Server. Put slightly more technically, you’re using Access to manipulate the data that is held in SQL Server.

2. You want to use Access to create a new database in SQL Server. You’ll work with Access to construct tables and for the other tasks outlined above. Technically, you’re using Access to manipulate both the data structure and the data in SQL Server

3. You wish to upsize an existing Access database to an SQL Server database. This is something I’ve written about quite a bit. Typically you’d use an upsizing tool: there are several available, including SSMA (SQL Server Migration Assistant), which is downloadable free from Microsoft, and Must from ASC Associates.