Chapter 37: SQL Server as an Access Companion
1221
This code is intended simply to show how ADO is able to run a SQL Server stored procedure,
retrieve the procedure’s return value as a recordset, and then use the recordset in Access. In this
particular case, the connection to SQL Server is made through the ADO provider, and not through
ODBC.
Triggers
A trigger is a bit of SQL code that executes when some action occurs on a table in a database.
Typically, triggers execute as before (FOR in SQL Server), as after (AFTER in SQL Server), and as
instead of (INSTEAD OF in SQL Server) triggers. As the name implies, a before trigger fires before
data is changed in the table, while an after trigger files after the data has changed.
The biggest danger with triggers is that they can be recursive, calling themselves over and over,
resulting in serious performance problems. For example, consider an AFTER trigger that changes
data in a table. If the change invokes the trigger a second time, an endless recursive loop may
occur.
Creating a trigger is very similar to a procedure or a function. This example creates an entry in a
log file every time a new product is added:
USE test
CREATE TRIGGER LogEntries ON Products
FOR INSERT
INSERT INTO LogFile(id,event)
VALUES(<autocounter>,’New product added’);Summary
This chapter has taken a look at some of the capabilities possible when Access is partnered with
SQL Server. Although some of the techniques used to access SQL Server have changed in Access,
the same capabilities are available as in previous versions of Access.
In many ways, Access is the ideal interface tool for SQL Server data. SQL Server provides a high
level of data security, the ability to service thousands of simultaneous users, and advanced data-
management tools such as log files, stored procedures, views, and triggers. Also, the storage capac-
ity of SQL Server installations is practically unlimited. Many SQL Server installations manage
billions of records, making all that data available to qualified client applications such as Microsoft
Access.