Part VI: Access as an Enterprise Platform
1188
database objects are stored procedures, user-defined functions, and triggers. Stored procedures are much
like VBA procedures, except the statements they contain consist of SQL language commands. A
SQL Server user-defined function is a type of stored procedure that returns a value, much like an
Access VBA function, while a trigger is a SQL statement attached to a SQL Server table that runs in
response to changes to the table’s data. Access data macros are very, very similar to SQL Server
triggers and are, in fact, often referred to as triggers in Access tables.A stored procedure is a block of commands that operates against data in the database. A user-defined
function is similar to a stored procedure except that it returns a single value. A trigger is an event
detector, which executes a sequence of commands when a specific event occurs with the database.
These objects add intelligence and logic that determines how data stored in the database is handled
by the database server.Note
Stored procedures are almost always written in the standardized structured query language (SQL) common to
all server database engines. The SQL you see in an Access query’s SQL view is similar to the SQL syntax used
by SQL Server, but there are many, many differences. Except for the smallest and simplest Access queries, you
can’t copy Access SQL code and use it in a SQL Server stored procedure.
Access has its equivalent of stored procedures, functions, and triggers, in the form of data macros,
modules, and class modules.l Data macros: Access 2010 introduces data macros, which are code elements attached
directly to fields in Access tables. Data macros behave much like SQL Server triggers, in
that data macros are fired whenever data is added, deleted, or changed in an Access table.
A data macro attached to an Access field or table is in effect anywhere the data is used: on
a form, on a report, on a query, or in VBA code.Cross-Reference
For more information on data macros, turn to Chapter 15.
l (^) Modules and class modules: Modules and class modules are blocks of VBA code, stored
within an Access database. You’ve seen many examples of VBA code in other chapters in
this book, so no further explanation is necessary.
These three objects all perform a similar function to that of stored procedures in a server database:
They execute sequences of commands in response to changing conditions in an Access database.
Those commands typically act on data stored within an Access database, but they can do much
more, such as modify the user interface or interact with the user.
In reality, an Access database is not suitable for the extreme processing requirements of large
groups of users, public-facing Web sites, or massive amounts of data. Microsoft never intended
Access to be an enterprise database system. The challenging role of enterprise data management is
better suited to specialized server database engines like SQL Server and Oracle Database.