Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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

3. Click the Execute Query button on the toolbar or press F5 to run the
   query.

4. 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."