Access 2007-2016
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  
 
NEW Release: Access Developer Level 3   dismiss
 
 

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)

 


 

 
 

Student Interaction: Access SQL Seminar Part 3

Richard on 10/21/2011:  This two-hour long seminar on Microsoft Access SQL picks up where PART 2 left off. You will learn how to manipulate the structure of your databases using simple SQL commands. You'll be able to build and modify tables, queries, indexes, relationships, and much more. These techniques are especially powerful for developers who have to support remote, back-end database files. 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 Click here for more information on the ACCESS SQL SEMINAR, PART 3.
Mubeezi Micah on 10/21/2011: Looks great! i will be the first one to buy it!

MICAH

Alex Hedley on 10/21/2011: Another fantastic course. :)
Great use of the SQL form.
Brilliant end to the series, loved the bonus tabledef part.

The VB course looks intriguing, hopefully you'll find some time to do it soon!

Reply from Richard Rost:

Wow... that was fast. :) Glad you enjoyed it.

Yes, there's a whole TON of cool stuff you can do with tabledefs (and DAO in general).

Mubeezi Micah on 10/22/2011: Wow! What a seminar! You made manipulating tables look so EASY...even when dealing with a remote database...Thank you Richard and keep it up. I really look forward to the VB and DAO seminars.

MICAH

Mubeezi Micah on 11/26/2011: Dear Richard,

I am failing to find the sample database files for SQL part 3. I tried the link above but didn't find it. Please advice me where i can find them.
Thank you!
MICAH

Reply from Richard Rost:

Part 3 didn't have a sample database. We didn't really build anything complex in class. If you really want it I can try to find mine - but I don't think you'd need it.

 Chris on 3/3/2012: In Altering Tables, you are modifying a remote table (customerT) in the db2 file. My question is: Does the file db2 file need to be running during the modification? (I see you give it the full pathname and filename to the file)

Reply from Richard Rost:

What do you mean by "running?" As long as you have access to the table, and it's not currently LOCKED by another user, then you should be able to modify it.

Chris on 3/5/2012: I was asking does the db that we are remotely modifying (which is an access application file) have to be running during the remote changes or is it enough that we have provided the full path name of the db for your code to access the file to modify it?.

I suppose I should just do it my self and answer my own question.

Reply from Richard Rost:

The BEST way to answer ANY question it to try it yourself. It's always AWESOME to answer your own questions. But... you shouldn't need to have the database open in Access in order to modify it. In fact, it's probably better that you don't.

David R on 6/19/2012: Good evening Richard,
I was in the process of looking seminar 3 for SQL. There are two questions if you can please help me with. I am having a issue with the enter key behavior with in the properites. For some reason I am able to press enter and cursor goes to the next line. After copying and pasting CREATE TABLE TempT "(FirstName TEXT (20), LastName TEXT (20)); " into the text box and for some reason I am not able to get the command to work. The error message I am getting the data type is not matching. Can you please me both scenarios.

Thank you

Reply from Richard Rost:

Have you followed the steps in the video EXACTLY? What is the EXACT error message you get?

Mubeezi Micah on 7/11/2012: Dear Richard,

Strange but true!

I just noticed something at Time Index 5:20 in Video 4.
If i write an SQL command to say ALTER TABLE TempT ALTER COLUMN MiddleName CHAR(50).
It alters the table as expected. But it also alters the existing data!
I somehow noticed that i get extra spaces infront of the existing data. The total length of the records in this field now comes to 50! Kind of strange but its true.
To fix it, you have to either use the RTrim function or to say ALTER TABLE TempT ALTER COLUMN MiddleName TEXT. This would set it to the default 255 characters.

Give it a shot, you will be surprised.

MICAH

Reply from Richard Rost:

That's very strange. I haven't tested it myself (I will when I get some time) but ALWAYS be careful when updating table structure. I have also seen some weird things like this happen before.

Lone Vistoft on 6/17/2013: You say in this seminar, that there will soon be a SQL Server seminar. Are you able to provide information on when this will happen?
Kind regards Lone

Reply from Richard Rost:

I don't have a date for it, but it's going to be soon. I need to finish up the Office 2013 lessons I have planned first, then I'll be working on several different seminars. Check the production schedule for more information.

Ronan on 6/17/2013: Count me in too...
Bradley Biles on 10/12/2013: Hi Richard,

I know this is probably way past the time from when you created this seminar, but on the topic of Constraints when two fields are involved, my current project will have this situation and will be a required by the Nevada Gaming Commission. We cannot have to gaming machines that have the same Model Number and the same Serial Number. This Constraint is perfect for my InventoryT table & entry form to stay in compliance and double check the manufacturer.

Brad

Reply from Richard Rost:

You can set up indexes that are based on two keys, so you can't have duplicates that are a combination of BOTH fields. This is called a COMPOSITE KEY.

Brad on 10/13/2013: Thank you Richard for the tip on Composite Keys. That will do the trick!!

I just have not reviewed all of the Tip videos yet.

Brad

Zied Ben Afia on 2/3/2014: Hi Richard !
is any DAO seminar coming soon?
If yes please let us know when exactly

Tom Dlugosh on 2/25/2015: Is the create/delete tmp table the appropriate way to copy records, manipulate some of the fields and copy the revised data back into the original table as new data or is there an easier way?

Reply from Alex Hedley:

You could do it a record at a time with an UPDATE query.
Just depends exactly what you want to do and how may changes you need to make.

Kenny Nelson on 10/7/2015: I LOVE THE MYSQL FORM! What a great idea! I am actually using it to create a VBA code library. I added a description field to my MySQLT where I can put a key phrase such as Error Handling, and I store my error handling routine in the MySQL field. Whenever I need some code, I know right where to find it without having to start from scratch!
Kenny Nelson on 10/12/2015: I am very interested in the SQL Server class you mentioned. Can you give an estimated time when that will be ready?

Reply from Alex Hedley:

There's a small snippet on SQL Server in Expert 24.

 

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