Chapter 40: SQL Server as an Access Companion
Access projects are used to create and maintain SQL Server 2005 Express edition databases, or “full” SQL Server 2005 databases (from here on usually referred simply as SQL Server). You can also use an Access project to create the user-interface objects and forms, reports, macros, and modules, which get their data from SQL Server. The database window for a project looks very similar to the Access database window you are already accustomed to. In fact, creating the user-interface objects is virtually the same as creating them in Access.
This chapter uses a database named Chapter40.accdb. If you have not already copied it onto your machine from the CD, you’ll need to do so now.
In general, SQL Server Express edition automatically installs as if it is to be executed on the same computer you’re working on. In other words, even though SQL Server is a server database, the default for SQL Server Express is to execute on the same computer as a client environment such as Access or Excel.
SQL Server 2005 Express edition is free. You can download it from (http://msdn.microsoft.com/vstudio/express/sql/) and use it as a development and deployment database server. Chapter 41 discusses SQL Server 2005 Express Edition in more detail.
Before beginning this chapter, keep in mind that the intention when using SQL Server is to use SQL Server to store data and Access to host and present an application’s screens (forms and reports).
This chapter examines a number of different ways to access SQL Server data from Access 2007. Although there are no SQL Server object designers (tables, stored procedures, views, and so on) in the .accdb file format, you can use Open Database Connectivity (ODBC) to link to SQL Server data objects. However, if you choose to use the Access 2000 ADP data file format you still have access to the full range of SQL Server object designers (as in earlier versions of Access). You can edit existing or create new tables, stored procedures, and views when using the ADP file format.
Be aware, however, that when you create an ADP file in Access 2007 (File⇒New⇒Browse, then select ADP from the Save as Type drop-down list), you’re actually creating an Access 2000–format data file. This isn’t a big issue for most developers, but you may encounter situations where Access 2007 features cannot be supported in an application because the file is not an .accdb.
Downloading SQL Server 2005 Express Edition
Microsoft is vitally interested in developers learning and using SQL Server 2005. But, the truth is that acquiring and installing the “full” versions of SQL Server can be daunting tasks. As a server application, SQL Server is relatively expensive to license, and its hardware requirements are rather extensive. Not to worry! Microsoft has a wonderful gift available to you, free for the downloading.
SQL Server 2005 Express Edition is a somewhat stripped-down version of SQL Server intended to be used as a database engine for smallish workgroup applications, and as a test platform for developers working on SQL Server front-end applications. You may freely download and install SQL Server 2005 Express, install and use it on your computer, and even bundle it with applications you distribute to users.
SQL Server Express works and behaves exactly like SQL Server Enterprise, its much bigger brother. SQL Server Express supports all of the data types, stored procedures, triggers, and other database objects used in SQL Server Enterprise. In fact, migrating a SQL Server Express database to SQL Server Enterprise involves nothing more than disconnecting from SQL Server Express and connecting the database files to SQL Server Enterprise.
The primary differences between the standard editions of SQL Server 2005 and SQL Server Express is that SQL Server Express databases are limited to 4 GB in size (twice that of Access 2007!), and SQL Server Express does not support some of the more advanced features of SQL Server Standard and Enterprise Editions. Otherwise, the database engines in all SQL Server 2005 editions are identical.
You really owe it to yourself and your users to take a look at SQL Server 2005 Express. At the time of publication, the official home of SQL Server 2005 Express Edition can be found at http://msdn.microsoft.com/vstudio/express/sql, or do a Web search for “SQL Server Express download.”
Connecting to SQL Server
One of the most fundamental operations with any large-scale multiuser database engine, such as SQL Server, is connecting to the database. Connecting directly to SQL Server, using SQL Server front-end tools is quite easy. The most basic connection to a database engine such as SQL Server, or even Oracle Database, is called just that: a connection. A connection is made by routing to that database on a specific computer, using a username and password (for security), usually using some type of network protocol such as TCP/IP.
What is a listener?
In SQL Server 2005 Enterprise Edition, a Windows service called SQL Browser is designated as the SQL Server database listening process. A listening process (or listener) quite literally listens over a network for requests to connect to a database. Computers on an Ethernet network place messages onto the network pipeline. Those messages are continually passed around the pipeline, where a message is routed to by routers, based on an IP address, and sent to the computer having the matching IP address.
A listener listens on a network for messages with an IP address matching its own. When the listener hears a network message directed to its IP address, the listener removes the message packet from the network and services the message. The listener asks the server for a database connection to use for processing the message. When the server grants a connection to the database, the listener hands the connection over to a database server process (server database engines like SQL Server are multi-threaded, and can simultaneously process many, many requests). The database server processes the request (perhaps a query), and then passes the result back to the sender. Because the server process assumes responsibility for servicing the user’s request, the listener is able to continue listening for new database connection requests.
The interaction between the listener process and the database server means that the database engine does not have to spend time listening for messages directed its way. Instead, the highly specialized listener serves as a “traffic controller” for the database engine, enabling the engine to continue working at top-speed on user requests. Once the database engine allocates an execution thread to process the message handed to it by the listener, the listener returns to its primary task of listening for the appropriate types of messages on the network.
What is a connection string?
The specification that is used to communicate with a database is called a connection string. A connection string is made up of a number of things:
• Hostname: The host is the computer where the database server resides.
• Database name: The name of a database on a specific server. SQL Server allows for multiple databases in a single installation, as well as multiple SQL Server installations on a single computer. Now and then, a SQL Server installation services a single database for each database server.
• Authentication: A username and password are used for security. Only authorized users will have usernames and passwords; thus, only people with usernames and passwords have access. In some environments, the username and password can use an operating system username and password. In other cases, a username and password can be part of the database software itself.
The easiest way to connect to a SQL Server database may be to use a command-line shell utility called SQLCMD that comes with both SQL Server and SQL Server Express (look in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn folder). SQLCMD provides a simple, command-driven interface to SQL Server. Although not practical for managing complex databases, SQLCMD provides a simple way to verify a SQL Server installation.
The options for SQLCMD are shown in Figure 40-1.
Figure 40-1
The SQLCMD utility has numerous options.
Highlighted in Figure 40-1 are the two most significant options, which are –S server (the computer on which SQL Server is running) and -d use database name (the name of a database within the SQL Server installation).
The name of the server in Figure 40-1 (-S server) is in reality a SQL Server instance name, not the name of a computer. Figure 40-1 shows a hostname which is the name of a computer on a network. When installing SQL Server, the default name applied to the SQL Server instance is the name of the host computer. Also, by default, SQL Server security is set to use the user’s login name and password as authentication. In other words, SQL Server uses Windows security and the name of the machine on the network.
When using the SQLCMD utility, the easiest way to communicate with a SQL Server database is with the following command to get to a specific SQL Server installation:
sqlcmd –S mycomputer
And the following more refined command to connect to a specific database, within a specific SQL Server installation:
sqlcmd –S mycomputer –d mydatabase
Figure 40-2 shows two connection screens for a tool called SQL Server Management Studio (SQL Server Management Studio can be downloaded from the same site as SQL Server Express). The screen on the left is connecting to a SQL Server on a remote computer called P450, using a SQL Server stored username and password to authenticate. The screen on the right side of Figure 40-2 is on the local computer, using the Windows username to authenticate.
Figure 40-2
Connecting to a SQL Server in the Management Studio
As you can see, the connection parameters (making up the connection string) are the same for both the command-line shell SQLCMD utility and for the windows GUI Management Studio connection to a SQL Server.
Connecting to SQL Server from Access
Creating a connection between Access and SQL Server environments requires a little something extra, as opposed to just a simple database connection, because both Access and SQL Server are essentially autonomous environments that must work together. As with many relational databases running under Windows, drivers are used to allow tools such as SQL Server and Access to communicate. As is common with many Microsoft software tools and toys, special drivers are created to facilitate communication between different software products. These drivers can be used to connect tools such as Excel and Access to an Oracle or DB2 database or, in this case, an Access database connected to SQL Server.
The drivers in question fall into a number of categories and include Object Database Connectivity (ODBC), Object Linked Embedding (OLE), and native drivers. Native drivers are often the best and fastest way to connect to server database engines, but they tend to be less generic and adaptable, and likely to apply to one specific product or database. Many of these drivers are produced by Microsoft because they all run under Windows operating systems. Some individual vendors do produce their own ODBC and OLE drivers. Microsoft drivers are often said to be more reliable, but that is an issue open to debate.
Let’s focus on an ODBC driver allowing Access to communicate with a SQL Server. How does one deal with an ODBC driver? You have to create an ODBC data source, then the reference the ODBC data source from within Access. First, you create an instance of an ODBC driver, on your client computer, provide the ODBC driver a name (Access associates with that name). Then Access talks to the data source name (DSN), which is hooked to the ODBC drive. The ODBC driver in turn contains the connection string to the SQL Server, which talks to the SQL Server database either on your local computer or remotely, to another computer, across a network.
Create a data source as follows:
1. Go to Windows Start menu, and choose Settings⇒Control Panel.
2. In the Control Panel, double-click the Administrative Tools option, and select Data Sources (ODBC).
The three ODBC configuration options are
• User DSN: A User DSN applies to a specific user on the client computer on which the User DSN is created.
• System DSN: A System DSN is similar to a User DSN except it applies over a network (to a certain extent).
• File DSN: A File DSN creates a connection configuration (a connection string), for a database, into a file on your client computer.
Of these three options, perhaps the File DSN is most useful in most situations. Because the connection information is stored in a file (the default location for DSN files is C:\Program Files\Common Files\ODBC\Data Sources), it is easy for you to share a DSN configuration with other users. Sharing a File DSN is easy: simply locate the DSN on your machine and attach it to an email, or move it to a common location on the network. User DSNs and System DSNs are actually stored in the computer’s registry and must be manually set up on each computer needing access to a data source.
Essentially, what you are doing in this situation is creating a link from Access to data that is stored in SQL Server. Therefore, tables are maintained in SQL Server, and the front-end application (queries, forms, reports, VBA code, and so on) is maintained in Access. In Chapter 17, you examined importing tables into Access, making copies of metadata and data, from something like a SQL Server or an Oracle database, creating complete copies of data in an Access database. In this case, you want to simply link between Access and SQL Server because data are maintained in SQL Server, and not copied in their entirety in Access. How can you do this?
1. In Figure 40-3, you can see that you select the External Data tab, the Import section, and the More drop-down control from the Access user interface.
Figure 40-3
Linking to an ODBC data source
2. From the More drop-down control select ODBC Database as a source to link to. As shown in Figure 40-4, select to link to a table in an external database. Click OK to continue.
Figure 40-4
Linking to a table stored externally to Access
The screen you get is as shown in Figure 40-5.
Figure 40-5
Selecting a data source type
The options are to link to a File Data Source, a User Data Source, or (possibly) a System DSN to communicate between Access and SQL Server. You will see a System Data Source tab in the Select Data Source dialog box when a System DSN is already available on your computer.
3. Type LocalSQLServer into the DSN Name box and click New.
4. Scroll all the way down the list of drivers and select the SQL Server driver (ignore the SQL Native Client driver for now), and click Next.
5. When asked where to store the File DSN file, click the Browser button, enter LocalSQLServer in the File Name box, click Save, and click Next when back at the screen with the Browse button.
You should have a screen entitled Create New Data Source.
6. Click Finish.
Your next screen is shown in Figure 40-6.
7. Note how the File DSN filename you created is now shown in the data source name box in Figure 40-6. Enter that, as shown in Figure 40-6, into the other two entry fields.
The description is not essential. The name of the SQL Server shown in Figure 40-6 is necessary and should obviously be a SQL Server you can actually get to, if you have access to one.
Figure 40-6
Creating a new data source connection to a SQL Server
8. Click Next.
9. As you can see in Figure 40-7, you can change all sorts of things.
Figure 40-7
Authentication of data source connection to a SQL Server
The default settings shown in Figure 40-7 assume you have a SQL Server with operating system authentication (SQL Server uses your Windows login name and password to authenticate you and grant permission to its databases).
10. Click Next.
The next screen, shown in Figure 40-8, shows that my SQL Server installation contains a database called test.
Figure 40-8
Changing the name of the default database to access
In Figure 40-8, I have checked the Change the Default Database To checkbox, and changed the text of the database name from master to that of test.
11. Click Next.
12. The following screen you don’t need to worry about. Just click the Finish button on it. The screen after that you should worry about. Figure 40-9 shows the last dialog box of the SQL Server Setup wizard after clicking the Test Data Source button.
If the connection fails, go back over these steps again. If that doesn’t help, you might want to consider talking to a database administrator or a network administrator.
SQL Server is rather fussy about its name. Prior to SQL Server 2000, SQL Server assumed the same name as its host computer because only one instance of SQL Server could be installed on a computer. However, beginning with SQL Server 2000, a single computer can host multiple SQL Sever installations, so the name you use to reference a SQL Server instance is a bit more complex. The syntax used to reference a SQL Server instance is MyComputer\MySQLServerInstance, where MyComputer is the name of the host computer, and MySQLServerInstance is the instance you wish to reference.
If only a single SQL Server instance is installed on the local computer, you may be able to, quite simply, specify (local) as the name of server. Otherwise, you’ll have to provide the name of the computer and the SQL Server instance name as described in the previous paragraph.
Figure 40-9
Ensuring a successful Access to ODBC to SQL Server connection
13. Click OK on the first screen, and then click OK again on the second screen.
The resulting screen should look like what you see in Figure 40-10.
Figure 40-10
Using an ODBC connection to link to SQL Server using a File DSN
14. If you want to create a User DSN, a System DSN, or both, go ahead and do so. You’ll simply be creating multiple data sources to the same SQL Server database. Click OK to save the new DSN.
Ignore all the system tables (prefixed with sys.) and tables prefixed with INFORMATION_SCHEMA in the Link Tables dialog box. The tables you’re interested in are generally at the top of the list in the Link Tables dialog box.
15. Next select tables from the Link Tables dialog box shown in Figure 40-11.
In Figure 40-11, a single table has been created in the test database. The table is named dbo.Table_1, as highlighted in Figure 40-11. Click the OK button to close the Link Tables dialog box.
dbo is shorthand for database owner and is the default prefix for all objects within a SQL Server database. A full explanation of SQL Server authentication, security, and ownership is well beyond the scope of this chapter. For the meantime, it’s enough to understand that SQL Server supports multiple users, each of whom is identified by a name. When a user creates a SQL Server object, he creates the object with either his own name prefix, or with the default dbo prefix, depending on how SQL Server security is configured.
16. Click OK.
The next screen shows the fields to select from the table called Table_1. We’ve selected both fields, which are highlighted as shown in Figure 40-12.
Figure 40-11
Selecting tables from a SQL Server database
Figure 40-12
Selecting fields from a table, from a SQL Server database
17. Click OK.
The result is a table linked from Access, into a SQL Server database. That data is shown in Figure 40-13, listing the contents of the table from within the Access 2007 graphical user interface (GUI).
Figure 40-13
A table linked from Access 2007 to SQL Server is entitled dba_<tablename> by default.
As you can see, connecting from Access 2007 to a SQL Server database is really quite simple, even if quite a number of steps are involved.
SQL Server security
SQL Server security works as many other large scale relational database do, with one small additional difference. Many relational databases such as Oracle Database authenticate by validating usernames and passwords, which are stored in the Oracle database as separate user definitions.
SQL Server can also validate in this manner, allowing a connection to a SQL Server database using a SQL Server username and password, stored within a SQL Server database. The additional factor with SQL Server is that it can authenticate a connection to a SQL Server with a local or even a network Windows username and password. Thus, if you’ve already logged in to Windows using a network username and password, you’re essentially already connected in both the operating system and the SQL Server installation. In other words, when connecting to SQL Server using Windows authentication, the connection tool automatically uses the Windows username and password to connect to SQL Server, without any need to enter a username and password. This is perhaps the easiest and least obtrusive approach to connecting Access databases to SQL Server.
Oracle also supports connections through an operating-system-level username and password, but the process is not quite as seamless and transparent as with SQL Server and Windows. The obvious reason for that is because SQL Server and the Windows operating system are both produced by the same company—Microsoft. Oracle, on the other hand, operates on a variety of operating systems and does not benefit from a homogenous environment as SQL Server does. One of the major benefits of Microsoft software is its inherent integration with all types of software tools and kits.
Comparing Access 2007 and SQL Server Security
How is security in Access 2007 comparable with SQL Server? There is much in the way of security within Access. This includes security features such as trusted zones, trusted Web sites, trusted objects, and restrictions on who can view or change specific documents. When using the .mdb file format, Access allows creation of users, plus granting and revoking of a minimum number of privileges to those users. Access users and privilege details are added to a workgroup information file (.mdw filename extension), where workgroups are groups of users.
An Access workgroup is much the same as a role in SQL Server. And an Access user is much the same as a SQL Server user. Access allows authentication of users with passwords. SQL Server does the same.
The only real difference is that the privileges allowed for allocation to users in Access are few. Privileges in relational databases such as SQL Server and Oracle Database number in the hundreds and are divided into system and object privileges. A system privilege allows access to metadata, such as creating a new table. An object privilege allows access to data, such as adding new records to a table.
Access (when using the .mdb format with workgroup security configured) pretty much parallels Oracle and SQL Server security. Unlike Access, however, SQL Server and Oracle security is generally applied to data, while Access workgroup security applies to data and the user interface. Data security restricts or allows access to data. From the perspective of data in a SQL Server database, you secure the content of applications, rather than the actual objects or front-end customer facing parts of an application.
SQL Server security is very comprehensive. Data stored in SQL Server is secured not only by the SQL Server database engine, but also by the Windows operating system, as well is whatever security has been applied to the network system on which SQL Server operates. In addition, because a SQL Server database is normally located on a dedicated server computer, the database itself is inaccessible to users. There is very little chance that a user would be able to download a SQL Server database and carry it away on a removable disk.
When it comes to object security, there is some crossover between Access and SQL Server with the advent of object-relational databases (including objects into relational databases). For example, the types of objects that can be stored in a database include things like Word documents, XML documents, images, audio files, and so on. At the data level, SQL Server security can restrict access to specific objects.
With respect to Access being also an application tool, something like an XML document can be executed from within a browser. So, you can effectively secure something like an XML document in both a database and an application, or even both. Even for the scenario of combined features between Access and SQL Server, there is still a distinct difference between database-level security and application security, because a database stores data and an application consumes that data. Again, there is crossover, such as with stored procedures in SQL Server, because stored procedures in a relational database can operate functionally on data. An application operates functionally on data.
Access DDL Security Commands
Access 2007 allows execution of the following Data Definition Language (DDL) commands within Access. These commands cannot be executed from within Access onto another database such as SQL Server. Similar DDL commands can be executed in SQL Server directly, regardless of any communicating Access 2007 database. These DDL commands are allowed in Access 2007:
• CREATE USER: Create one or more users with username and password:
CREATE USER <user> <password> [, ... ]
• ALTER USER: Change an existing Access user’s password:
ALTER USER <user> PASSWORD <new> <old>
• CREATE GROUP: Create one or more groups with group name and personal identifier for a person or group of persons (a person is an Access user):
CREATE GROUP <group> <pid> [, ... ]
A group allows you to group privileges together (like a role in SQL Server). Then you can subsequently add users to that group, granting all the privileges assigned to the group to any user added to that group.
• ADD USER: Add one or more existing Access users into an existing group:
ADD USER <user> [, ... ]
• ALTER DATABASE PASSWORD: Change the password for an entire Access database:
ALTER DATBASE PASSWORD <new> <old>
• GRANT and REVOKE: Grant privileges to, and revoke privileges from, a user or a group:
GRANT <privilege> [,...] ON {TABLE <table>|OBJECT <object>}
TO <user>|<group>
REVOKE <privilege> [,...] ON {TABLE <table>|OBJECT <object>}
FROM <user>|<group>
Some of the more interesting allowed privileges are as follows:
• SELECT: Read data from a table.
• INSERT: Add new records into a table.
• DELETE: Delete records from a table.
• UPDATE: Change existing records in a table.
• CREATE and DROP: Create or drop an object, respectively, such as creating a new table.
• SELECTSECURITY and UPDATESECURITY: Read and change security, respectively, such as user and password changes.
• DBPASSWORD: Change the password for an entire Access database.
• DROP: The DROP command has different variations allowing dropping of users and groups:
DROP USER <user> [, ...]
DROP USER <user> [,... ] FROM GROUP
DROP GROUP <group> [, ...]
Working with SQL Server Objects from Access
SQL Server contains all sorts of things that Access does not, in addition to standard relational tables.
The term relational tables implies that both Access and SQL Server contain tables and indexes, as well as primary and foreign keys with enforced referential integrity.
SQL Server also supports database objects such as stored procedures, functions, and triggers. Whatever is linked from SQL Server (not imported) can be utilized from Access tables linked to the underlying SQL Server objects.
Everything is SQL Server–based because Access 2007 can do one of two things:
• Import tables (with data) from SQL Server as a copy of the SQL Server table. Any changes to the copy in Access will not be reflected in SQL Server. And any changes to the same tables in SQL Server will require a refresh in Access, which in this case means a full and complete re-import of an entire table.
• Link to tables that remain within SQL Server. Linked Access tables can update SQL Server data because the table and data actually reside in SQL Server, not in Access. In fact, the interaction between an Access table linked to SQL Server is so seamless that most users are unaware that they’re working with remote data.
Data types and a comparison between Access 2007 and SQL Server data types are covered in Chapter 41. The precise meshing of tables between Access and SQL Server is more relevant there. The chapter covers upsizing, which is essentially converting from Access and SQL Server. The data types in SQL Server are much more comprehensive. This chapter deals with top-level objects, such as tables and views, not the structure of those objects (the fields within tables). The real task of this chapter at this point, in explaining SQL Server as an Access 2007 companion, is to show which SQL Server objects can be accessed from Access 2007.
Using SQL Server tables from Access
The Access 2007 .accdb database looks as shown in Figure 40-14.
Figure 40-14
The Access 2007 tables in the Chapter40.accdb database
As shown in Figure 40-14, some tables have been imported from SQL Server, and some tables are linked. The Region table is an imported table, which means the table and its data reside in Access. The data of the Region table are shown in Figure 40-15.
As you can see in Figure 40-15, a new record is being added, for the region of The Planet Mars. Obviously, the population is 0. The area is set to 1,000 just for fun. Some points need to be made here:
• The copied Region table has had a new record added in Access as soon as the record is entered and the focus of the Access interface is moved off the row.
• The new record is not present in the SQL Server database because the table resides within Access.
• There is no way to copy the new record into the SQL Server database. Refresh can only be performed by copying from SQL Server to Access once again, thus removing the new record because it is added in Access.
Figure 40-15
Adding a new record to an Access 2007 copied table
There is no refresh for imported tables in either direction.
Linked tables allow data refresh because the data is maintained in one place. The way that changes are sent back into SQL Server are as shown in Figure 40-16.
As shown in Figure 40-16, changes to tables linked to Access do not appear to be real-time refreshed into SQL Server. Changes are refreshed on command. This method actually makes sense with respect to the difference in the functionality and purposes of Access 2007, as compared with SQL Server 2005.
You could, of course, refresh the other way around and execute a command like this in the SQL Server database. Figure 40-17 shows a script executed in the query window of the SQL Server management studio tool, adding a new record, and then displaying it on the screen.
Figure 40-16
Access e-mails changes to SQL Server
Figure 40-17
Making changes directly into SQL Server tables
This is the script used in Figure 40-17:
use test
go
insert into region(region_id,region,population,area)
values(14,’The Planet Jupiter’,0,1000);
go
select * from region;
go
After running this short script and reopening the Access 2007 database, the linked table (dbo_Region) contains the record just added in SQL Server (see Figure 40-18).
In Figure 40-18, the imported table (Region) does not contain the new record added to SQL Server, because the copied table has not been recopied.
Figure 40-18
SQL Server changes are automatically reflected only in linked Access tables.
Views in SQL Server
Another object used in larger relational databases such as SQL Server and Oracle is called a view. A view is really a stored query that joins tables and filters data. Essentially, when you create a form in Access, you’re creating a query behind that form. A SQL Server view object creates a query which exists only as a chunk of SQL code. When a client application references the view, SQL Server executes the SQL statements, producing a table-like view of the data.
The result is that an Access query can be executed against a SQL Server view, as if the view were a table. When the view is accessed in a SELECT command, the records are retrieved from tables underlying the view and the records returned to the client. The view itself can include query command adjustments such as filtering and re-sorting.
Figure 40-19 shows the creation of a view object in SQL Server. This view object is a join of regions and countries, returning only the region and country names, all in alphabetical order by the name of the country, showing a resort of the data by jumbling up the regions. The query and result are both shown in Figure 40-19.
The code used for the view creation in Figure 40-19 is as follows:
use test
go
create view countries as
select r.region, c.country from region r join
country c on(c.region_id = r.region_id)
go
select * from countries order by country;
go
The view appears as any other SQL Server table when you ask Access 2007 to link to SQL Server data, as shown in Figure 40-20.
Figure 40-19
Creating a view in SQL Server
Figure 40-20
A view is the same as a table with respect to Access 2007.
Also, shown in the bottom-right corner is an extra window brought up by Access when linking to the SQL Server view, requesting a unique identifier (what amounts to a primary key), to identify each record. This is obviously the combination of, or composite of, the region and country names.
The unique identifier is needed so that, when data in the view is changed in Access, Access can tell SQL Server which row has been updated. Without a unique identifier, it would be impossible for Access to reliably update SQL Server data.
Stored procedures, functions, and triggers
Traditionally, a stored procedure is an addition to the relational database model, allowing for some processing inside a relational database. The original intention of stored procedures was as a chunk of SQL code acting solely on data in a database. In recent years, some relational databases have made stored procedure languages powerful enough that they can perform number-crunching processing and calculations, often performing tasks that have nothing to do with data stored in a database.
Stored procedures, functions, and triggers are not queries. A stored procedure is a block of SQL statements that are executed as a single entity.
One use of stored procedures is as a handy container for storing all of the SQL statements that you use throughout an application. Instead of writing SQL statements in your application code, you can store them in the database as stored procedures, calling them from your code in much the same way that you call a function stored in an Access module. Some of the many benefits of stored procedures are as follows:
• They can contain multiple SQL statements.
• They can call another stored procedure.
• They can receive parameters and return a value or a result set.
• They’re stored in a semi-compiled, interpretive state on the database server, so they execute faster than if they were embedded in your code. In other words, stored procedures are typically not compiled into a relational database as binary code, but they are usually pre-parsed, and partially pre-executed, making for faster execution.
• They’re stored in a common container in your application so that others can maintain them more easily because there is less database access code.
• After a stored procedure has been added to a SQL Server database, it is accessible to any client application using that database. This means that an Access 2007 desktop database application will execute the exact same logic as a Web application written with Visual Studio .NET, if they both use the same stored procedure to access data.
Some disadvantages of stored procedures are as follows:
• Overuse of stored procedures tends to place too much business logic into a database. This can sometimes make number-crunching-type business logic execute in a database very slowly. Some types of processing are best left to application coding, which is often much better suited to intense calculations.
• Overuse of stored procedures for data access can sometimes cause serious issues with network performance.
Let’s begin by creating a stored procedure in an Access .accdb file (not connected to SQL Server), and then execute that same stored procedure in Access. Follow these steps:
1. Open the Access .accdb database for this chapter.
2. Click the Create ribbon tab and select Query Design from the Other group on the Access ribbon.
3. When you see the Show Table dialog box, click Close because you’re going to create the query by writing SQL statements, not by using existing tables in the database.
4. At the top-left, click SQL View icon at the left side of the Access ribbon.
This gets you into the query text editor. Experiment by running a query such as this:
SELECT * FROM REGION;
5. Click the Run icon in the Results group at the left end of the Access ribbon.
6. Click the view icon at the top-left, and click the SQL View option.
This returns you to the text editor.
7. Type in the following CREATE PROCEDURE command:
CREATE PROCEDURE JumbledCountries () AS
SELECT r.region,c.country
FROM region r JOIN country c
ON(c.region_id=r.region_id);
Unfortunately, the preceding statement cannot be verified because Access 2007 refuses to recognize the CREATE PROCEDURE statement. A CREATE TABLE error is also produced when attempting to execute the preceding CREATE PROCEDURE statement.
Parameters can be passed to a stored procedure by including them in the procedure’s definition as follows:
CREATE PROCEDURE (<parameter> [,... ]) AS ...
An Access 2007 stored procedure can be executed using the EXECUTE command:
EXECUTE <procedure> [<parameter> [, ... ] ]
You cannot create a function or a trigger in Access 2007. You also cannot access SQL Server procedures, functions, or triggers from an Access 2007 .accdb, but they can be useful in SQL Server. (These SQL Server objects can be accessed from the .adp file format.)
Now let’s create the same two stored procedures in SQL Server, but directly in SQL Server, and see if we can execute them through a link with Access. This is the same procedure used previously, but this time it’s created in SQL Server:
use test
go
CREATE PROCEDURE JumbledCountries () AS
SELECT r.region,c.country FROM region r JOIN country c
ON(c.region_id=r.region_id);
go
A function is an expression because it returns a value, and an expression can be part of another expression. So, a function can call another function, and so on. The following is a function:
use test
go
CREATE FUNCTION Density(@population AS INT, @area AS INT)
RETURNS INT
AS
BEGIN
RETURN @population/@area;
END;
go
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), 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 fires after the data has changed.
Another important point about triggers is that they cannot contain transaction termination commands such as COMMIT and ROLLBACK, because other INSERT, UPDATE, DELETE commands, which are changing data, cause the trigger to execute. Allowing a transaction to terminate inside a trigger (triggered by a part of a calling transaction) is completely pointless because it negates the transactional aspect of the calling transaction. The biggest danger with triggers is that they can call themselves over and over, sometimes recursively, such that serious performance problems result with what can amount to an extremely large uncommitted transaction (a trigger can’t contain a COMMIT or ROLLBACK command).
Using triggers to implement and enforce referential integrity is not a recommended practice for reasons mentioned earlier.
Creating a trigger is very similar to a procedure or a function. This one creates an entry in a log file every time new product is added:
use test
go
CREATE TRIGGER LogEntries ON Products
FOR INSERT
INSERT INTO LogFile(id,event)
VALUES(<autocounter>,’New Product added’);
go
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 2007 the same capabilities are available as in previous versions of Access. Many features require you to use the Access 2000 .adp format instead of the Access 2007 .accdb file type.
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 stored procedures, views, and triggers. Also, the storage capacity of SQL Server installations is practically unlimited. Many SQL Server installations manage billions of records, making that data available to qualified client applications such as Access 2007.