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

Lessons

•  00. Introduction (8:34) Free
•  01. Overview (13:35) Free
•  02. Setup WinHost Account (21:03) Free
•  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
•  Handbook PDF - AI Generated
• 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

Linked Table Security Notice

I've always maintained that using an Access database to connect to an SQL Server back-end will give you much better security than using Access alone (even with a split database setup). However, you should be aware that if you connect to SQL Server from Access using manually linked tables, then it is possible for a sufficiently experienced Access user to figure out the password for your server based on the linked table connections.

Colin Riddington recently posted an article explaining how the passwords for linked tables aren't masked (hidden) at all in the MSysObjects table, which maintains your database connection strings for linked tables. Hopefully Microsoft will one day fix this glaring security hole.

Update 2024-07-23: I've put together a TechHelp video on the Security Flaw in linked tables between Access and SQL Server. I also show how to correct it in Part 2 and the Extended Cut. 

Questions?

Please feel free to post your questions or comments below. If you have 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.