Terminal Services Application for Ice Cream Manufacturer

Application Profile

Name	ERP System for Ice Cream Manufacturer
Tasks	Manages almost all the company's processes
Users	30 most on premise, some remote
Software Versions	Office 365, SQL Server 2019 on prem, Terminal Services
Particularities	Specialized bar code readers run Access on a Terminal Server
Company	IT Impact Inc., (AccessExperts), USA, AccessExperts.com
Writer	Juan Soto (president at the software consultant firm) adapted by Maria Barnes (AFo)

A couple of years ago IT Impact was approached by a major ice cream manufacturer who needed to upgrade 19 Access front ends from Access ADPs to Office 365 and the corresponding on prem SQL Server to a newer version. We asked them if they had considered using a modern ERP system for their operation, but they LOVED their Access system and converting to an ERP would have cost around $250,000. Even after spending that much, there was no guarantee the ERP would work for their needs entirely. In the end it was an easy decision.

The challenge of converting from an ADP to ACCDB

ADPs are a now deprecated Access file format that allowed the direct use of a Microsoft SQL Server database as a backend. This is different than the current approach using an ACCDB which links only tables to SQL Server. In an ADP you were also directly connected to SQL Server views, stored procedures, and user-defined functions. Their previous version of SQL Server was also no longer supported and definitely needed to be upgraded. IT Impact took on the nearly year-long project.

Differences between the interface with SQL

Converting from ADP to ACCDB can be arduous for a system this size, taking us a year to convert, check for compatibility issues and test all the components. Of particular significance is the need to maintain the old ADP views as pass through SQL queries to keep the speed of the application from deprecating. The client knew it was going to take this long and was willing to make the financial commitment, which was in the low six figures.

The Application that handles it all

This system handles almost everything for the company. It manages all orders, the inventory of raw ingredients and finished goods, training, production schedules, quality assurance, and human resources. The company has 100 employees total spanning two shifts and multiple buildings. They use SQL Server and Terminal Services for EVERYTHING. The users of the applications are mostly on premise. Only a few access the applications remotely. Of particular note, some of the users do not have actual computers. They use the application only from a handheld bar code reader!

Example of barcode reader similar to what is used

It all starts with the warehouse operation, they have three warehouses in different locations around town, and all the inventory management is done in Access. The handheld scanners running Access is a great way to carry Access around with you inside the warehouse!

The power of Terminal Services

In order to ensure easy management of the App, the customer decided on using Terminal Services, which is Microsoft's multiuser, thin client available for Windows servers. In this case a user's connecting device, whether a fully loaded PC or a terminal only, functions as an I/O terminal. Both the OS and application are run in the server. Terminal Services uses Microsoft's Remote Desktop Protocol (RDP) to govern keyboard, mouse, and screen transfer.

Access with Terminal Services

There are many reasons to consider using Terminal Services with a Microsoft Access application including

• Increase in application performance and speed (particularly if the Terminal Server is running in the same physical location as the data source)

• Centralizing the application for ease of upgrading and management

• Expanding the application beyond a LAN environment

• Using the application on a device that is only an I/O terminal (such as the barcode reader)

• Ensure all the users have the same desktop

Issues with using Terminal Services

• Everyone is using the same server, so if the server has a problem or is unavailable, every user is locked out of the application.

• If a lot of users login, the server may require a hefty dose of CPU power and/or RAM, no underpowered servers will work.

• There is no way to customize Office, meaning if you need an add-in for Access, either everyone gets the add-in or no one does.

• Can be hard to configure printing.

This was a large and very interesting project. Bonus - we got a lot of free ice cream while we were working on it!

The Showcase section presents interesting Access projects to demonstrate the current range of usage and capabilities of the product. Interesting can refer to technical, organizational or thematic aspects. If you would like to suggest an application for an article, please contact us using this form.