Fix what Access export wizards miss

from the Artful SQL Server & Access Tips List


Access applications may be fine for a system with a few users and a few thousand rows. Often there comes a time, though, when the user count reaches the thousands, or the row counts are in the millions or billions. Now you need a real database. The leap to SQL Server may be a boon. Above all, an Access Data Project (ADP) front end tied to a SQL Server backend will be dramatically faster than the equivalent front end/back end, even if you leave the front end largely unchanged.

The Microsoft Access-to-SQL-Server upsizing wizard is a great help, but a simple upsize won't do everything. Your front end probably contains logic that makes no sense in SQL. A ubiquitous example is a form containing two related combo boxes or list boxes. Selection in the first box limits selections in the second (for example, cboCountries and cboRegions and cboCities).

Such situations abound in database applications: Branches, Departments, and Employees; Suppliers and Products; Publishers, Titles, and Authors. The standard Access MDB way of handling this is to rewrite the row source string of each successive list. It could be as simple as:

With Me
  .cboSecondList.RowSource = "SELECT * FROM SomeTables WHERE PK = " & .cboFirstList
End With

It could get fancier and involve several observations, but that's the basic notion.

But in the SQL world, this is counterproductive. SQL Server cannot compile this code. Every time, it results in a string that must be parsed, compiled, and executed. Ideally, you want to move this kind of logic out of the applications and into the database.

I’ll cover two or three approaches to this problem, illustrating the techniques you might use to solve it. We’ll use stored procedures, user functions, and pure SELECT,/code> statements. This illustrates the situation:

Fig A

Selecting a publisher populates the list of titles available.

Preliminaries

First, we need a new ADP file whose connection points to the pubs sample database installed with SQL Server 2000. Use the wizard to create a data project that connects to an existing database. Access opens the Data Link Properties dialog box, where you can specify the server, the particular database, and the security model (integrated Windows security or separate SQL security). Click the Test Connection button to verify that your connection settings are valid.

The forms we’ll be looking at assume the existence of this stored procedure ...

CREATE PROCEDURE dbo.ap_PublisherSelect
AS SELECT pub_id, pub_name, city, state, country
FROM dbo.publishers
ORDER BY pub_name

and this user function ...

CREATE FUNCTION dbo.fnTitlesByPublisher( @pub_id Integer = 0 )
RETURNS TABLE
AS
  RETURN ( SELECT TOP 100 PERCENT title_id, title, type, pub_id
  FROM dbo.titles
  WHERE ((@pub_id Is Null) Or ([pub_id] = @pub_id))
  ORDER BY title )

The sproc populates a combo box containing a list of publishers. The user function returns a table, so it may be substituted anywhere in a SQL statement for a table reference.

We’ll use this function to populate the list box with titles from the selected publisher. Before proceeding, notice the syntax of the WHERE clause. This enables us to call the function in two distinct ways: We can pass a valid parameter such as 1389, or we can pass a NULL. In the latter case, our function will return all rows in the Titles table. (Why write two functions when one will do?)

To create this form and see it work, follow these steps:

1. Click the Forms tab of the database window, and then double-click the Create Form in Design View option. A new form that has not yet been bound to a table or other data source will open.

2. Click the Combo Box icon on the toolbar, and then click somewhere on your new form to drop a combo box control. Name it cboPublisherSelect. Double-click the control to open its property sheet. Select the Data tab, and in its Row Source property, type dbo.ap_PublisherSelect, the stored procedure created above. Give it two columns and make the width of the first column zero. This hides the first column, which will contain the Primary Key (which is most often meaningless to a user).

3. On the form, place a list-box control called cboBookSelectMulti, with the row source blank. Set its MultiSelect property to Extended. Give it three columns and make the width of the first column zero. Finally, set its Visible property to False.

4. Place three buttons on the form footer as illustrated.

The concept is this: In the AfterUpdate event of cboPublisherSelect, we execute code to populate the list of titles and then make it visible. Similarly, if cboPublisherSelect is null, we hide the list of titles with this subroutine:

Private Sub cboPublisherSelect_AfterUpdate()
    Dim lngPub As Long
    Dim strSQL As String
    Dim fVisible As Boolean
    With Me
      fVisible = Not IsNull(.cboPublisherSelect)
      .cboBookSelectMulti.Visible = fVisible
      If fVisible Then
        lngPub = Me.cboPublisherSelect
        strSQL = "SELECT * FROM dbo.fnTitlesByPublisher(" & lngPub & ")"
        .cboBookSelectMulti.Visible = True
        .cboBookSelectMulti.RowSource = strSQL
      End If
    End With
End Sub

Now when you open the form, it should resemble this:

Fig B

The form opens with the list box hidden.

This is pretty much a standard technique for classic MDBs, with a few small differences:

1. The cboPublisherSelect combo box references a stored procedure rather than a table or query.

2. The SELECT statement that we build in the AfterUpdate event invokes a user function and passes a parameter to it.

3. All objects are referred to using the prefix dbo.

Most migrants from MDB to ADP have no prior acquaintance with the concept of object ownership. In a nutshell, a database can have two objects of the same name if their owners are different. To distinguish them, SQL uses the owner name as a prefix. As the developer, you are dbo. When you run the program, Access can see the object that is actually called dbo.ap_PublisherSelect, but other users cannot. Thus, the prefix dbo. Now when other users run the program, Access will be able to find these objects.

The buttons on the footer

In creating this form, we imagined a situation in which the selected books would be processed in some way. For example, you might insert some rows in an OrderDetails table or raise the price 10 percent. The brief code attached to the buttons illustrates two uses. The first button concatenates the titles into a string for MsgBox. The second constructs a typical SQL IN() clause that you might use in insert, update, or delete commands. Here is the code for the OnClick events of the first two buttons:

Private Sub cmdCreateInClause_Click()
  Dim ctl As Control
  Dim intI As Integer, intX As Integer
  Dim strSQL As String
  Dim intColNo As Integer
  intColNo = 0
  strSQL = "IN("
  With Me
    Set ctl = .cboBookSelectMulti
    With ctl
      For intI = 0 To .ItemsSelected.Count - 1
        intX = ctl.ItemsSelected(intI)
        If intI > 0 Then
          strSQL = strSQL & ", " & q(ctl.Column(intColNo, intX))
        Else
          strSQL = strSQL & q(ctl.Column(intColNo, intX))
        End If
      Next intI
      strSQL = strSQL & ")"
    End With
  End With
  MsgBox "Your sample IN() clause:" & vbCrLf & strSQL, vbInformation, "SQL Version"


One more detail: the function q(), whose purpose is to wrap a string in single quotes. SQL Server thinks that strings in double quotes are column references, while Access thinks single quotes announce comments:

Public Function q(v As Variant) As String
  Const sq As String = "'"
  q = sq & v & sq
End Function

This function neatly sidesteps the problem. Note that the function receives a value of any type and wraps it in quotes, even if it’s a number. Why? Because SQL understands this. To prove this, write a query like the following:

SELECT dbo.discounts.*
FROM dbo.discounts
WHERE (discount = '10.5')

SQL Server doesn’t care that the column in question is decimal(5). The syntax still works, so you can skip any code that tests the data type. Wrap everything in quotes and be done with it.

The stored procedure approach

The next version executes a parameterized stored procedure to populate the titles list. To build this form, open the previous form in design mode and save it with a new name. Open the code window and remove the code from the AfterUpdate event of cboPublisherSelect. Replace it with this code:

  Dim lngPub As Long
  Dim strSQL As String
  Dim fVisible As Boolean
  With Me
    fVisible = Not IsNull(.cboPublisherSelect)
    .cboBookSelectMulti.Visible = fVisible
    If fVisible Then
      lngPub = Me.cboPublisherSelect
      strSQL = "EXEC ap_TitlesByPublisher " & lngPub
      .cboBookSelectMulti.RowSource = strSQL
      .cboBookSelectMulti.Visible = True
    End If
  End With

This is only slightly different from the previous approach. The main difference is that rather than construct a SQL SELECT statement, we execute a stored procedure. With large result sets, this approach will be significant, because SQL Server will have the advantage of statistics, self-tuning, and so on, whereas the SELECT statement approach forces recompilation every time.

Return to the Artful SQL Server & Access Tips page