Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Microsoft Access SQL Seminar, Part 3
 
Learn how to supercharge your databases using SQL

 

 

This two-hour long video seminar will pick up where Part 2 left off. You will learn more about the SQL programming language. We will focus on manipulating the structure of your Access database - building and modifying tables and queries with SQL commands. The techniques in this seminar are especially useful for the administrator or developer who has to support remote database files. You can now update them (add a table, change a field, etc.) with simple SQL commands.

Learn More...

Click on the video image to the right to learn more about exactly what's covered in this seminar. The video is about 5 minutes long and fully explains everything that's discussed. Then, continue reading the course outline below if you have further questions.

AccessLearningZone.com
  5 minutes
 
Seminars - Access SQL Part 3
Description: Learn the SQL programming language to enhance your Microsoft Access database projects. Part 3 focuses on table and query design.
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
IMPORTANT: Lessons 7 to 10 will NOT work with Access 2000 or earlier.
Pre-Requisites: Access SQL Seminar, Part 2
Running Time: 2 hours, 13 minutes
Cost: $109 - Order multiple courses to receive a discount up to 50% off

 

This seminar has several goals. You will:

1. Learn how to create a modify a Table
2. Discover the different Access SQL Data Types
3. Create Indexes for your tables
4. Connect to external databases using SQL
5. Construct a CONSTRAINT clause
6. Work with table relationships

7. Learn the differences between ANSI-89 and ANSI-92 SQL
8. Set up Referential Integrity between your tables
9. Create stored views and procedures
10. Learn about Access database security via SQL

We will begin by learning how to create a TABLE in SQL. We'll talk about why it's important (and powerful) to be able to do this. We'll create a form to launch our SQL commands via VBA. We'll also see how to delete (drop) a table.

 

Next we will learn about all of the different Access SQL data types such as Text, Memo, Byte, Integer, and so on. You'll see how most of them are the same as regular Access, but a few things have changed.

 

Next we'll learn how to set up Indexes with SQL. You'll create a PRIMARY KEY field, learn how to force unique values (indexed, no duplicates), and how to ignore or disallow NULL values.

 

 

You will learn how to ALTER the structure of your tables once they're built. This is great for modifying existing databases. You can add or delete columns (fields), change some of their properties, and I'll even show you how to connect to external databases.

 

You will learn how to use a CONSTRAINT so that you can name a specific property of your table. This allows you to easily modify it later. You'll also learn how to create a multi-field index spanning two or more fields (and exactly what that is used for).

 

Next we'll learn how to set up relationships between two tables. You'll use a constraint to create a FOREIGN KEY field.

 

 

Next, we'll learn how to switch our Access databases over from ANSI-89 to ANSI-92 to unlock some additional new SQL features. We'll learn about the pros and cons of each version, and what some of the changes are. If you're using Access 2000 or earlier, a few of these lessons won't work for you because Access 2000 only supports ANSI-89. Sorry.

 

You will learn how to create a CHECK constraint (essentially a validation rule) and DEFAULT value for fields. You'll also learn about multi-field validation rules.

 

You will learn about Referential Integrity and how to set up the CASCADE UPDATE and CASCADE DELETE properties.

 

You'll learn how to create a VIEW and PROCEDURE, which are essentially queries in Access... but you'll learn about why they're important to know if you ever start working with and SQL server program.

 

Finally, we'll talk about Access security and SQL. You'll learn how to create users and groups, and how to assign various permissions to them for the objects in your database.

 

Again, this seminar is perfect for anyone who wants to learn how to supercharge their Microsoft Access databases with the added power of custom SQL. It is the third part of a three-part series. Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access SQL Seminar 3 Outline

00. Intro (5:02)

01. Creating a Table (14:32)
Importance of SQL table design
CREATE TABLE command
TEXT field
Length of a TEXT field
Multiple Fields
Make a text box to hold our SQL
Bind SQL form to table
DROP TABLE

02. Access SQL Data Types (8:30)
Access to SQL Aliases
TEXT
MEMO
BYTE
SMALLINT
INTEGER
SINGLE
DOUBLE
CURRENCY
COUNTER
DATETIME
YESNO
OLEOBJECT
Hyperlink
Attachment
Lookup
Start COUNTER at different value
Inflate AutoNumber value
SQL view in query designer

03. Indexing (14:32)
NULL
NOT NULL
PRIMARY KEY
UNIQUE
CREATE INDEX
CREATE UNIQUE INDEX
DROP INDEX
WITH DISALLOW NULL
WITH IGNORE NULL
WITH PRIMARY
ASC, DESC

04. Altering Tables (16:12)
ALTER TABLE
ADD COLUMN
ALTER COLUMN
DROP COLUMN
ALTER COLUMN NOT NULL
Altering tables in EXTERNAL databases
Cannot alter LINKED tables
Dim DB as database
OpenDatabase command
DB.Execute
Adding tables to external database
CREATE link to external table

05. Constraints (10:49)
CONSTRAINT
PRIMARY KEY
DROP CONSTRAINT
ADD CONSTRAINT
UNIQUE
Multi Field Index
Index spanning 2 or more fields

06. Relationships (9:37)
Relationships review
CONSTRAINT FOREIGN KEY
Relationships Window

07. ANSI-92 SQL (8:32)
Differences between 89 and 92
Wildcard characters
Switch database from 89 to 92
Broken queries
LIKE to ALIKE

08. CHECK & DEFAULT (11:24)
CHECK
CONSTRAINT CHECK
Multi Field CHECK Constraint
Table-Level Validation Rules
DEFAULT value
ALTER SET DEFAULT
ALTER DROP DEFAULT

09. Referential Integrity (6:16)
ON UPDATE CASCADE
ON DELETE CASCADE

10. VIEWS & PROCEDURES (11:08)
CREATE VIEW
Views vs. Queries
DROP VIEW
Parameters
CREATE PROCEDURE
Action Queries
Append, Delete, Update
EXECUTE procedure
DROP procedure

11. Database Security (11:44)
ALTER DATABASE PASSWORD
Open Exclusive Mode
GRANT, REVOKE
USER, GROUP
CREATE, ALTER, DROP
Privileges
SELECT, DELETE, INSERT, UPDATE
DROP, CREATE
SELECTSCHEMA, SCHEMA
UPDATEOWNER, CONNECT

12. Review (5:01)

 


 

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP