About User-Defined Functions

When your Microsoft Access project is connected to a Microsoft SQL Server 2000 database, you can create and use user-defined functions. The following example shows the ListCust user-defined function that accepts a named parameter, returns a table, and is used in the FROM clause of a SELECT statement.

SELECT * FROM ListCust(@[Enter a Name])

Overview of user-defined functions

A user-defined function is a query, like a view or stored procedure, that you can:

• View in the database window.

• Create in the Query Designer or SQL Text Editor.

• Use to retrieve data and browse the results in a datasheet

• Use as the record source of a form, report, or combo box. 

  Note   You cannot use a user-defined function as the record source of a data access page.

• Programmatically manipulate using the Access object model.

User-defined functions combine the best features of views and stored procedures into a single query that you can nest, pass parameters to, sort, and return values. In many cases, user-defined functions are a better alternative to views and stored procedures because you can return a single table of data or scalar value, hide the business logic and details of how that data or scalar value was created, and simplify the complexity of your SQL statement syntax.

For more information about and examples of user-defined functions, see the SQL Server documentation.

The three types of user-defined functions

Based on what kind of value it returns, each user-defined function falls into one of three categories:

in-line user-defined function   Contains a single SELECT statement and returns an updateable table of data. You can use this function in the FROM clause of a query. You can graphically create and edit an inline user-defined function by using the Query Designer. You can also define extended properties.

table user-defined function   Contains one or more SELECT statements and returns a non-updateable table of data. You can use this function in the FROM clause of a query. You can create and edit a table user-defined  function by using the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a table user-defined function by using the Query Builder. However, you cannot define extended properties.

scalar user-defined function   Contains one or more SELECT statements and returns a scalar value, such as the int, decimal, varchar, sql_variant or table data types. You can use a scalar function in a query anywhere you would use a column name. You can create and edit a scalar user-defined  function by using the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a scalar user-defined function by using the Query Builder. However, you cannot define extended properties.