Using Office Applications with SQL Server Express
by Michael Otey, [email protected]
SQL Server 2005 Express is probably best known as a database for ISV and hobbyist application developers. However, you don’t need to be a programmer to take advantage of SQL Server Express. You can use SQL Server Express as a back-end database and couple it with many front-end applications that use ODBC, OLE DB, or SQL Server .NET data providers. ODBC and OLE DB are mature data-access technologies, and of the many ODBC- or OLE DB-compatible applications, the most widely used are Microsoft Office applications--Word, Excel, and Access. Because these applications are ODBC- and OLE DB-enabled applications, they can seamlessly connect to SQL Server and SQL Server Express databases. Thus, you can incorporate the data in your SQL Server Express databases into your Office applications. For example, you could maintain a list of contacts, employees, or customers in a SQL Server Express database and use Word’s Mail Merge feature to create a mailing list with a targeted list of customers. The Mail Merge feature lets you apply Word's text, font, graphics, and logo capabilities to the data from your SQL Server Express database. You can take full advantage of Word’s advanced formatting features to produce professional-looking letters and SQL Server Express's ability to store and retrieve related data.
Likewise, Excel’s integration with ODBC and OLE DB lets you use Excel as a data-analysis front end for data in a SQL Server Express database. Excel is a widely used front end for many data sources and provides easy, flexible database connections. In Excel, you can use direct table imports to copy a table from a database into a worksheet, then use Excel to modify the data without affecting the source data in SQL Server Express. You can also use Microsoft Query to build complex, multiple table joins in which you can perform row selection and filter data, then return the results to an Excel workbook. Or you can link Excel Pivot Tables to ODBC or OLE DB data sources (e.g., SQL Server) to obtain more sophisticated data analysis.
Although Access is best known for its standalone database capabilities, you can also use it as a front end for SQL Server Express. In fact, for beginner programmers, Access is an effective tool for creating data-entry forms, reports, and queries. Or you can use Access as a front end and use ODBC or OLE DB to connect to other relational database platforms such as SQL Server and SQL Server Express. You can use Access linked tables or create an Access Project to connect to SQL Server Express.
Linked tables are an older technology that are best suited for situations in which you have an existing Access database and you want to incorporate information from your SQL Server Express database. When you create a linked table, you use ODBC to connect to a table on a target database. Then, you can use these linked tables like an Access table; you can open them and include them in queries and reports. Your logon privileges will govern the data-access capabilities you can use to connect to a SQL Server Express database. Using Microsoft Access Projects (ADP) as a front end is a great way to manage SQL Server 7.0 and SQL Server Express databases. ADP lets Access act as a front end to access a target database; the data is stored in SQL Server Express, not in Access. However, you can use all the Access tools, forms, and reports to edit and query the data in the SQL Server database. Any new database objects that you create by using ADP will be created on SQL Server Express.
By Michael Otey
In my last Jump Start column (May 1, 2006, http://www.sqlmag.com/Article/ArticleID/50173/sql_server_50173.html ), I described how to create and execute a T-SQL script by using the command-line SQLCMD tool or the Query Editor in SQL Server Management Studio Express (SSMSE).
Using T-SQL scripts to create databases, tables, and database objects has some useful side benefits. When you run a T-SQL script, it creates the required objects and a text file that documents the database structure that you’re using, and you can rerun the script if you need to recreate the database for testing or recovery purposes. However, to make a T-SQL script rerunable, you need to add to the script some code that checks for the existence of the database object you want to create--your script will generate an error message if you attempt to create an object that already exists. The following T-SQL script shows the code required to check for a pre-existing table named Media.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Media' AND type = 'U')
DROP TABLE Media
CREATE TABLE \[dbo\].\[Media\](
\[MediaID\] \[int\] NOT NULL,
\[MediaType\] \[varchar\](10) NOT NULL,
\[MediaTitle\] \[varchar\](50) NOT NULL,
\[LastUpdated\] \[datetime\] NOT NULL
) ON \[PRIMARY\]
IF EXISTS is essentially a true-or-false test that checks the results of the embedded SELECT statement. This SELECT statement queries a special system table called sysobjects that lists all of the system objects. The query returns results for objects named Media with the type "U" (for user table). If the IF EXISTS test is true, the DROP TABLE command will be executed--deleting the existing table before the CREATE TABLE command is executed and allowing the script to be rerun.
SQL Server 2005 BOL Includes a T-SQL Reference
By Michael Otey
If you’re getting started with SQL Server 2005 Express, or even if you’re a veteran user, you'll find the SQL Server Express Books Online (BOL) online reference indispensable. BOL contains documentation, installation instructions, security guides, and tutorials, and truly is an essential resource for everyone working with SQL Server 2005 Express. You can download SQL Server Express BOL and samples from:
However, one essential thing that you won’t find in SQL Server Express BOL is a T-SQL reference. I guess Microsoft figures that knowing how to use T-SQL isn't an essential part of working with SQL Server Express, but if you try to develop a table yourself, you’ll soon see otherwise. You can download the full SQL Server 2005 BOL, which includes the T-SQL reference, from:
Introductory Guide to Learning SQL
by Blake Eno
O'Reilly announced "Learning SQL on SQL Server 2005", an introduction to the SQL language. You'll first learn simple SQL commands such as how and where to type a SQL query, and how to create, populate, alter, and delete tables. This book also covers joins, query development, subqueries, aggregate functions, and correlated subqueries. Each topic, concept, and idea comes with examples of code and output, along with exercises. "Learning SQL on SQL Server 2005" costs $44.99. For more information, contact O'Reilly at 707-827-7000 or 800-998-9938.