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:
[ Ссылка ]
Presentation:
[ Ссылка ]
SQL Converter
[ Ссылка ]
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
[ Ссылка ]
SQL Server with Access playlist
[ Ссылка ]
SQL Server Academy: Introduction to SQL Server, by Juan Soto (54m29)
[ Ссылка ]
Access for Beginning Developers 4-part series, by Maria and crystal (strive4peace)
1. Access overview and shortcut keys (54m03)
[ Ссылка ]
2. Forms, Reports, Macros, QAT (52m16)
[ Ссылка ]
3. Queries (57m12)
[ Ссылка ]
4. VBA and Macros (1h00m27)
[ Ссылка ]
Edit Linked Excel files in Access, by Colin Riddington, isladogs (10m50)
[ Ссылка ]
Next!
Tuesday SEPTEMBER 27, 2022, at 12 noon Central time
Using Text-To_Speech TTS in Access, by David Nealey and Alessandro Grimaldi
[ Ссылка ]
AccessUserGroups.org
Lunchtime chapter host Maria Barnes
Last Tuesday @ noon Central time CT -- join us!
[ Ссылка ]
✓ Join our free online Access User Groups
