SQL Server Academy Part III with Juan Soto and Mark Edwards
[UPDATE] Download now available! https://accessusergroups.org/wp-conte...
Juan and Mark demonstrate and explain the steps in creating DSN-Less connections from MS Access to SQL Server. Presentation materials include managing linked views, (SQL Server queries are called views), with Access.
The download used in the presentation will be available 30 days after the presentation. Check back for a link on or after February 10, 2023.
Table of Contents:
00:00 - Introduction to AUG SQL Server Academy
06:23 - chatGPT AI
11:05 - Main Presentation Begins
11:07 - Intro Mark Edwards
12:05 - DSN-Less Connections
12:49 - Advantages & Disadvantages of DSNs
13:59 - Mark Edwards Demo of DSN-Less configuration and connection
14:44 - DSN-Less Connection SQL Server to Access
14:58 - Configuration Tables
16:05 - Connection Parameters in Config Table
16:14 - List of Linked Tables in Config Table
16:30 - Specify Indexed Column for Linked Views
16:58 - Program Options in Config Table
17:41 - Stored Pass-thru query w/connection
18:34 - Linked Views grouped with Local Tables
19:09 - Pass-Thru Queries
19:57 - One or More SQL statements in Passthrus
20:27 - Read Me module
21:53 - MSysObjects table showing linked tables
25:54 - Demo Ends. Code walk through begins
31:19 - Keep configuration table, tblLinkedTable, on the Server
32:39 - Link Tables in Multiple Databases
33:19 - Alias linked Access Table for continuity
34:49 - To be Editable, Views need a Primary Key
36:20 - Using Triggers on SQL Server tables
37:21 - Stored Program Options
37:59 - Q&A and Discussions
39:10 - Windows Auth & SQL Server Auth Code
40:46 - Users Should NOT Choose tables to Link
42:30 - Login Can Display only Username and PWD
43:52 - Mike Wolfe's No Longer Set Blog
44:10 - Search for Latest Installed ODBC Driver
44:45 - "Write Conflict" linked tables
44:52 - Every Table needs a Rowversion field
45:10 - AccessExperrts.com/Starthere
46:20 - Add a Timestamp/Rowversion Field
46:38 - SSMA adds Timestamp fields automatically
49:04 - PK field not selected during Linking
50:30 - Link to YouTube Video Juan Mentioned
51:00 - Using the Code in this presentation
51:43 - Tool Tip Showing Connection String
53:25 - Open and Cache a Connection
55:13 - Search for "Ben Clothier" on the blog
56:46 - Discussion: MSConf in SQL Server
58:49 - Executing Stored Procedures in Passthrus
1:03:02 - Final Comments and Q & A
1:09:33 - Next Session: SQL Server Security
1:10:02 - Provide Feedback to Juan