Get Access to SQL Server With Microsoft Access

Microsoft Access is a wonderful thing. It looks, smells, and tastes like a comfortable Excel spreadsheet. But once you get past the wonderful interface, you find out that as databases go, Access really isn’t up to the power demanded by most applications. So when we go to create a supporting database for a design automation or we build a tracking database for our data management system, we want the ease-of-use of Access, but the power of something like Microsoft SQL Server. 

For a change, you can have both. As you probably already know, Microsoft SQL Server is available in an Express Edition for free from Microsoft (version 2005 or 2008). But when you install this version, you really get no way to manage your databases, no way to create databases, and no way to input data. The answer from Microsoft is the SQL Server Management Studio Express Edition but Access can handle a lot of it as well. Management Studio offers more power and more accurate control in areas like data types, so it’s good to have around, but when it comes to inputting data quickly and easily, nothing beats Access.

When you open Microsoft Access and create a new database, Access 2003 provides several options including, “Project using existing data…” Access 2007 provides no such option, or at least it doesn’t offer it to you when creating a new database. If you click on Blank Database, then on the folder icon to choose a filename and location, you can change the Save As Type option to “Microsoft Access Projects (*.adp)” and click OK. Upon clicking the Create button, Access will popup and ask you if you would like to connect to an existing SQL Server Database. If you click Yes, you will be presented with the same dialog that Access 2003 provided six clicks ago, shown below (Data Link Properties dialog):

This dialog first asks you for the server name. One tricky bit here is that for SQL Express Edition or servers with multiple SQL instances, this requires the full instance name, typically <hostname>\SQLEXPRESS. After putting in the requisite information and selecting the database to connect to, Access opens its familiar user interface. At this point, you can’t tell that you’re not working on a local Access database. You can import data, add data, and manipulate and report on that data to your heart’s content. You can even add tables and create views.

One warning is that Access is not quite as fully featured or production-oriented as SQL Server. This is evident in the fact that some functionality like programmability, for example, is not available via Access. A more troublesome issue is that the data types in Access differ from those of just about any other database on the planet, including SQL Server. This can cause problems when a table is created in Access. You will want to go to SQL Studio Manager at some point and validate or update the data types that you are using.

So there’s no reason to be intimidated by Microsoft SQL Server; Microsoft Access is there to provide quick and easy ways to add, maintain and update data. You can even easily import data from a variety of sources including other databases, text files, XML, and even Excel. Microsoft also provides functionality within Excel to work directly with SQL Server data, albeit to a much lesser extent. If you’re getting into the details of this type of Access to SQL Server connection and struggling with some advanced point, contact us and talk with one of our SQL Server gurus to get some assistance.

Tags: database, Microsoft Access, Microsoft SQL Server, SQL, SQL Server Management Studio