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

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