AEU: Automating DSN-Less Connections to SQL Server (Peter Bryant)

This month’s session was led by Peter Bryant who is the owner of Corylus Business Systems
Web: https://corylus-business.co.uk
Email: [email protected]

Peter demonstrated code (and some data) to enable you to fire and forget application upgrades to different networks.
Just build and send the customer the ACCDE that works on your development network and have it automatically reconnect to their server and database on first run.
It works with more than one target SQL Database in the system. The user then just saves that new ACCDE to distribute to users.
The solution has been proven with many clients

DOWNLOADS:
https://www.isladogs.co.uk/aeu-15/
https://corylus-business.co.uk/april-2023-europe-access-user-group/

DONATE:
If you find this session / code useful, please consider giving a donation to the Help for Heroes charity supported by Peter: https://www.justgiving.com/fundraising/pjbryant2024

TIMES:
0:00 Welcome to this month’s speaker, Peter Bryant
0:36 Outline of meeting by Peter Bryant.
1:59 Assumptions & Infrastructure
5:15 Establish connections
5:59 Four tables used to control actions:
1: Version ID / Number – check if local/backend versions are identical – use to control schema changes
6:57 2: IclNetworks – list of valid domains & SQL Server instances
8:34 3: IclDatabaseNames – reconnection info & sort order – allows easy switching between instances
9:51 4: IclReconnectDSNLess – lists all tables in each database – manages table connections
10:10 Debug form features
10:58 Explanation of DEMO to follow
12:22 DEMO – shows how app checks version and database
14:17 Database switching made easy
15:57 Qu: Steve – do you check whether server is running? Wider discussion
19:31 DEMO part 2 – step through code
24:23 Refreshing table links in code
29:06 Handling/rebuilding indexes for SQL views so the view data can be updated
34:39 Same code also used for changing databases …
37:31 …or changing SQL Server instances
39:48 Qu: Neil S – returning string from fields collection? Wider discussion
45:27 Post Demo notes / recap
46:51 Final summary – deployment of this approach
47:45 Future plans
48:16 Potential issues and solutions
48:49 Possible variations
49:35 List of items for download
50:24 Help for Heroes – donate at https://www.justgiving.com/fundraising/pjbryant2024
50:55 Thanks to Peter for today’s session
52:16 Next month – change to original plan – Tom Van Stiphout will be discussing Northwind 2.0
53:10 Tom Van Stiphout outlined next month’s talk followed by discussion about localization
59:49 End Credits