See the SQL Server Bible Wiley for more information about working with SQL Server features such as stored procedures and userdefined functions

Figure 18.45 shows the four queries in the original Basic Northwind.mdb database that were successfully upgraded to SQL Server.

The first two queries were converted to views and the third and fourth to user-defined functions, as indicated by their distinctive icons. Figure 18.46 shows the qryOrderSubtotals view in design view, and Figure 18.47 shows the qryCurrentProductList user-defined function in SQL view.

You now have a client/server application consisting of an Access project front end and a SQL Server back end; from this point on you will need to use SQL Server techniques to work with the project and the back-end tables.

ras-ref

Four upsized queries in an Access project.

All Access Objects

M tbIProdurts 3 tbtShippers 3 tbtSuppliers EH tlkpForms IDÜI tlkpReports 3 zstblBackupInfa Queries qryOrderSubtotals 3I qtotOrders qryCurrentPro d u ctU st qryReports Forms

1Ü fmnuMain 31 frmCategories 3 frmCustomerOrders HI frmCustamers HI frmEmployees 31 frm Orders H frmProductList

FIGURE 18.46

A SQL Server view created from an Access query.

FIGURE 18.46

A user-defined function created from an Access query.

tolTER FUNCTION dbo.qiyCurrentProductList 0

RETURNS TABLE AS

RETURN [ SELECT TOP 100 PERCENT ProductlD, ProductName FROM dbo.tblPro ducts WHERE (Discontinued = 0] ORDER BY ProductName)

tolTER FUNCTION dbo.qiyCurrentProductList 0

RETURNS TABLE AS

RETURN [ SELECT TOP 100 PERCENT ProductlD, ProductName FROM dbo.tblPro ducts WHERE (Discontinued = 0] ORDER BY ProductName)

0 0

Post a comment