Two years ago, he decided to give up his career in the corporate technology world and bought the airline (founded in the late 1940’s) to follow his passion for flying. This is a small but growing business made up of pilots, passengers, and propellers.

The airline's fleet consists of Cessna 172, 206, and 207 airplanes that are the “VWs of the skies”, four to six seaters utilizing either 180 or 300 hp, air-cooled engines.  A new airplane would cost a half million dollars, so these early 1970’s vintage planes are regularly serviced in a large hangar that we visited. We got a chance to see vintage plane parts hiding in the corners, disassembled fuselages under maintenance, amusing posters from the fifties, and the innards of engine compartments. Each plane can tote up to 1,500 lbs. of passengers, possessions, and please don’t forget about 40 gallons of gas.

Yet another creative Access solution

Right away, Jason hit turbulence. The company was entirely paper-based and sometimes flying blind when it came to basic business tasks. Because he couldn’t find any off-the-shelf product to run the outfit efficiently, he decided to build a custom application from the ground up using Access, which now lies at the heart of his day-to-day operations.

Largely self-taught as a programmer, Jason developed the entire solution from scratch. He followed a typical flight path for Access: using the Jet database engine as the back-end, creating various form and report objects as needs arose, incorporating and redesigning maintenance spreadsheets to create a work traceability log, writing many VBA modules, and then upsizing to Azure SQL to scale his solution.

He now has 105 tables (70% of them linked to Azure SQL), 80 queries, 70 forms, and 25 reports. One large form drives the entire application which makes liberal use of popup forms and drop-down lists to conserve screen real estate and instantly present vital data. He even incorporates credit card transactions by using Access XML to transfer data to the bank.

Along this circuitous route, Jason developed a company web portal for pilots and mechanics to access up-to-date information on their smart phones, such as flight schedules, manifests, customer reservations, and maintenance records. To build his site pages, he used what he was most familiar with: ASP, VB Script, and CanvasJS (for charts) to pull live data from the Azure SQL back-end.

Here’s the view from ten thousand feet:

Deployment is a snap because his employees are using Office 365. After he updates his Access solution, he simply emails a download link from a shared drive.

Jason is constantly elevating his game. His future plans are to add an automated reservation system and a booking site. Now that he has captured critical business data, he can also create reports and charts, generate business intelligence, and reliably grow his enterprise.

Airing it out to the Access team

Jason recently discovered the new Linked Table Manager and had a good first impression. He called out the improvements, especially the ability to quickly refresh a table from the navigation pane and the new Relink command.

For accounting, he currently exports data to Excel and imports to QuickBooks but would love a read/write connector built right into Access to help automate the process.

Although he had not yet discovered the new Access charts, we pointed him to the feature, and he was intrigued to say the least.

Jason particularly likes how easy it is to install instances of SQL Server with Azure SQL and found ODBC Driver 13.1 to be fast and efficient.

However, migrating data from Access to Azure SQL was a struggle and his queries initially did not perform well, until he converted them to pass-through queries. Even converting queries was an added challenge due to syntax, function, and date disparities between Access SQL and TSQL. He recommended providing a much more streamlined way to migrate Access to Azure SQL. In particular, he would like an easy way to create a pass-through query and save it as a SQL view.

Happy landings

We left with a strong respect for the design and development of a unique Access solution that was at the heart of a rising business. More and more, Access customers are creating applications that combine Access as a front-end, Azure SQL as a back-end, and a hybrid mobile-first, cloud-first mix.

By the way, Jason lives with his family on the San Juan islands and commutes to his office in his personal plane almost every day. Now that’s one unique way to create a work-life balance and enjoy a roundtrip ticket to your journey to the cloud.