ebook img

SQL: Access to SQL Server PDF

711 Pages·2002·25.61 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview SQL: Access to SQL Server

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 510-549-5930, fax: 510-549-5939, 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

Description:
SQL: Access to SQL Server is more than just a comprehensive reference tool for Access developers. You'll also find efficient SQL solutions for common Access problems and tasks, as well as helpful hints and warnings about what to avoid. Learning Jet SQL is an important step from just using Access to
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.