Microsoft Access Developer's Guide to SQL Server (Sams White Book)

Microsoft Access 2000 Developer's Guide to Microsoft SQL Server provides much needed information and guidance for the Access power user or developer who wants to exploit the power of SQL Server. Written by Access experts and Microsoft MVPs Mary Chipman and Andy Baron, this hands-on guide provides you with the practical knowledge you need to harness the enterprise-level power and scalability SQL Server offers, while using the Access tools you are familiar with. More than just a migration guide, you will benefit from the authors' expert discussions of topics including the new Microsoft Database Engine (MSDE), Transact SQL, building stored procedures and views, converting your applications to SQL Server, working with SQL Server security, and building Access interfaces to SQL Server databases. Booknews A guide for Access power users and developers who are ready to build database applications with SQL Server. Coverage includes converting an Access database to SQL Server, SQL Server fundamentals, SQL Server security, and linking to SQL Server using Access databases. Other subjects are Active X Objects, designing and creating an SQL Server database, programming effective stored procedures, architecture distributed -tier applications, and deployment, tuning, and maintenance. Chipman and Baron are consultants in the private sector. Annotation c. Book News, Inc., Portland, OR (booknews.com) Microsoft® Access Developer’s Guide to SQL Server......Page 2 Copyright © 2001 by Sams Publishing......Page 3 Overview......Page 4 Contents......Page 5 About the Authors......Page 18 Questions, Questions......Page 22 What You’ll Find in this Book......Page 23 CHAPTER 1 Converting Your Access Database to SQL Server......Page 26 Reasons to Upsize......Page 27 Getting Started with the Upsizing Wizard......Page 31 Upsizing Gotchas: What to Watch Out For......Page 40 What About Security?......Page 41 Using the Upsizing Wizard to Create Linked Tables......Page 42 Upsizing Using Data Transformation Services (DTS)......Page 50 Using Linked Servers for Data Access......Page 53 Beyond Upsizing: What’s Next?......Page 55 Summary......Page 57 CHAPTER 2 SQL Server Fundamentals......Page 60 SQL Server Editions......Page 61 SQL Server Graphical Components......Page 63 The Server......Page 78 The Database......Page 98 What’s New in SQL Server 2000......Page 105 Installing SQL Server......Page 107 Summary......Page 118 CHAPTER 3 Understanding SQL Server Security......Page 120 Why Security Is Important......Page 121 Letting Users In......Page 122 Understanding Permissions......Page 143 What About Access Security?......Page 149 Upsizing Access Security Permissions on Tables......Page 152 Summary......Page 160 CHAPTER 4 Introduction to Access Projects (ADPs)......Page 162 About Access Projects......Page 163 Creating a New Access Project......Page 166 Forms in Access Projects......Page 177 Managing Your SQL Server Through an Access Project......Page 197 Summary......Page 198 CHAPTER 5 Linking to SQL Server Using Access Databases (MDBs)......Page 200 How Jet Uses ODBC......Page 201 Working Efficiently with Linked Tables......Page 207 Creating Efficient Bound Forms......Page 217 Using Pass-Through Queries......Page 223 Caching Data in Local Tables......Page 229 Tweaking ODBC Settings to Tune Performance......Page 232 Summary......Page 237 CHAPTER 6 ActiveX Data Objects (ADO) Fundamentals......Page 240 A Short History of Data Access Technologies......Page 241 Getting Started with ADO......Page 242 Creating an ADO Connection Object......Page 247 Creating an ADO Recordset Object......Page 259 Creating an ADO Command Object......Page 271 Mixing and Matching the Three Objects......Page 277 Advanced ADO......Page 279 Other ADO Libraries......Page 287 Choosing the Best Tool for the Job at Hand......Page 291 Summary......Page 293 CHAPTER 7 Designing and Creating a SQL Server Database......Page 294 Designing a Relational Database......Page 295 Implementing Your Design in SQL Server 2000......Page 302 Summary......Page 339 CHAPTER 8 Introduction to Transact-SQL (T-SQL)......Page 340 Defining Transact-SQL......Page 341 Referring to Objects......Page 364 Selecting Data......Page 365 Joins......Page 377 Updating Data with Action Queries......Page 385 Using Transact-SQL Functions......Page 389 Programming in Transact-SQL......Page 399 Other Querying Techniques......Page 405 Summary......Page 414 CHAPTER 9 Creating and Optimizing Views......Page 416 Defining Views......Page 417 Creating and Modifying Views......Page 423 Using Views to Update Data......Page 444 Indexed Views......Page 449 Optimizing Views and Queries......Page 452 Summary......Page 462 CHAPTER 10 Programming Effective Stored Procedures......Page 464 Stored Procedure Fundamentals......Page 465 What’s Under the Hood......Page 477 Creating Parameterized Stored Procedures......Page 491 Error Handling in Stored Procedures......Page 496 Creating UPDATE, DELETE, and SELECT Stored Procedures......Page 502 Debugging Stored Procedures......Page 507 Advanced Stored Procedure Topics......Page 509 Stored Procedures and User-Defined Functions......Page 514 The System Stored Procedures......Page 515 Summary......Page 516 CHAPTER 11 Scaling Up with Unbound Access Applications......Page 518 What Is an Unbound Application and Why Build One?......Page 519 Using Stored Procedures......Page 520 Deciding Between Access Databases and Access Projects......Page 523 Unbound MDBs......Page 524 Unbound ADPs......Page 584 Summary......Page 612 CHAPTER 12 Building Access Reports from SQL Server Data......Page 614 General Guidelines for Creating Efficient Reports......Page 615 Using Access Features to Filter Data......Page 616 Reporting in Access Projects......Page 620 Reporting in Access Databases......Page 651 Reporting on OLAP Data......Page 658 Summary......Page 675 CHAPTER 13 Internet Features in Access and SQL Server......Page 676 Publishing SQL Server Data to the Web......Page 678 XML in SQL Server 2000......Page 685 Access Internet Features......Page 701 Summary......Page 713 CHAPTER 14 Architecting Distributed n-Tier Applications......Page 714 Overview of n-Tier Development......Page 715 Creating Middle-Tier Components......Page 748 Security and Connection Pooling......Page 757 Summary......Page 758 CHAPTER 15 Deployment, Tuning, and Maintenance......Page 760 Tuning......Page 761 Monitoring Performance......Page 762 Backup and Maintenance......Page 782 Deployment......Page 796 Summary......Page 812 APPENDIX A Resources......Page 814 Web Sites......Page 815 Useful Downloads......Page 816 Magazines and Journals......Page 817 INDEX......Page 818 A guide for Access power users and developers explains how to use the SQL server to configure MSDE, build stored procedures, convert applications, improve server security, and build Access interfaces.