In this clients system the data had already been migrated to SQL Server and they have an ad-hoc reporting system that uses an Access query which they want to use to dynamically feed data to Excel. At the moment the data returned to Access is pasted into Excel. One problem is that the Access query makes use of an NZ function, a lesser problem is that the query refers to values in an Access form; both of these issues means that Excel is unable to link to the Access query.

Once we have converted the query to a SQL Server view, everything will be working server side and we will no longer have the above problems, another benefit will be improved performance. The converted result is shown below.

The conversion appears to be complicated because the existing Access query uses an Access form into which a user enters date parameters, this would normally make converting the SQL a more challenging task, but we have a simple systematic solution to this.

The case study shows how we can tackle converting problematic Access queries which for reasons of performance or for other reasons of compatibility need to be converted into SQL Server views. The study also acts as a walk-through for MUST customers facing similar problems, and shows how after upsizing MUST+SQL professional can continue to be used as a handy tool for increasing productivity on an on-going basis.

The existing access report form is shown below.

This activity lists the orders within the date range, which are then pasted into Excel.

The query links to the form picking up the date selection criteria.

A second query utilises the NZ function.

Top of Page

We start using MUST+SQL to make a connection to the SQL Server.

Next we load the existing SQL Server data structures into MUST+SQL, this is very important and is utilised by the query translators to validate the SQL.

The result shown below illustrates the data dictionary which now holds information on all the tables and fields in the SQL Server.

Next we select the ad-hoc Access reporting database.

The queries in the Access database are then analysed (without selecting to Analyse forms and reports option as this is not needed).

As a result of the analysis we now have a list of all the Access queries in the reporting system.

MUST+SQL also evaluates the query hierarchy of how all the queries in Access relate to each other, this will be important as if we select to convert a query, then MUST+SQL can also work out automatically which other related queries also need to be converted.

In the example below selecting one query results in a second dependent query also being selected.

We now have all the required queries selected and can convert them to SQL Server views.

MUST+SQL has also worked out all the screen parameters which are used in Access for these queries, as we shall see a technique which will enable the Excel user to enter choices when pulling back data from SQL Server.

From the parameters list, we can also display a list of all queries which use a particular parameter.

Examining the SQL we can see how the Access parameters have now been substituted when the SQL was converted. MUST+SQL creates a table to hold the users screen choices. A user specific view is then automatically CROSS JOINED onto the existing SQL and all the old screen references are replaced automatically by references to the new parameters.

Next we will enter some test parameters to see that the new views do not give and errors.

Testing the query will also create a record in the parameter table for the current user.

Top of Page

In Excel we now link the user view that MUST+SQL has created onto our spreadsheet. This view is always called vw_tblParameters.

Inside Excel we can now see our test parameters reflected back through the view.

Note this is user specific so we have a true multi-user reporting system.

We can now link the new results view into our spreadsheet as either a table or pivot table of the data.

This now returns the data to Excel.

As these views are read-only we need to add a couple of cells into which the dates will be entered by the user, and a command button to refresh the data.

Set the refresh option and note the object name which will need to be set in the button code to refresh the data.

Code behind the button is shown below. Once you have this working the first tabulation of the data in vw_tblParameters can be deleted from the spreadsheet as this was only used to get us started on the task.

Private Sub cmdRefreshData_Click() ' Reads the dates from the excell spreadsheet ' Writes the dates to the parameter view then refresh the results Dim cn As New ADODB.Connection Dim strConn As String Dim rsParameters As New ADODB.Recordset ' open connection to the database strConn = "Provider=sqloledb.1; Data Source=VISTAULTRA64\VISTAULTRA64_08;" & _ "Initial Catalog=Northwind; Integrated Security =SSPI" cn.Open strConn rsParameters.ActiveConnection = cn rsParameters.Open "vw_tblParameters", , adOpenDynamic, adLockOptimistic If rsParameters.EOF Then ' First time user so create a new parameter record in the ' parameter view rsParameters.AddNew End If ' Now save the parameter values rsParameters("_param_txtStartDate") = Cells(6, 4) rsParameters("_param_txtEndDate") = Cells(7, 4) rsParameters.Update rsParameters.Close cn.Close Set rsParameters = Nothing Set cn = Nothing ' for older versions of excel 'Me.QueryTables("Query from Northwind1").Refresh ' for 2007 Me.ListObjects("Table_Query_from_Northwind_1").Refresh End Sub

Note that in Excel 2003 and earlier versions we would use the collection Me.QueryTables, but in Excel 2007 we use Me.ListObjects.

Top of Page

If at some later date you want to repeat this process for another new set of queries, when loading the data dictionary MUST+SQL will detect the existing parameter table and load this for you, so your new changes integrate into the existing report structure.

Top of Page

Performing this conversion using MUST+SQL and designing the spreadsheet took 2 hrs to complete. If we had to re-write the SQL without MUST+SQL and do a manual translation this would easily have taken another 3 or 4 hours to complete the work, so the productivity gain in using MST+SQL is tremendous, and if 10 or 20 queries had to be converted then the labour saving would be at least 3 or 4 days work.

The client was very pleased with the solution in now having no longer to cut and paste the data into Excel, the increased speed of execution using a view was an additional bonus.

Top of Page