Access Cookbook - Ken Getz [311]
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.
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
TIP
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.
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."
16.6. Create a Custom Smart Tag to Get a Weather Report
Problem
I have a call list of customer names. I'd like to use a smart tag on the postal code field to retrieve a weather report for that postal code so that when I make the call, I can talk about the weather. How can I create a custom smart tag that retrieves the weather forecast from the Internet for a given postal code?
Solution
There are two different approaches to creating your own smart tags: you can create an XML file or you can create a dynamic-link library (DLL). Using an XML file is the best solution when you want to create a smart tag that simply navigates to a location on the Internet (or an intranet). Creating a DLL is the preferred approach when your smart tag is more complex and you need more flexibility or conditional logic. In this example you'll learn how to create an XML-based smart tag.
The first step is to create the XML file. This example will navigate to the weather forecasting section of the MSNBC Web site at http://www.msnbc.com. It takes multiple mouse clicks and typing in a zip code to find local weather conditions if you obtain the weather forecast for a given zip code manually. Once you get there, if you look at the URL of the local weather page after typing in the zip code 96708, you'll see that the URL looks like the following:
http://www.msnbc.com/news/wea_front.asp?tab=oth&czstr=96708&ta=y&accid=96708
You can create your own XML smart tag (this example is called Weather.XML) by creating an XML file using the following format. Note that the FL:url tag contains the revised URL with the literal zip code replaced by {TEXT} placeholders: