If you are familiar with your database structure, and have permissions, it is possible to quickly setup a user interface to your SQL database using just Microsoft Excel.

There are 2 approaches that can be taken to achieve this in a matter of minutes:

1. Office Data Connection
   1. On the Data tab, select ‘From Other Sources’>’From SQL Server’. You will see a prompt to connect to the database, where you must enter the SQL Server hostname and login credentials. The next window will ask which database to connect to. At this point, you can click ‘Finish’ to end the process of creating your database connection.
   2. The next step is to select the data that you want to pull through into Excel. On the Data tab, select ‘Existing Connections’, and open the connection that you have just created. Click on the ‘Properties…’ button, and select the ‘Definition’ tab. Choose ‘SQL’ from the ‘Command Type:’ dropdown, and write your SQL query in the ‘Command Text:’ field. Click OK to end.
   3. Your data will be taken from the database and displayed as a table in your Excel spreadsheet.
2. Microsoft Query
   1. This solution requires a System DSN, which is created using the interface in Control Panel>Administrative Tools>Data Sources (ODBC). We recommend that your DSN is created using a read-only account.
   2. On the Data tab, select ‘From Other Sources’>’From Microsoft Query’. Select the DSN that you have setup to connect to the correct database.
   3. You will now step through a series of stages to create your query. If you prefer you can skip to the last screen, and select ‘View data or edit query in Microsoft Query’, where you will be able to use the visual interface to generate your SQL query.
   4. To send the results back to Excel, click ‘File’>’Return Data to Microsoft Office Excel’.
      To make the query even more useful, you can also pass parameters from Excel to your query. This builds a level of user interaction into the dataset, and enables report customisation within a matter of seconds.

When connecting Excel 2007 to a SQL Server database, the ‘Parameters…’ button remains disabled and greyed out, until at least one parameter has been included in your query. To include the first parameter within the query, and therefore activate the ‘Parameters…’ button, within Query Manager type your user prompt surrounded by square brackets, for example ’[Please enter the start value of the desired range]‘.

Now, from the Data tab, select ‘Connections’ and choose ‘Properties…’ for the connection that you want to make customisable. Go to the ‘Definition’ tab and now you will see that the ‘Parameters…’ button has been activated. Click this button to modify how the parameter is populated. There are 3 options here:

1. Prompt the user with a popup window
2. Use a static value
3. Use the contents of another Excel cell

Option 3 enables an Excel user to customise the report without any knowledge of databases or SQL, just the ability to update the contents of an Excel cell.

This is a really nifty method to allow users on your network to safely access your data. If you have used a read-only DSN there is no risk of a user successfully performing something malicious – DROP DATABASE database_name!