In this blog post I’m going to cover a couple of taboo subjects in the SQL Server realm.
1. Using Microsoft Access.
2. Giving end users the ability to import data directly into SQL Server tables and allow them to directly manage data within these tables.

Ok calm down, sit back down and let me explain how these are useful from a SQL perspective. Picture this; one source of an ETL process is to take an external file and import it into the data warehouse. Sound familiar? Absolutely. Now this file may fluctuate a bit. The data often changes and the formatting can cause issues during the import process. Also, the data values are often updated. Wouldn’t it be handy if we could instead just give the end user the ability to directly import data into a staging table? Why not give the end user the ability to update these staged values until they are ready to be imported into the data warehouse?

Now that we understand the use case, let’s take a look at a table based solution so our end users can quickly import and manage their data. Unfortunately, Excel only offers a one-way option for working with data in tables. You can import data into Excel from SQL Server, but you cannot make updates and send it back. Let’s instead turn to Microsoft Access where we can create linked tables and import data from flat files directly into SQL Server. We also get a nice ‘spreadsheet’ view of the data within the table and can update values directly on the server. Scary huh. Let’s see how we can do this.

First, we need to create an ODBC connection to the SQL Server database. On your workstation, go to the Control Panel, Administrative Tools, and select Data Sources (ODBC).

Select the ‘User DSN’ tab, and click on the ‘Add’ button.

Walk through the wizard and give your connection a relevant name and point it to the database you wish to connect to.

Next, navigate to Access. In my examples I will be using Microsoft Access 2013. From the main screen select ‘Blank desktop database’. Select a file name, loction, and click ‘Create’.

You will now be presented with an empty looking table. In order to import data to an existing SQL Server table, we need to first create a linked table connection to SQL Server. From within Access, go to the ‘External Data’ tab and click the ODBC Database icon.

Choose the second option which is ‘Link to the data source by creating a linked table’.

Next, click on the ‘Machine Data Source’ tab and choose the ODBC connection that you previously created.

Finally, select the Table you want to connect to, click ‘Next’, and then ‘OK’.

You will now have a linked table icon on the left side.

It is worth noting here that from within Access you cannot create a new table on SQL Server, or modify fields. There is a design view. However, it will prompt that you cannot save your changes for linked tables. An admin or developer will need to create the tables before hand for the end users.

Now that we have a destination table defined, let’s import some data. click on the ‘External Data’ tab and click the Excel button.

From this screen, select the data source file location and select one of the import options. We already have a table so we will select the ‘Append’ option. While this screen looks like it has options to create a new table on SQL Server, it will not. The first option will create a new local table, and the 3rd option will create a link to the source Excel file. Click ‘Ok’, and the next screen will show a preview of the data. If all looks correct, click ‘Next’ and then ‘Finish’.

The last step is to look at the data, and update where neccessary. To do this simply double click the linked table from the left hand side. This will now show data in a table view where you can alter values. Note: Data updated here will directly update data within the sql server database.

Normally SQL Server Admin’s and Developers do all of the data preparation, ETL work, and data management. By leveraging Microsoft Access, we can give end users the ability to import data directly into SQL Server staging tables and also allow them to manage data within a spreadsheet view. With proper security in place, this can help expedite ETL processing. It can also provide a cost effective data quality solution by giving end users the ability to review and cleanse data before it is imported into a data warehouse.