Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 16.5 Create a Smart Tag on a Table in an Access Project

16.5.1 Problem

I would like to create a smart tag on a table in my Access Project (.adp). When I open the SQL Server table in the table designer in my Access project, I do not see the smart tag property, although it is listed for controls in the Forms designer.

16.5.2 Solution

In SQL Server, the smart tag property has to be set as an extended property since it is not one of the standard SQL Server table properties. This requires that you run SQL Server's built-in sp_addextendedproperty system stored procedure to add it as an extended property. The syntax shown in SQL Server Books Online for sp_addextendedproperty is not that easy to figure out, as you can see from this listing:

sp_addextendedproperty
    [ @name = ] { 'property_name' }
    [ , [ @value = ] { 'value' } 
        [ , [ @level0type = ] { 'level0_object_type' } 
         , [ @level0name = ] { 'level0_object_name' } 
            [ , [ @level1type = ] { 'level1_object_type' } 
             , [ @level1name = ] { 'level1_object_name' } 
                    [ , [ @level2type = ] { 'level2_object_type' } 
                     , [ @level2name = ] { 'level2_object_name' } 
                    ] 
            ] 
        ] 
    ]

Follow these steps to add the PersonName smart tag to the LastName column in the Employees table in the NorthwindCS SQL Server database:

Launch the SQL Server Query Analyzer and connect to your SQL Server as a system administrator.

Type the following code in the query window or load it from 16-05.SQL in the sample directory:

USE NorthwindCS
GO
EXEC sp_addextendedproperty 'MS_SmartTags', 
 'urn:schemas-microsoft-com:office:smarttags#PersonName', 
 'user', dbo, 'table', Employees, 'column', LastName
GO

Click the Execute Query button on the toolbar or press F5 to run the query.
Open the 16.05.adp sample project and open the Employees table in datasheet view. You will see the PersonName smart tag displayed for every entry in the LastName column in the Employees table.

16.5.3 Discussion

In order to execute sp_addextendedproperty, the minimum permissions required are membership in the db_owner and db_ddladmin fixed database roles. The code listing here assumes that you are running it as a system administrator (the dbo user).

Unlike creating a smart tag in an Access/Jet database, there is no way to propagate the new extended property to any previously existing forms automatically. However, new forms that you create on the Employees table will inherit the new Person Name smart tag set on the LastName column. You can test to see if the code executed correctly by creating a new AutoForm on the Employees table, as shown in Figure 16-7.

Figure 16-7. A new form created after running sp_addextendedproperty inherits the smart tag extended property

Access does not itself have a full-featured development environment for creating SQL Server applications. There are many features missing, such as the ability to administer SQL Server security. It is highly recommended that you purchase the Developers Edition of SQL Server, which Microsoft has made available for a nominal price. Even if you are using MSDE, you can install the client tools, which include the SQL Server Enterprise Manager, Query Analyzer, and Profiler. You can find more information about the Developers Edition version of SQL Server at http://www.microsoft.com/sql/howtobuy/development.asp.

16.5.4 See Also

For more information on working with extended properties in SQL Server, see the SQL Server help topic, "Using Extended Properties on Database Objects."

[ Team LiB ]