Access 2002 Data Projects for Developers

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Mike Gunderloy

Access data projects were new in Access 2000, and they were clearly version 1 technology: interesting, but not polished. The combination of Access 2002 and SQL Server 2000 makes ADPs a much more reasonable choice for SQL Server front-end development in Access. In this article, Mike Gunderloy looks at some of the new goodies that this combination offers to developers.

As far as I'm concerned, the improvements to Access data projects in Access 2002 are pretty exciting. By working together with the SQL Server team, the Access developers at Microsoft managed to deliver a very workable environment for building front ends to SQL Server data with this release. This article will give you my picks of new features you should investigate if you're considering an ADP solution. While the Jet engine is still not dead, despite persistent rumors to the contrary, it's certainly no longer necessary for serious Access development.

SQL Server Desktop Engine

I'll start with the database engine. Although Access 2002 ADPs can be based on SQL Server 6.5 or 7.0 databases, you'll definitely want to use SQL Server 2000 if you have the choice. Many of the new features in ADPs will only work with a SQL Server 2000 back end. Fortunately, the SQL Server 2000 Desktop Engine (the replacement for the SQL Server 7.0-based MSDE) is in the Office box. I don't want to dig too far into SQL Server itself, but here are a few of the new features in this version:

• Cascading referential integrity
• Better co-existence with previous versions
• Faster distributed queries
• XML querying and results
• Transformations of replicated data
• Extended property support

There are many more improvements to SQL Server 2000 beyond this short list. Some are targeted especially at high-volume servers with gigantic databases. You can find more information at www.microsoft.com/sql/evaluation/features/default.asp.

Designing objects

As a developer, you're likely to spend a good deal of time designing objects. There's good news on this front: The Visual Data Tools designers that are shared with Visual Studio have been replaced by dedicated ADP designers that function much more like the Access design surfaces you've grown to know and love. Figure 1 shows the new Table designer. Note that the column properties are now down in a tabbed dialog, where they belong—and that you can set lookup properties (this is due to the use of extended properties in SQL Server, which I'll discuss in the next section).

The ADP view designer, shown in Figure 2, has undergone a more subtle upgrade. Notable here is the appearance of the Sort Type and Sort Order properties in the grid. Access handles sorted views by saving them as SELECT TOP 100 PERCENT views—a trick that's been floating around the Access community for a while. It's nice to see it supported directly in the product.

The stored procedure designer has been completely redesigned. As you can see in Figure 3, it now strongly resembles the view designer, and it allows graphical design of stored procedures for the first time. Gone is the old "designer" that was nothing more than a text editor. Also gone is the color-coded SQL that the old designer supported; perhaps in a future version we'll get color-coding in all of the Access designers.

Views and stored procedures are now both on a single tab in the database container labeled Queries, just as they are in an Access database. They're joined by functions, a new entity in SQL Server 2000. Figure 4 shows the new function designer, which, as you can see, is yet another variant on the designer for other query types. I'll have more to say about functions later in this article.

Extended properties

For the first time, Access 2002 ADPs using SQL Server 2000 as their database engine can create extended properties—the SQL Server equivalent of the Jet engine's user-defined properties.

This is a great thing, because extended properties are the key to much of the Access user interface. For example, try resizing a column in a datasheet in an Access 2000 project. Close and reopen the datasheet, and your changes will be lost. Now try the same thing in Access 2002; you'll find that the changes persist. Extended properties are behind many of the user interface tricks that make Access easy to work with, and having them available in an Access project is a welcome relief. Subdatasheets and lookup properties for fields are just two more of the many features that this simple change enables.

Of course, as developers it's not enough to know that Access itself makes use of extended properties. We want to be able to create them ourselves. Although there's nothing built into the Access user interface to enable this, you need only master three SQL Server stored procedures and one function to manage your own extended properties:

• The sp_addextendedproperty stored procedure adds a new extended property to a database object.
• The sp_dropextendedproperty stored procedure removes an extended property from a database object.
• The sp_updateextendedproperty stored procedure updates the value of an existing extended property.
• The fn_listextendedproperty function retrieves the value of an extended property or the list of all extended properties from a database object.

Figure 5 shows an Access 2002 form designed for editing extended properties. This form only handles extended properties for tables, but you can easily adapt the code to other extended properties. When you select a table in the combo box, the form uses the fn_listextendedproperty function to stock the list box with all of the extended properties for that table:

Private Sub cboTables_Click()
lboExtendedProperties.RowSource = _
  "SELECT name, value " & _
  "FROM ::fn_listextendedproperty (NULL, _
  'user', " & "'" & cboTables.Column(1) & "', _
  'table', " & "'" & cboTables & "', NULL, NULL)"
lboExtendedProperties.Value = _
       lboExtendedProperties.ItemData(0)
End Sub

This transforms the RowSource for the list box to something like this:

SELECT name, value _
 FROM ::fn_listextendedproperty (NULL, _
  'user', 'dbo', 'table', 'authors', NULL, NULL)

The first argument to the function (which needs to be prefixed by double colons because it's a system-supplied function; this syntax is new in SQL Server 2000) is either the name of an extended property or NULL to retrieve all extended properties. The next six arguments, which you'll see repeat in the other extended property calls, are as follows:

• Level 0 type
• Level 0 name
• Level 1 type
• Level 1 name
• Level 2 type
• Level 2 name

To understand these, take a look at Table 1, which shows the various available types for extended properties.

Table 1. Extended property hierarchy for SQL Server 2000.

To specify an object, you must specify the types and corresponding names, with NULLs for trailing parameters. For example, the table dbo.authors is specified as:

'user', 'dbo', 'table', 'authors', NULL, NULL

While the column LastName in this table would be specified as:

'user', 'dbo', 'table', 'authors', 'column', 'LastName'

To add a new property, you call sp_addextendedproperty, specifying a name and value for the new property, as well as the six-part object identifier. The form does this with a command button:

Private Sub cmdAddNew_Click()
Dim varPropertyName As Variant
Dim varPropertyValue As Variant
Dim strAdd As String
    
varPropertyName = _
 InputBox("Enter a name for the new property", _
     "Add Extended Property")
varPropertyValue = _
 InputBox("Enter a value for the new property", _
     "Add Extended Property")
     
If (Not IsNull(varPropertyName)) And _
  (Not IsNull(varPropertyValue)) Then
      strAdd = "sp_addextendedproperty " & _
       "'" & varPropertyName & "', " & _
       "'" & varPropertyValue & "', 'user', " & _
       "'" & cboTables.Column(1) & "', " & _
       "'table', '" & cboTables & "', NULL, NULL"
End If
CurrentProject.Connection.Execute strAdd
lboExtendedProperties.Requery
End Sub

Property values use the new sql_variant data type, which can hold pretty much anything you throw at it, to a limit of 8016 bytes. To delete an extended property, you pass the property name and the object identifier to sp_deleteextendedproperty. Again, there's a button for this on the form:

Private Sub cmdDelete_Click()
Dim strDrop As String
    
strDrop = "sp_dropextendedproperty '" & _
  lboExtendedProperties.Column(0) & "', " & _
  "'user', '" & cboTables.Column(1) & "', " & _
  "'table', '" & cboTables & "', NULL, NULL"
CurrentProject.Connection.Execute strDrop
lboExtendedProperties.Requery
End Sub

Finally, sp_updateextendedproperty is very similar to sp_addextendedproperty, except that sp_updateextendedproperty will throw an error if the property doesn't already exist:

Private Sub lboExtendedProperties_DblClick _
               (Cancel As Integer)
Dim varNewValue As String
Dim strUpdate As String
    
varNewValue = InputBox("Enter new value for " & _
  lboExtendedProperties.Value, _
 "Update Extended Property", _
  lboExtendedProperties.Column(1))
If Not IsNull(varNewValue) Then
  strUpdate = "sp_updateextendedproperty " & _
   "'" & lboExtendedProperties.Value & "', " & _
   "'" & varNewValue & "', 'user', " & _
   "'" & cboTables.Column(1) & "', " & _
   "'table', '" & cboTables & "', NULL, NULL"
End If
    CurrentProject.Connection.Execute strUpdate
    lboExtendedProperties.Requery
End Sub

If you're going to do a lot of work with extended properties, you might want to wrap up functions for adding, deleting, and updating them into a library database that you can load with your applications.

Linking tables

One of the best features of Access has always been the ease of working with heterogeneous data sources via linked tables. With Access 2002, that feature is available in Access projects as well as databases. Figure 6 shows the new Link Table Wizard in action.

The Link Table Wizard works by constructing SQL statements to build linked servers and views on the server side. This is, at least in theory, something that you could do yourself with Access 2000. But it's one of those tedious tasks that's made much easier by having a Wizard handy. If you construct a link using a linked server based on a SQL Server or Oracle data source, you'll get fully updateable data. You can also use T-SQL links (based on OPENROWSET and OPENDATASOURCE) to build read-only views to a wide variety of data.

If you understand linked servers and the four-part view syntax, of course, you can do this in Access 2000. For example, to link an Access 2000 ADP to a table named Customers in a database named Accounting on a server named HOMER, you'd first create a linked server for HOMER and then build the view.

CREATE VIEW [Homer_Customers]
AS
SELECT * 
FROM [Homer].[Accounting].[dbo].[Customers]

So there's no magic to the Link Table Wizard, and not really any extension to what you can do with data. What the Wizard does is save you a lot of time, and make links in a SQL Server database accessible to those who aren't expert enough with Transact-SQL to build them by hand.

Using functions

SQL Server 2000 implements a new type of rowset-returning object, the function. A function is similar to a stored procedure in that it can return a value that depends on an input parameter. Unlike a stored procedure, though, the result of a function can be used in the FROM clause of a SELECT statement. From the Transact SQL point of view, you can think of a function as a sort of inline procedure; from the point of view of Access ADPs, functions are good ways to handle parameterized queries.

Figure 4 showed you a function in the designer. This particular function takes the storeid that represents a store in the pubs database as a parameter, and returns a rowset containing all books sold in that store together with the quantity sold. Figure 7 shows a form based on this function. When the user selects a store from the combo box, the Change event runs to reset the form's RecordSource property:

Private Sub cboStore_Change()
Me.RecordSource = _
  "SELECT * FROM SalesByStore(" & cboStore & ")"
End Sub

Functions provide a handy trick for the construction of parameterized recordsets. Functions can contain quite complex conditional logic and multiple statements, though once they get past a certain level of complexity they can't be represented in the graphical designer.

Deployment help

Finally, ADPs offer two new menu choices designed to help in deploying your Access projects to a computer other than your development machine:

• Tools | Database Utilities | Transfer Database lets you choose any SQL Server on your network, and supply login credentials and a database name. It then makes an exact copy of the SQL Server back-end database that you're using on the new server. This choice is ideal for moving an ADP back end from your development machine to a production server. After you've used Transfer Database, you'll need to edit the ADP connection string (File | Connection) to point to the new server, and your application will be ready to distribute.
• Tools | Database Utilities | Copy Database File prompts you to supply a name for a disk file with the MDF extension, and then makes a copy of the physical file that holds the database on the server (as a side effect, it has to disconnect all users from the database). This option is useful if you need to deploy to a server that you're not connected to at all times. You can send the physical file to the new server, where it can be imported to SQL Server with the sp_attach_single_file_db stored procedure.

The bottom line

Should you move your databases to Access 2002 projects? Like so many other Access questions, the only sensible answer is "It depends":

• If you've got ADPs in Access 2000 but you're still using SQL Server 7.0 for data storage, the upgrade is nice but not compelling. You'll benefit from the new designers, the Link Table Wizard, and some other minor features, but you'll miss out on extended properties.
• If you've got ADPs in Access 2000 and you've moved the data to SQL Server 2000 (or are planning to do so soon), the upgrade is compelling. Once you start getting the benefit of extended properties as well as the other new features, you'll wonder how you functioned with the old version.
• If you've got multi-user Access databases, it's time to evaluate a move from Jet to SQL Server for the back end. Particularly if these databases are already using ADO rather than DAO, you'll benefit immensely from the more solid multi-user database engine of SQL Server.
• If you've got single-user Access databases, you probably won't want to switch to Access projects, at least in this version. Despite all rumors to the contrary, the Jet engine isn't dead, and it won't die soon. Deploying the SQL Server Desktop Engine with an ADP to individual desktops is harder than it needs to be, and you won't derive much (if any) benefit from the added complexity.

No matter what you decide to do with your applications, you should try to find some time to experiment with ADPs in Access 2002. It's worth knowing what you can do with the Access and SQL Server combination for future use, even if you don't have a pressing current need. If you evaluated and rejected Access 2000 ADPs, I think you'll be pleasantly surprised by the improvements.

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the July 2001 issue of Smart Access. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.