Access DevCon 2021: Day 2

Today was the second day of the annual Access DevCon Vienna conference.  Due to COVID-19, the conference was held virtually.  Here's a quick recap of the day's speakers.

twinBASIC: The New Kid on the Block

I started the day off with my presentation on twinBASIC (slides).  If you've been following my blog, you're probably ready for me to move on to other topics by now.  If you've been reading here AND attended the conference, you probably noticed a suspiciously high level of overlap between the screenshots in the presentation and the screenshots in this series of articles I wrote recently.  This was no accident, as you might imagine.

The response seemed pretty positive, with the overwhelming question being, "How does this help Access developers?  Where does it fit into the Access ecosystem?"

The short-term answer is that twinBASIC will provide a simple copy-and-paste option to move VBA code into external libraries where you can take advantage of some of these twinBASIC benefits:

• expanded syntax
• multi-threading
• native compilation
• full Unicode support

The long-term answer is that I hope one day it will replace VBA as the Office programming language. And to go with it–maybe more importantly–a VS Code-like variant of the Monaco editor will replace the current development environment.

Selling Access Solutions

The venerable Juan Soto spoke next about the business side of Microsoft Access development.  He shared his "fifteen-minute SEO tip," which boils down to this:

Include your city and contact phone number in the title of your home page.

Why do this?  Because your website title is what Google displays in its search results.  And for many clients, they won't even click the link to your web page...they'll just dial the phone number you so conveniently displayed for them:

How you actually set your web site's title will depend on what software you are using to host your site.  But in terms of the HTML, you simply set the <title> tag to whatever you want to appear in the search results:

One great piece of advice Juan gave was to demo your past work with other clients.  The point he made–and it's an excellent one–is that there is a lot of crappy Access software out there.  Many decision-makers have only ever seen the Access database that Ned in accounting cobbled together that no one else can touch lest the whole thing collapse under its own weight.  The best way to show them that you're different–and that there's nothing inherently bad about Access itself–is to show them examples of very not-crappy Access software.

Team building

Juan also emphasized the importance of building a team to maximize the amount of value you can create for others and–not incidentally–for yourself as the business owner.  He advocated for you as business owner of a consultancy to act as the face of the business and lead salesperson.  

For further reading on this topic, I wholeheartedly recommend the E-Myth Revisited, by Michael Gerber, and Built To Sell, by John Warrilow.  The first two-thirds of the E-Myth should be required reading for new entrepreneurs (the last part about "The Game" I found unconvincing and, frankly, a bit odd, but maybe I haven't grown my company to the size where that advice matters yet).  Built to Sell is all about setting up systems so that your business can survive without you.  Whether you actually want to sell your business is largely irrelevant; having a business that can run without your constant intervention is a great way to lower your stress (or so I hope...I'm not there yet 😉).

Tuning an Access/SQL Azure Hybrid App

Kevin Bell presented next and offered several tips for what is sure to be a growth market in the Access development space: Microsoft Access front-end applications with data stored in the cloud via SQL Azure.  Kevin noted that when SQL Azure first debuted, it did so with a subset of the features available via on-premises SQL Server.  He said those differences have mostly disappeared.  This greatly simplifies the migration from on-premises SQL Server to SQL Azure (cloud-based SQL Server).

The migration is not without pitfalls, however.  Most notably, developing with SQL Azure data requires being more keenly aware of how much data Access is actually requesting and moving across the network.  Kevin offered three rules of thumb to maximize performance (these are completely from memory, so my apologies to Kevin if I got something wrong):

• Request only the data you need;
• Only when you need it; and
• Process as much as possible on the server

He then demonstrated the dreadful performance you might see if you migrate from on-prem SQL Server to cloud-based SQL Azure by simply relinking your tables.  Data operations that appear to happen instantaneously over a local network–such as populating a handful of combo boxes on a form–could suddenly take dozens of seconds when that same form is transferring data from the cloud.

Kevin then presented a handful of techniques for minimizing these data transfer delays:

• Linking to SQL Server views instead of tables
• Linking to table-valued functions (TVFs)
• Executing stored procedures
• Using local temporary working tables

Kevin also highlighted the importance of choosing appropriate data centers, as the latency can vary widely based on the users' geographic proximity to those data centers.  He recommended running an Azure Speed Test from the users' location(s) to decide which data center will provide the best performance.

Advanced Graphical Interfaces with Access

Alessandro Grimaldi was the surprise hit of Access DevCon 2021.  A fellow newcomer to the Access conference scene, Alessandro blew the audience away with a dizzying array of ... well ... advanced graphical interfaces.

Here's a small sampling of some of the techniques he presented:

• Drag-and-drop controls
• Horizontal sliders used to set start and end times
• "Accordion" forms where the user can resize a group of side-by-side subforms by clicking and dragging a common dividing line
• A rotating photo gallery
• A map picker that looked like something out of a GIS system but was somehow written entirely in VBA
• An interactive pirate map, for crying out loud
• And so many more

I feel bad even writing the above descriptions of Alessandro's work because I can't possibly do it justice.  He started a YouTube channel recently (it's gained 37 subscribers since his talk...more than a third of all the live attendees). I recommend you subscribe for when he starts posting more of his advanced techniques, like this one on creating right-click popup menus.

Timetabling made easy

Alessandro also demonstrated his MEETER application.  This was a meeting scheduling application that identified available meeting times by following user-provided rules, such as length of meeting, earliest start time, latest end time, optional attendees, required attendees, and mandatory attendees (without whom the meeting can't even be held).  His application would then read in the identified attendees' schedules, then use an advanced algorithm to identify ideal meeting times.  Alessandro mentioned the name of the algorithm during the feedback session at the end of the day, but I can't remember it for certain; I believe it may have been bit vector intersection, an advanced technique indeed.

In contrast with David Nealey's simple, brute-force approach to graphics in his Day 1 presentation on Attractive Infographics, Alessandro favors a pure-code approach.  Whereas David had a long career as a geologist, Alessandro has been programming for nearly 40 years.  He's most at home in the code.  

But how do you do it, Alessandro?

Unfortunately, Alessandro did not have time to show how he accomplished his techniques.  When pressed during the Q&A session, he finally said, "It's not really that hard.  It's just lines and labels and some code."  An understated response if ever there was one.

Alessandro's work was truly magnificent.  I hope Karl and the other organizers release the recording of his presentation publicly.  You really need to see it to believe it.

Practical Tools for Access

The final presentation of the day was a brief overview of two free and two commercial tools for Access developers, presented by their authors.

Data Source Manager

A replacement for the anemic Linked Table Manager built in to Access, Kevin Bell's free Data Source Manager offers several improvements over the native alternative.

• ODBC connection string editor
• Easily add tables to an existing connection
• Search and filter all linked tables
• Tree-view control that groups links by type (Access, SQL Server, Excel, etc.) and data source (db name, Excel filename, etc.)

Kevin recently set up a ClickOnce installer that provides first-class ribbon integration for .accdb databases (the Data Source Manager gets its own ribbon group instead of getting unceremoniously dumped in to the "Add-ins" group).  The .exe installer is still provided for .mdb support.

I really liked the icons that Kevin included in both the Data Source Manager and his SQL Azure hybrid demo application, so I asked him where he got them.  He said they came from icons8.com, the 40x40 size.  It appears that this is the set he was using:

Advanced Data Generator

The ADG tool lives up to the Advanced part of its name.  This tool will generate test data that looks like real data.  For example, you can generate country/region-specific first and last names, addresses, and cities.  You can also generate order details in a one-to-many subtable of an invoices table.  You have complete control over the number of records added, the methods used to generate the test data, and can even pull data from existing lookup tables.

The pro version with DAO/ADO connectivity sells for €219 +VAT (~$265), but there is a €99 (~$120) version with support limited to Access database files.

JSON Analyse & Transform for Access (JATFA)

The JATFA tool enables importing JSON files directly into well-structured Access tables.  More and more web APIs offer data in JSON format, but Access cannot import .json files natively.  This tool fills that gap.

It reads the structure of the .json file, creates tables (including one-to-many subtables), and populates those tables with the .json contents.  

The program costs £60.00 (roughly $85 USD).

Access Crash Reporter

Anders Ebro demonstrated his Access Crash Reporter tool, which captures environment information, screenshots, and other relevant bug report data.  The user then has a chance to review the information and email the report to the developer.

Anders offers this tool for the princely sum of zero dollars.  Based on current exchange rates, this works out to roughly zero pounds sterling or about zero Euros (plus VAT, of course).