Microsoft Office 2000/Visual Basic Programmer's Guide

Working with the Objects in a SQL Server Database from Access

The database window for an Access project displays options to work with the following objects contained in the SQL Server database back-end:

• Tables   You can create and modify tables stored in a SQL Server database. By using the Design view, you can add or modify columns (fields), define their properties (such as data types), and set default values. You can also define additional properties of the tables, such as check constraints (which are similar to Access validation rules), and indexes, by displaying the table’s Properties dialog box. And, you can create one or more triggers, which are actions that are performed whenever a record is added, updated, or deleted. Triggers are defined by using Transact-SQL statements. To add a trigger to a table, right-click the table in the Database window, and then click Triggers.
  
  
• Views   You can create and modify views that are stored in a SQL Server database. A view is essentially the same thing as an Access select query. That is, a query that returns records from one or more tables by using an SQL SELECT statement. You define views in Design view by using visual tools that are very similar to those available in the query Design view for Access databases. However, queries that require argument aren't defined as views, but as stored procedures. Additionally, queries that perform bulk operations, such as Access action queries, are also defined as stored procedures on SQL Server.
  
  
• Database diagrams   Database diagrams provide a graphic display of the tables and relationships in your database that is similar to the Relationships window for Access databases. In addition to allowing you create, edit, or delete relationships between tables, you can also create, edit, or delete tables and table column definitions. To work with database objects that aren't graphically represented in the diagram, such as indexes and constraints, you can display the table's Properties dialog box. When you save a database diagram, the database is updated to match your diagram. You can create more than one database diagram to work with subsets of your database's design.
  
  
• Stored procedures   In a SQL Server database, a stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. A stored procedure can be executed with one call from an application; and allows user-declared variables, conditional execution, and other powerful programming features. Stored procedures can contain program flow, business logic, and queries against the database. They can accept arguments, output parameters, return single or multiple result sets, and return values. You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:
  • You can execute a series of SQL statements in a single stored procedure.
    
    
  • You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.
    
    
  • The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.

Stored procedures can be used for a broad variety of functions, much like VBA procedures, but their functionality is specific to SQL Server and the Transact-SQL language. They are similar to Access action queries, but they can perform a much broader array of actions against the database and the server itself.