Welcome to the Access SQL Server Online Seminar. Total running time is 4 hours, 13 minutes.

Lessons

•  00. Introduction (8:34)
•  01. Overview (13:35)
•  02. Setup WinHost Account (21:03)
•  03. Connect Access to SQL Server (23:44)
•  04. Querying Data on the Server (32:19)
•  05. Relinking TableDefs, ADO (39:06)
•  06. Security Part 1 (28:41)
•  07. Security Part 2 (20:15)
•  08. SSMS Query, Alter, Backup (13:40)
•  09. Display Data on Web Using ASP (31:28)
•  10. Security Part 3 (14:22)
•  A1. Addendum 1 (6:02)
•  A2. Addendum 2 (29:12)

Course Files

•  Student Database Only * see notes below
•  Student Database After Addendum 2
•  Course ZIP File - Videos & Database (709 MB)

Notes

• If you download a copy of my database, remember that the password in the database file is for MY online SQL server which you can't have. You need to open the database with the Shift-key bypass and then enter your own credentials for your server in the code module for the MainMenuF. 

Learn More

• In Access Developer 33, Lesson 4, I show how to loop through all of the linked tables in your Access database and automatically reconnect to them if they need it. This uses the TableDefs collection and table attributes which I didn't really cover much of in the Seminar. 

Links

• Access Developer 1- VBA
• Access Developer 16 - Recordsets
• Access Security Seminar
• Access SQL Seminar Series
• Intro to VBA Programming (Free)
• WinHost Web/SQL Server Hosting
• Goodbye, GoDaddy
• SSMS (SQL Server Management Studio)
• SQL Server Migration Assistant (not recommended)
• Comparing Access SQL with T-SQL (Transact-SQL)
• Backing up Microsoft Access Databases
• EditPlus Web Page Editor
• Free ASP 101 Course (Active Server Pages)
• Complete ASP Course
• Access Runtime

Resources

• Full Course Description Page
• Skills Test Quiz - Under Construction
• Certificate of Completion
• Post-Class Survey

Related News

• Here's a great article by Juan Sato over at AccessExperts.com about Access and SQL Server including some tips for ensuring maximum compatibility. 

Addendums

• Addendum 1: if you have a yes/no value in Access, it will get changed to a bit value on the server. If you don't specify a default value, you won't be able to delete records. Also show how to use GETDATE() to provide default date/time at the server level.
   
• Addendum 2:
  • Moved passwords to a global module to make them easier to update
  • Added a TimerInterval change to the Main Menu On Load event
  • You will need the dbSeeChanges option if you use a Recordset to a linked table on SQL Server
  • More with SQL Server not liking Yes/No fields. Not only will you not be able to delete them as in Addendum 1, but they could potentially lock your whole table. So, I'll show you how to remove NULL values with an update query (instead of just deleting them like I did in Addendum 1), set the Allow Nulls property on the server, set the default value on the server and in your Access forms, and other stuff.
  • How to change text throughout the table with the REPLACE and CAST functions in SQL

Questions?

Please feel free to post your questions or comments below. If you have a questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.