You’re a seasoned Microsoft Access developer who can write VBA code in your sleep. For years, you’ve been developing complex applications in the classic MDB file type, but your very success now threatens you: Your tables have upwards of 50,000 rows, and the number of users is going up. The classic front-end/back-end style you’ve been using for years is beginning to show marks of strain. So what do you do?

Microsoft supplies an upsizing wizard that promises to do most of your upsizing work, but wizards are by definition easy and incomplete. Instead, a leap to SQL Server could provide you with several major wins. Above all, an Access Data Project (ADP) front end tied to a SQL 2000 back end is dramatically faster than the equivalent front end/back end, even if you make almost no changes to the front end.

But a simple upsize won’t do everything. In particular, your front end may contain logic that simply makes no sense in the SQL world. 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.

In the SQL world, though, 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 statements. Figure A illustrates the situation.

Figure 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, in which 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 a stored procedure and a user function, shown in Listing A and Listing B, respectively.

We’ll use this stored procedure to populate a combo box containing a list of publishers. Listing B contains our user function. If you’re new to user functions, note that it returns a table and thus may be substituted anywhere you might use a table.

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. 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. 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. 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. 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. (Listing C shows the code required to do this.)

When you open the form now, it should resemble Figure B.

Figure B
The form opens with the list box hidden.

This is pretty much a standard technique for classic MDBs. There are a few small differences:

  • ·        The cboPublisherSelect combo box references a stored procedure rather than a table or query.
  • ·        The SELECT statement that we build in the AfterUpdate event invokes a user function and passes a parameter to it.
  • ·        All objects are referred to using the prefix dbo.

Most if not all 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. Listing D shows the code for the OnClick events of the first two buttons:

One more detail: the use of 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 the code in Listing E.

This approach is only slightly different from the previous one. 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.