A question I am often asked is how to troubleshoot database problems from the desktop. A connection to a database can go wrong in several places, but I have found that by creating a test connection, the problem can usually be found fairly easily. For the purpose of this article, we will assume that the end user has all necessary passwords and permissions to the database.

In my experience, the easiest way to check the connection is through Excel. The process is the same whether you are using Excel 2003, XP, or 2000. If you are using a version earlier than 2000, then stop procrastinating and upgrade, already! You will need to know the location where the database is located, as well as the type of database (Access, SQL, Oracle, Sybase, etc.).

The first step is to open a new Excel workbook. You can go to Data - Get External Data (the menu name may vary slightly depending on the version of Excel) and choose New Database Query. This will open a window allowing you to choose a data source. It is important that you know what type of database that you are attempting to connect to, and choose the odbc driver for that database type. If you do not see a driver for the database type, then that is probably the problem. Install the database driver and try again. For this example, I’m going to use an Access database so I will choose MS Access Database and click OK. A window will now open asking to select a database. Find the location of the database and click OK. The Query Wizard will now begin allowing you to select the tables you would like to add. At this point, a successful connection to the database has been made. This rules out any problems with the odbc driver and permission to the database. Next, let’s select a table. Choose from any of the tables listed, and double click on a column name to add it to the list in your query. Click the next button to continue. The wizard will now allow the data to be filtered. For our purposes, filtering isn’t necessary, so click next to continue. The next step will allow the data to be sorted, which, again, is not necessary for our purposes, so click next. Finally, the query wizard will ask what to do with the data. Choose Return Data to Microsoft Excel and click Finish. A window will open asking where to put the data. Click on cell A1 (or any other cell you would like to insert the data into) and click OK. If the database connection was successful, the data will populate the Excel sheet.

Although there are many ways to test database connections, I found this to be one of the more effective ways. Most business users have Excel installed, which makes this easily accessible. If you are having problems connecting to a database, you will most likely find the problem along the way when testing the connection in Excel.