|
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)
|
