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:
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:
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.