AL: Using SQL Server with Access, by Maria Barnes

Maria Barnes speaks on “Using SQL Server With Access” and covers different ways of connecting to SQL Server from Access including Linked Tables, DSNLess Connections, Recordsets, Pass thru Queries, and Stored Procedures. Database: http://accessusergroups.org/lunch/wp-... Presentation: http://accessusergroups.org/lunch/wp-... SQL Converter https://accessusergroups.org/sql-conv... 7:02 linked tables are the most common way to link Access and SQL 7:36 benefits to use SSMA to help migrate tables from Access to SQL Server SSMA = SQL Server Migration Assistant 8:22 match SSMA bitness to office 10:43 SQL Datasets 11:08 Adrian explain why SQL Server uses 1 and 0 for Yes and No (it's in the bits) 11:26 SQL Server has a bit field 11:32 Access stores boolean in integer 11:47 every single bit a 1 in binary is minus 1 in decimal 12:00 every bit 0 is 0 in decimal 12:13 Some developers use an integer field. 12:37 link tables: External Data, New Data Source, From Database, From SQL Server 13:00 link doesn't store DSN name, stores DSN information. Hover over table to see connection string 13:30 Table Design - Description property 13:59 what the DSN file looks like? set up SQL Server directly through Get External Data interface as well. 17:10 look at linked tables in Access 17:26 SSMS SSMS = SQL Server Management Studio 18:38 DSNLess connection 19:31 VBA explicitely control connection 19:43 ADO connection accessible publically 20:03 open ADO connection in Form Open event 21:15 purpose for variable names to help protect code 22:00 obfuscate 22:37 Form that opens when database is opened, calls OpenC 23:11 data source (RecordSource) of form is a linked table 23:52 In form design, the RecordSource is blank. 24:06 Open event, make a call to routine called ADO_Form. Pass form itself and a Select statement 24:41 VBA for ADO_Form called on Open to populate a form with data. 24:54 SQL_Connection function 25:20 having an intermediate function is safer in case you lose internet 25:58 set form RecordSet to that recordset, close recordset in this function, and set to nothing. 26:30 what you can do with SQL datasets 26:56 call to stored procedure with parameters inline 27:06 VBA Form_Open SQL Select statement to run server-side 27:26 question about calling sp 27:36 commented line below shows calling a stored procedure 29:20 dbSeeChanges 30:02 question: does dbSeeChanges have adverse effects if the tables are in Access? 30:54 Queries 33:34 can see degradation running Access queries through linked tables. 33:58 Pass-through queries 35:15 error running server SQL when form opens 36:03 use SQL converter on AccessUserGroups.org 38:26 Adrian suggestion to use IN clause of SQL to connect to external database 39:05 example SQL using IN 43:45 Adrian example IN clause - end ' should be before ; 44:15 make pass-through query in VBA 44:17 VBA make a new querydef 46:41 Stored Procedures - SQL Command object 47:30 VBA CallSQLStoreProcedure 47:53 CommandText is stored procedure name 48:05 RETURN_VALUE parameter 50:25 Resources 54:31 George says Pass-Through Queries are read-only 55:05 a subform can fill with data first, problem if parent filter needed first 55:30 unbound subform, bind when parent form loads 56:52 AUG lunch videos have a 4-part series on basics of Access for beginning developers, links below 57:30 Recordsource is set via code 57:41 also use to load subforms on tabs 58:48 Returns Records property 59:26 updateable query vs action query 1:00:15 use linked table to be write-able 1:01:27 George, Colin's video Edit Linked Excel files in Access (link below) 1:01:38 Adrian, can update records using IN 1:01:57 Next meeting Text-to-speech, TTS Video links * Access Lunchtime playlist    • Access Lunchtime   SQL Server with Access playlist    • Access with SQL Server   SQL Server Academy: Introduction to SQL Server, by Juan Soto (54m29)    • SQL Server Academy--Introduction to SQL Se...   Access for Beginning Developers 4-part series, by Maria and crystal (strive4peace) 1. Access overview and shortcut keys (54m03)    • AL: Tips & Shortcuts for the Beginning Acc...   2. Forms, Reports, Macros, QAT (52m16)    • AL: Beginning Access Developer, Part 2–For...   3. Queries (57m12)    • AL: Part 3 - Queries! Tips & Tricks for th...   4. VBA and Macros (1h00m27)    • AL: Beginning Access Developer, Part 4 - V...   Edit Linked Excel files in Access, by Colin Riddington, isladogs (10m50)    • Edit Linked Excel files in Access   Next! Tuesday SEPTEMBER 27, 2022, at 12 noon Central time Using Text-To_Speech TTS in Access, by David Nealey and Alessandro Grimaldi https://accessusergroups.org/lunch/ev... AccessUserGroups.org Lunchtime chapter host Maria Barnes Last Tuesday @ noon Central time CT -- join us! https://accessusergroups.org/lunch/ ✓ Join our free online Access User Groups