SQL: Access to SQL Server

711 Pages·2002·25.61 MB·English

by  Susan Sales Harkins

SO-L: Access
to SO-L Server
SUSAN SALES HARKINS AND MARTINW.P. REID
APress Media, LLC
SQL: Access to SQL Server
Copyright ©2002 by Susan Sales Harkins and Martin W.P. Reid
Originally published by Apress in 2002
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN 978-1-893115-30-9 ISBN 978-1-4302-1573-8 (eBook)
DOI 10.1007/978-1-4302-1573-8
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Editorial Directors: Dan Appleman, Peter Blackburn, Gary Comell, Jason Gilmore,
Karen Watterson
Technical Reviewer: Russell Sindair
Managing Editor: Grace Wong
Project Manager: Doris Wong
Copy Editor: Tom Gillen
Production Editor: Kari Brooks
Compositor: Impressions Book and Journal Services, Inc.
Illustrator: Kurt Krames
Indexer: Valerle Robbins
Cover Designer: Tom Debolski
Marketing Manager: Stephanie Rodriguez
Phone [phone], fax: [phone], email info@apress .com, or visit http://www. apress .com.
The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation oftbis work, neither the author nor Apress shall
have any liability to any person or entity with respect to any lass or darnage caused or alleged to
be caused directly or indirectly by the information contained in this work.
To the child or grandchild who refuses to put me in a nursing home
and takes care ofm e in my old age.
-Susan Sales Harkins
For my wife Patricia-who made it all possible and who kept everything going
while I was at the keyboards-and for my children: Aine, Liam, Maeve, Emer,
Roisin, and our new addition as yet unborn. Finally to my parents,
William and Sarah Reid, who struggled for all of us.
-Martin W.P. Reid
Contents at a Glance
Foreward ............................................................ .x iii
About the Authors ..................................................x vii
About the Technical Reviewer ................................... .x viii
Acknowledgments .................................................... .x ix
Chapter 1: Structured Query Language .............................1
Chapter 2: An Introduction to Using Jet SQL in Access ......... 7
Chapter 3: An Introduction to Relational Database Theory .....3 5
Chapter 4: SQL Grammar ............................................. 69
Chapter 5: Retrieving Data ...................................... .. 83
Chapter 6: Modifying Data ........................................ 133
Chapter 7: Grouping and Summarizing Data .......................1 69
Chapter 8: Creating and Modifying Tables .......................1 99
Chapter 9: Manipulating Relationships and Indexes ............2 15
Chapter 10: Advanced Manipulation Techniques ..................2 33
Chapter 11: Queries: The Sequel ................................ .2 53
Chapter 12: An Introduction to the
SQL Server 2000 Desktop Engine .................. .. 279
Chapter 13: Introduction to SQL Server .........................3 07
Chapter 14: SQL Server Views .....................................3 71
Chapter 15: SQL Server Structure ................................3 97
V
Contents at a Glance
Chapter 16: Warking with Stored Procedures ................... .441
Chapter 17: Upsizing Directly from Access .................... .493
Chapter 18: Security Issues ......................................5 41
Chapter 19: SQL Server Meets the Web ...........................5 85
Appendix A: More SQL Salutions to Common Problems ............6 55
Appendix B: An Introduction to Using ADO
and ADOX wi th SQL ....................................6 67
Index ................................................................6 85
vi
Contents
Foreward .............................................................x zu
About the Authors ..................................................x vii
About the Technical Reviewer ....................................x viii
Acknowledgments .....................................................x ix
Chapter 1: Structured Query Language ...................1
Standards .............................................................2
The Many Dialects ....................................................3
Chapter 2: An Introduction
to Using Jet SQL in Access .................. 7
Building Access Queries .............................................8
How Access Uses SQL ................................................1 4
Debugging Tricks ....................................................2 9
Chapter 3: An Introduction
to Relational Database Theory .............3 5
The Rules of Normalization ........................................3 6
Creating Relationships ............................................ .44
Chapter 4: SQL Grammar .......................................6 9
Statement Structure ................................................ 70
Concatenating Litera! Values and Variables ..................... 78
vii
Contents
Chapter 5: Retrieving Data .................................8 3
The SELECT Statement ...............................................8 3
Using SELECT Statements in SQL Objects .........................1 08
Updateable Queries .................................................1 20
Chapter 6: Modi fying Data .................................1 33
Changing Existing Data with UPDATE ..............................1 34
Adding New Data with INSERT INTO ............................... .1 43
Removing Data with DELETE ........................................1 50
Creating New Tables with SELECT INTO ...........................1 62
Chapter 7: Grouping and Summarizing Data ...........1 69
Summarizing Data ...................................................1 70
Using GROUP BY to Create Groups .................................1 78
Creating Crosstab Queries ........................................1 92
Chapter 8: Creating and Modi fying Tables ...........1 99
Creating Tables ....................................................2 00
Modifying a Table' s Structure ....................................2 08
Chapter 9: Manipulating Relationships
and Indexes ..................................... .215
Enforcing Referential Integrity .................................2 16
About Jet Indexes ..................................................2 25
Chapter 10: Advanced Manipulation Techniques ......2 33
SQL-Specific Queries ..............................................2 33
Applying Security ..................................................2 42
Transactional Processing .........................................2 51
viii
Contents
Chapter 11: Queries: The Sequel .........................2 53
Creating and Using Subqueries ....................................2 53
Parameter Queries ..................................................2 66
Chapter 12: An Introduction to the
SQL Server 2000 Desktop Engine .........2 79
What Is the SQL Desktop Engine? .................................2 80
Who Should Use the SQL Desktop Version .........................2 84
New to SQL Desktop .................................................2 86
Di fferences between SQL Desktop and SQL Server 2000 ......... .287
Installing the SQL Desktop Version ..............................2 87
Getting Acquainted with the SQL Desktop Environment ..........2 93
Access 2002 and Security .........................................2 97
Forms in Access Data Projects ....................................3 01
Chapter 13: Introduction to SQL Server ..............
307
History of SQL Server .............................................3 08
SQL Server 2000 Editions .........................................3 09
Types of Installation .............................................3 11
Installing SQL Server 2000 .......................................3 12
Installing the Monster ............................................3 14
Introducing Enterprise Manager ..................................3 23
Database Diagrams ..................................................3 29
Viewing and Warking with Properties
in the Designer from Access XP ...............................3 45
Backing up Your Work ..............................................3 48
Database Properties ...............................................3 66
Improve Performance with Indexes ................................3 67
Chapter 14: SQL Server Views .............................
371
Warking with Views .................................................3 72
Advantages of Views ...............................................3 73
Indexing Views .....................................................3 83
ix
Contents
Chapter 15: SQL Server Structure .......................
397
Comparing Database and Server Properties .......................3 97
System Databases .................................................. .401
Comparing SQL Server and MS Access 2002 Data Types .......... .402
Transact SQL (T -SQL) ............................................. .404
SQL Server @@ System Functions ..................................4 25
SQL Server Query Analyzer ........................................4 35
Object Browser .....................................................4 37
Chapter 16: Warking wi th Stored Procedures ........
.441
What Is a Stored Procedure? ......................................4 41
Query Builder in an Access Data Project ........................4 45
Designing a Stored Procedure .................................... .446
Design Inline Function ........................................... .454
Defaul ts and Nulls ................................................ .463
Parameters and Forms ............................................. .465
Designing Views ....................................................4 73
Dealing with Errors .............................................. .482
SQL Server 2000 Stored Procedure Wizard ....................... .485
Chapter 17: Upsizing Directly from Access ..........
493
How It Can Fall Apart ............................................ .494
Jet Extended Properties ...........................................5 10
The Upsizing Wizard ...............................................5 11
Chapter 18: Securit y Issues ..............................
541
SQL Server Securit y Structure ....................................5 43
Manage Role, Membership and Other Object Permissions
wi thin the Database ............................................5 47
The Public Role ....................................................5 50
The Application Role ..............................................5 50
Consequences in the Access Data Project ........................5 56
Creating the Database Roles ......................................5 59
Viewing the Results in Access 2002 ..............................5 65
Creating the Clerical Role .......................................5 66
X