Home Page Icon
Home Page
Table of Contents for
IV. Visual Basic for Applications
Close
IV. Visual Basic for Applications
by Steven Roman
Access Database Design & Programming, 3rd Edition
A Note Regarding Supplemental Files
Preface
Preface to the Third Edition
Preface to the Second Edition
The Book’s Audience
The Sample Code
Organization of This Book
Part I
Part II
Part III
Part IV
Part V
Part VI
Part VII
Part VIII
Conventions in This Book
Obtaining Updated Information
Using Code Examples
Request for Comments
Acknowledgments
I. Database Design
1. Introduction
1.1. Database Design
1.1.1. Why Use a Relational-Database Design?
1.1.1.1. Redundancy
1.1.1.2. Multiple-value problems
1.1.1.3. Update anomalies
1.1.1.4. Insertion anomalies
1.1.1.5. Deletion anomalies
1.1.2. Complications of Relational-Database Design
1.1.2.1. Avoiding data loss
1.1.2.2. Maintaining relational integrity
1.1.2.3. Creating views
1.1.3. Summary
1.2. Database Programming
2. The Entity-Relationship Model of a Database
2.1. What Is a Database?
2.2. Entities and Their Attributes
2.3. Keys and Superkeys
2.4. Relationships Between Entities
2.4.1. Types of Relationships
3. Implementing Entity-Relationship Models: Relational Databases
3.1. Implementing Entities
3.1.1. Implementing Entity Classes—Table Schemes
3.1.2. Implementing Entity Sets—Tables
3.2. A Short Glossary
3.3. Implementing the Relationships in a Relational Database
3.3.1. Implementing a One-to-Many Relationship—Foreign Keys
3.3.2. Implementing a One-to-One Relationship
3.3.3. Implementing a Many-to-Many Relationship—New Entity Classes
3.3.4. Referential Integrity
3.3.5. Cascading Updates and Cascading Deletions
3.4. The LIBRARY Relational Database
3.4.1. Setting Up the Relationships in Access
3.5. Index Files
3.5.1. Example
3.6. NULL Values
4. Database Design Principles
4.1. Redundancy
4.2. Normal Forms
4.3. First Normal Form
4.4. Functional Dependencies
4.5. Second Normal Form
4.6. Third Normal Form
4.7. Boyce-Codd Normal Form
4.8. Normalization
4.8.1. Decomposition
II. Database Queries
5. Query Languages and the Relational Algebra
5.1. Query Languages
5.2. Relational Algebra and Relational Calculus
5.3. Details of the Relational Algebra
5.3.1. Renaming
5.3.2. Union
5.3.3. Intersection
5.3.4. Difference
5.3.5. Cartesian Product
5.3.6. Projection
5.3.7. Selection
5.3.8. Joins
5.3.8.1. Equi-join
5.3.8.2. Natural join
5.3.8.3. θ-Join
5.3.9. Outer Joins
5.3.10. Implementing Joins in Microsoft Access
5.3.11. Semi-Joins
5.3.12. Other Relational Algebra Operations
5.3.13. Optimization
6. Access Structured Query Language (SQL)
6.1. Introduction to Access SQL
6.2. Access Query Design
6.3. Access Query Types
6.4. Why Use SQL?
6.5. Access SQL
6.5.1. Syntax Conventions
6.5.1.1. Notes
6.6. The DDL Component of Access SQL
6.6.1. The CREATE TABLE Statement
6.6.1.1. Column definition
6.6.1.2. Constraints
6.6.1.3. Notes
6.6.2. The ALTER TABLE Statement
6.6.2.1. Notes
6.6.3. The CREATE INDEX Statement
6.6.3.1. Note
6.6.4. The DROP Statement
6.6.4.1. Note
6.7. The DML Component of Access SQL
6.7.1. Updatable Queries
6.7.2. Joins
6.7.2.1. Inner joins
6.7.2.2. Outer joins
6.7.2.3. Nested joins
6.7.2.4. Self-joins
6.7.2.5. Notes
6.7.3. The SELECT Statement
6.7.3.1. Predicate
6.7.3.2. ReturnColumnDescription
6.7.3.3. FROM TableExpression
6.7.3.4. WHERE RowCondition
6.7.3.5. GROUP BY GroupByCriteria
6.7.3.6. HAVING GroupCriteria
6.7.3.7. ORDER BY OrderByCriteria
6.7.4. The UNION Statement
6.7.4.1. Example
6.7.4.2. Notes
6.7.5. The UPDATE Statement
6.7.5.1. Example
6.7.6. The DELETE Statement
6.7.7. The INSERT INTO Statement
6.7.7.1. Note
6.7.8. The SELECT...INTO Statement
6.7.8.1. Notes
6.7.9. TRANSFORM
6.7.10. Subqueries
6.7.10.1. Syntax 1
6.7.10.2. Syntax 2
6.7.10.3. Syntax 3
6.7.10.4. Notes
6.7.11. Parameters
III. Database Architecture
7. Database System Architecture
7.1. Why Program?
7.2. Database Systems
7.3. Database Management Systems
7.4. The Jet DBMS
7.5. Data Definition Languages
7.5.1. The Jet Data Definition Language
7.6. Data Manipulation Languages
7.6.1. The Jet Data Manipulation Language
7.7. Host Languages
7.8. The Client/Server Architecture
IV. Visual Basic for Applications
8. The Visual Basic Editor, Part I
8.1. The Project Window
8.1.1. Project Names
8.1.2. Project Contents
8.1.2.1. Standard modules
8.1.2.2. Class modules
8.2. The Properties Window
8.3. The Code Window
8.3.1. Procedure and Full-Module Views
8.3.2. The Object and Procedure Listboxes
8.4. The Immediate Window
8.5. Arranging Windows
8.5.1. Docking
9. The Visual Basic Editor, Part II
9.1. Navigating the IDE
9.1.1. General Navigation
9.1.1.1. Navigating the code window at design time
9.1.1.2. Tracing code
9.1.1.3. Bookmarks
9.2. Getting Help
9.3. Creating a Procedure
9.4. Run Mode, Break Mode, and Design Mode
9.5. Errors
9.5.1. Design-Time and Compile-Time Errors
9.5.2. Runtime Errors
9.5.3. Logical Errors
9.6. Debugging
9.6.1. Tracing
9.6.1.1. Step Into (F8 or choose Step Into from the Debug menu)
9.6.1.2. Step Over (Shift+F8 or choose Step Over from the Debug menu)
9.6.1.3. Step Out (Ctrl+Shift+F8 or choose Step Out from the Debug menu)
9.6.1.4. Run to Cursor (Ctrl+F8 or choose Run To Cursor from the Debug menu)
9.6.1.5. Set Next Statement (Ctrl+F9 or choose Set Next Statement from the Debug menu)
9.6.1.6. Breaking out of Debug mode
10. Variables, Data Types, and Constants
10.1. Comments
10.2. Line Continuation
10.3. Constants
10.3.1. Enums
10.4. Variables and Data Types
10.4.1. Variable Declaration
10.4.2. The Importance of Explicit Variable Declaration
10.4.2.1. Option Explicit
10.4.3. Numeric Data Types
10.4.4. Boolean Data Type
10.4.5. String Data Type
10.4.6. Date Data Type
10.4.7. Variant Data Type
10.4.8. Access Object Data Types
10.4.8.1. The generic As Object declaration
10.4.8.2. The Set statement
10.4.9. Arrays
10.4.9.1. The dimension of an array
10.4.9.2. Dynamic arrays
10.4.9.3. The UBound function
10.4.10. Variable Naming Conventions
10.4.11. Variable Scope
10.4.11.1. Procedure-level (local) variables
10.4.11.2. Module-level variables
10.4.12. Variable Lifetime
10.4.12.1. Static variables
10.4.13. Variable Initialization
10.5. VBA Operators
11. Functions and Subroutines
11.1. Calling Functions
11.2. Calling Subroutines
11.3. Parameters and Arguments
11.3.1. Optional Arguments
11.3.2. Named Arguments
11.3.3. ByRef Versus ByVal Parameters
11.4. Exiting a Procedure
11.5. Public and Private Procedures
11.6. Fully Qualified Procedure Names
12. Built-in Functions and Statements
12.1. The MsgBox Function
12.2. The InputBox Function
12.3. VBA String Functions
12.4. Miscellaneous Functions and Statements
12.4.1. The Is Functions
12.4.1.1. The IsDate function
12.4.1.2. The IsEmpty function
12.4.1.3. The IsNull function
12.4.1.4. The IsNumeric function
12.4.2. The Immediate If Function
12.4.3. The Switch Function
12.4.4. The Beep Statement
12.5. Handling Errors in Code
12.5.1. The On Error Goto Label Statement
12.5.2. Handling Errors in the Calling Procedure
12.5.3. The Calls Stack
12.5.4. The Error Object
12.5.5. The On Error GoTo 0 Statement
12.5.6. The On Error Resume Next Statement
12.5.7. The Resume Statement
13. Control Statements
13.1. The If ...Then Statement
13.2. The For Loop
13.3. The Exit For Statement
13.4. The For Each Loop
13.5. The Do Loop
13.6. The Select Case Statement
13.7. A Final Note on VBA
13.7.1. File-Related Functions
13.7.2. Date- and Time-Related Functions
13.7.3. The Format Function
V. Data Access Objects
14. Programming DAO: Overview
14.1. Objects
14.1.1. Object Variables
14.1.2. Object-Variable Naming Conventions
14.1.3. Referencing the Properties and Methods of an Object
14.2. The DAO Object Model
14.3. The Microsoft Access Object Model
14.4. Referencing Objects
14.4.1. Fully Qualified Object Names
14.4.2. Using Object Variables to Your Advantage
14.4.3. Default Collections
14.5. Collections Are Objects Too
14.5.1. Properties and Methods of Access Collections
14.5.2. Properties and Methods of DAO Collections
14.5.3. Properties and Methods of User-Defined Collections
14.5.4. Say It Again
14.5.5. Refreshing Certain Collections
14.6. The Properties Collection
14.6.1. The Virtues of Properties Collections
14.6.2. Types of Properties
14.6.2.1. Property: Inherited
14.6.2.2. Property: Name
14.6.2.3. Property: Type
14.6.2.4. Property: Value
14.6.3. User-Defined Properties
14.7. Closing DAO Objects
14.8. A Look at the DAO Objects
14.8.1. DBEngine Object
14.8.2. Errors
14.8.3. Workspaces
14.8.4. Users
14.8.5. Groups
14.8.6. Databases
14.8.7. TableDefs
14.8.8. QueryDefs
14.8.9. Recordsets
14.8.10. Relations
14.8.11. Containers
14.8.12. Documents
14.8.13. Fields
14.8.14. Parameters
14.8.15. Indexes
14.9. The CurrentDb Function
15. Programming DAO: Data Definition Language
15.1. Creating a Database
15.1.1. Notes
15.2. Opening a Database
15.3. Creating a Table and Its Fields
15.3.1. Notes
15.3.1.1. Note
15.3.2. Changing the Properties of an Existing Table or Field
15.4. Creating an Index
15.5. Creating a Relation
15.5.1. Notes
15.6. Creating a QueryDef
15.6.1. Notes
15.6.2. Running a Query
15.6.3. Properties of a QueryDef Object
16. Programming DAO: Data Manipulation Language
16.1. Recordset Objects
16.2. Opening a Recordset
16.2.1. Note
16.2.2. Default Recordset Types
16.3. Moving Through a Recordset
16.3.1. BOF and EOF
16.3.1.1. Notes on the BOF and EOF properties
16.3.1.2. Notes on the Move methods
16.4. Finding Records in a Recordset
16.4.1. Finding Records in a Table-Type Recordset
16.4.1.1. Notes
16.4.2. Finding Records in a Dynaset-Type or Snapshot-Type Recordset
16.5. Editing Data Using a Recordset
16.5.1. Editing an Existing Record
16.5.2. Deleting an Existing Record
16.5.2.1. Notes
16.5.3. Adding a New Record
16.5.3.1. Notes
VI. ActiveX Data Objects
17. ADO and OLE DB
17.1. What Is ADO?
17.2. Installing ADO
17.3. ADO and OLE DB
17.3.1. Data Stores
17.3.2. Data Providers
17.3.3. Data Consumers
17.3.4. Service Providers
17.4. The ADO Object Model
17.4.1. The Three-Pronged Approach to Data Manipulation
17.4.2. The Connection Object
17.4.2.1. Properties of the Connection object
17.4.2.2. Methods of the Connection object
17.4.3. The Recordset Object
17.4.3.1. Cursors
17.4.3.2. LockType
17.4.3.3. Properties of the Recordset object
17.4.3.4. Methods of the Recordset object
17.4.4. The Command Object
17.4.4.1. Command objects and connections
17.4.4.2. Properties of the Command object
17.4.4.3. Methods of the Command object
17.4.5. The Property Object and Dynamic Properties
17.4.6. The Field Object
17.4.6.1. Properties of the Field object
17.5. Finding OLE DB Providers
17.6. A Closer Look at Connection Strings
17.6.1. The Microsoft Jet 3.51 OLE DB Provider
17.6.2. The Microsoft OLE DB Provider for ODBC Drivers
17.6.2.1. Connecting to an Excel workbook
17.6.2.2. Connecting to a text file
17.6.2.3. ODBC support
17.7. An Example: Using ADO over the Web
18. ADOX: Jet Data Definition in ADO
18.1. The ADOX Object Model
18.1.1. Creating a Database
18.1.2. Creating Tables
18.1.3. The Tables Collection
18.1.4. Creating Indexes
18.1.5. Creating a Primary Key
18.1.6. Creating a Query
18.1.7. Conclusion
VII. Programming Problems
19. Some Common Data Manipulation Problems
19.1. Running Sums
19.1.1. Solution
19.2. Overlapping Intervals I
19.2.1. Solution
19.3. Overlapping Intervals II
19.3.1. Solution
19.4. Making Assignments with Default
19.4.1. Solution
19.5. Time to Completion I
19.5.1. Solution
19.6. Time to Completion II
19.6.1. Solution
19.7. Time to Completion III—A MaxMin Problem
19.7.1. Solution 1
19.7.2. Solution 2
19.8. Vertical to Horizontal
19.8.1. Solution
19.9. A Matching Problem
19.9.1. Solution
19.10. Equality of Sets
19.10.1. Solution
VIII. Appendixes
A. DAO 3.0/3.5 Collections, Properties, and Methods
A.1. DAO Classes
A.2. A Collection Object
A.2.1. Methods
A.2.2. Properties
A.2.3. Methods
A.3. Connection Object (DAO 3.5 Only)
A.3.1. Collections
A.3.2. Methods
A.3.3. Properties
A.4. Container Object
A.4.1. Collections
A.4.2. Properties
A.5. Database Object
A.5.1. Collections
A.5.2. Methods
A.5.3. Properties
A.6. DBEngine Object
A.6.1. Collections
A.6.2. Methods
A.6.3. Properties
A.7. Document Object
A.7.1. Methods
A.7.2. Properties
A.8. Error Object
A.8.1. Properties
A.9. Field Object
A.9.1. Collections
A.9.2. Methods
A.9.3. Properties
A.10. Group Object
A.10.1. Collections
A.10.2. Methods
A.10.3. Properties
A.11. Index Object
A.11.1. Collections
A.11.2. Methods
A.11.3. Properties
A.12. Parameter Object
A.12.1. Properties
A.13. Property Object
A.13.1. Properties
A.14. QueryDef Object
A.14.1. Collections
A.14.2. Methods
A.14.3. Properties
A.15. Recordset Object
A.15.1. Collections
A.15.2. Methods
A.15.3. Properties
A.16. Relation Object
A.16.1. Collections
A.16.2. Methods
A.16.3. Properties
A.17. TableDef Object
A.17.1. Collections
A.17.2. Methods
A.17.3. Properties
A.18. User Object
A.18.1. Collections
A.18.2. Methods
A.18.3. Properties
A.19. Workspace Object
A.19.1. Collections
A.19.2. Methods
A.19.3. Properties
B. The Quotient: An Additional Operation of the Relational Algebra
B.1. Step 1
B.2. Step 2
B.3. Step 3
C. Open Database Connectivity (ODBC)
C.1. Introduction
C.2. The ODBC Driver Manager
C.3. The ODBC Driver
C.3.1. Driver Types
C.4. Data Sources
C.4.1. DSNs and Data Source Types
C.4.1.1. Machine data sources
C.4.1.2. File data sources
C.4.2. Creating DSNs: The ODBC Administrator
C.4.3. Example DSNs
C.4.3.1. Excel system data source
C.4.3.2. Excel file data source
C.4.3.3. Text-system data source
C.4.4. Connecting to a Data Source
C.4.5. The SQLConnect Function
C.4.6. Connection Strings
C.4.7. SQLDriverConnect
C.5. Getting ODBC Driver Help
C.6. Getting ODBC Information Using Visual Basic
C.6.1. Preliminaries
C.6.2. Getting Driver Information
C.6.3. Getting Data Sources
D. Obtaining or Creating the Sample Database
D.1. Creating the Database
D.2. Creating the BOOKS Table
D.3. Creating the AUTHORS Table
D.4. Creating the PUBLISHERS Table
D.5. Creating the BOOK/AUTHOR Table
D.6. Backing Up the Database
D.7. Entering and Running the Sample Programs
E. Suggestions for Further Reading
Index
Colophon
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
7. Database System Architecture
Next
Next Chapter
8. The Visual Basic Editor, Part I
Part IV. Visual Basic for Applications
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset