Master database creation and management Access 2016 Bible is your, comprehensive reference to the world's most popular database management tool. With clear guidance toward everything from the basics to the advanced, this go-to reference helps you take advantage of everything Access 2016 has to offer. Whether you're new to Access or getting started with Access 2016, you'll find everything you need to know to create the database solution perfectly tailored to your needs, with expert guidance every step of the way. The companion website features all examples and databases used in the book, plus trial software and a special offer from Database Creations. Start from the beginning for a complete tutorial, or dip in and grab what you need when you need it.
Access enables database novices and programmers to store, organize, view, analyze, and share data, as well as build powerful, integrable, custom database solutions but databases can be complex, and difficult to navigate. This book helps you harness the power of the database with a solid understanding of their purpose, construction, and application.
- Understand database objects and design systems objects
- Build forms, create tables, manipulate datasheets, and add data validation
- Use Visual Basic automation and XML Data Access Page design
- Exchange data with other Office applications, including Word, Excel, and more
From database fundamentals and terminology to XML and Web services, this book has everything you need to maximize Access 2016 and build the database you need.
Table of Contents:
Introduction xxxix
Part I: Access Building Blocks 1
Chapter 1: An Introduction to Database Development 3
The Database Terminology of Access 3
Relational Databases 6
Access Database Objects 7
A Five-Step Design Method 10
Chapter 2: Getting Started with Access 19
The Access Welcome Screen 19
How to Create a Blank Desktop Database 21
The Access 2016 Interface 23
The Ribbon 26
The Quick Access toolbar 28
Part II: Understanding Access Tables 31
Chapter 3: Creating Access Tables 33
Table Types 33
Creating a New Table 34
Creating tblCustomers 51
Changing a Table Design 52
Understanding tblCustomers Field Properties 70
Setting the Primary Key 73
Indexing Access Tables 75
Printing a Table Design 81
Saving the Completed Table 83
Manipulating Tables 83
Adding Records to a Database Table 85
Understanding Attachment Fields 86
Chapter 4: Understanding Table Relationships 89
Building Bulletproof Databases 90
Data Normalization and Denormalization 92
Table Relationships 104
Integrity Rules 113
Keys 116
Chapter 5: Working with Access Tables 131
Understanding Datasheets 132
Looking at the Datasheet Window 134
Opening a Datasheet 138
Entering New Data 138
Navigating Records in a Datasheet 145
Changing Values in a Datasheet 148
Using the Undo Feature 150
Copying and Pasting Values 151
Replacing Values 152
Adding New Records 153
Deleting Records 153
Displaying Records 154
Sorting and Filtering Records in a Datasheet 163
Aggregating Data 168
Printing Records 169
Previewing Records 170
Chapter 6: Importing and Exporting Data 171
How Access Works with External Data 171
Options for Importing and Exporting 175
Importing External Data 177
Exporting to External Formats 200
Chapter 7: Linking to External Data 209
Linking External Data 210
Working with Linked Tables 224
Splitting a Database 228
Part III: Working with Access Queries 235
Chapter 8: Selecting Data with Queries 237
Introducing Queries 238
Creating a Query 240
Working with Query Fields 246
Adding Criteria to Your Queries 250
Printing a Query’s Recordset 254
Saving a Query 254
Creating Multi-Table Queries 254
Working with the Table Pane 259
Creating and Working with Query Joins 261
Chapter 9: Using Operators and Expressions in Access 267
Introducing Operators 267
Using Operators and Expressions in Queries 282
Entering Single-Value Field Criteria 288
Using Multiple Criteria in a Query 296
Entering Criteria in Multiple Fields 302
Chapter 10: Going Beyond Select Queries 309
Aggregate Queries 309
Action Queries 318
Crosstab Queries 333
Optimizing Query Performance 345
Part IV: Analyzing Data in Access 349
Chapter 11: Transforming Data in Access 351
Finding and Removing Duplicate Records 351
Common Transformation Tasks 359
Chapter 12: Working with Calculations and Dates 375
Using Calculations in Your Analyses 375
Using Dates in Your Analyses 388
Chapter 13: Performing Conditional Analyses 401
Using Parameter Queries 401
Using Conditional Functions 410
Chapter 14: The Fundamentals of Using SQL 421
Understanding Basic SQL 421
Getting Fancy with Advanced SQL Statements 426
Using SQL-Specific Queries 433
Chapter 15: Subqueries and Domain Aggregate Functions 441
Enhancing Your Analyses with Subqueries 442
Domain Aggregate Functions 455
Chapter 16: Running Descriptive Statistics in Access 467
Basic Descriptive Statistics 468
Advanced Descriptive Statistics 476
Part V: Working with Access Forms and Reports 483
Chapter 17: Creating Basic Access Forms 485
Formulating Forms 486
Working with Controls 498
Introducing Properties 517
Chapter 18: Working with Data on Access Forms 525
Using Form View 526
Changing Values in a Form 531
Printing a Form 538
Working with Form Properties 539
Adding a Form Header or Footer 550
Working with Section Properties 550
Changing the Layout 552
Converting a Form to a Report 556
Chapter 19: Working with Form Controls 557
Setting Control Properties 558
Creating a Calculated Control 565
Working with Subforms 565
Form Design Tips 567
Tackling Advanced Forms Techniques 570
Using the Tab Control 577
Using Dialog Boxes to Collect Information579
Designing a Form from Scratch 582
Chapter 20: Presenting Data with Access Reports 591
Introducing Reports 592
Creating a Report, from Beginning to End 596
Banded Report Design Concepts 614
Creating a Report from Scratch 620
Improving the Report’s Appearance 642
Chapter 21: Advanced Access Report Techniques 649
Grouping and Sorting Data 650
Formatting Data 662
Adding Data 680
Adding Even More Flexibility 682
Part VI: Access Programming Fundamentals 695
Chapter 22: Using Access Macros 697
An Introduction to Macros 698
Understanding Macro Security 702
Multi-Action Macros 706
Submacros 709
Conditions 713
Temporary Variables 716
Error Handling and Macro Debugging 721
Embedded Macros 727
Macros versus VBA Statements 729
Chapter 23: Using Access Data Macros 733
Introducing Data Macros 734
Understanding Table Events 735
Using the Macro Designer for Data Macros 738
Understanding the Action Catalog 740
Creating Your First Data Macro 743
Managing Macro Objects 747
Recognizing the Limitations of Data Macros 749
Chapter 24: Getting Started with Access VBA 751
Introducing Visual Basic for Applications 752
Understanding VBA Terminology 754
Starting with VBA Code Basics 755
Creating VBA Programs 755
Understanding VBA Branching Constructs 770
Working with Objects and Collections 780
Exploring the Visual Basic Editor 785
Chapter 25: Mastering VBA Data Types and Procedures 795
Using Variables 796
Working with Data Types 803
Understanding Subs and Functions824
Creating Functions 830
Simplifying Code with Named Arguments 836
Chapter 26: Understanding the Access Event Model 839
Programming Events 840
Identifying Common Events 843
Paying Attention to Event Sequence 852
Chapter 27: Debugging Your Access Applications 861
Organizing VBA Code 862
Testing Your Applications 863
Traditional Debugging Techniques 870
Using the Access Debugging Tools 876
Trapping Errors in Your Code 889
Part VII: Advanced Access Programming Techniques 895
Chapter 28: Accessing Data with VBA Code 897
Working with Data 897
Understanding ADO Objects 901
Understanding DAO Objects 914
Writing VBA Code to Update a Table 924
Chapter 29: Advanced Data Access with VBA 941
Adding an Unbound Combo Box to a Form to Find Data 941
Filtering a Form 949
Chapter 30: Customizing the Ribbon 959
The Ribbon Hierarchy 959
Editing the Default Ribbon 965
Working with the Quick Access Toolbar 968
Developing Custom Ribbons 971
Creating a Custom Ribbon 974
The Basic Ribbon XML 983
Adding Ribbon Controls 985
Attaching Ribbons to Forms and Reports 994
Removing the Ribbon Completely 996
Chapter 31: Preparing Your Access Application for Distribution 999
Defining the Current Database Options 1000
Developing the Application 1009
Polishing Your Application 1015
Bulletproofing an Application 1029
Securing the Environment 1040
Part VIII: Access and Windows SharePoint Services 1045
Chapter 32: Integrating Access with SharePoint 1047
Introducing SharePoint 1047
Understanding SharePoint Sites 1048
Sharing Data between Access and SharePoint 1051
Using SharePoint Templates 1060
Chapter 33: Deploying Access Applications to SharePoint 1063
Understanding Web Publishing with Access 1064
Examining the Limitations of Access Web Applications 1067
Publishing a Custom Access Application to SharePoint 1070
Index 1091
About the Author :
Michael Alexander is a Microsoft Certified Application Developer. For more than 15 years, he has been consulting and developing Microsoft Office solutions. Michael is a Microsoft MVP and runs a free tutorial website at www.datapigtechnologies.com. Dick Kusleika has been named a Microsoft MVP for 12 consecutive years. He develops Access- and Excel-based solutions for clients, and has worked with Microsoft Office for more than 20 years. Dick writes a popular Excel-related blog at www.dailydoseofexcel.com.
