Metal Packaging Production Statistics Database

Access Database replaces Oracle JDE System to simplify process

Sonoco manufacture metal packaging for a wide range of products including biscuits, bottle packaging and food. One of their UK production sites had been using a two Visual Basic (VB) programs to record production statistics (eg pallets produced, waste, etc) for a number of years but one of these applications had recently stopped working. Additionally, over the years this VB.net program has had many changes and now contained a significant amount of redundant code and functionality, so Sonoco were now looking to replace it with an application which was easier to maintain.

Xlteq were asked to take a look at the process and programs and discovered that the original VB systems extracted data from an Oracle JDE system and then interfaced with several Access databases. After an analysis of the original VB program, Xlteq realised that the program could be replaced with simpler Access databases which could replicate the entire functionality.

 First Steps

Xlteq received over 1,000 files from Sonoco which made up the source code, projects and compiled programs as well as XML data files, ini files and executables. The VB source files were loaded into Visual Studio and the code was inspected to try and understand how these files were utilised and how the programs worked.

Example of a project in Visual Studio

After mapping out the previous design we could then start to build the replacements in Access, working through the code and forms to create the Access equivalents. We also removed redundant programming which had built up over the years, added comments to help future changes and redesigned the forms for a more modern and tidy look

Original Design

New Updated Design

Updating the Code

Updating code from one language (VB.Net) to another (VBA) can present several problems. Whilst VB.Net and VBA share many similarities, sometime the equivalent code does not directly exist in VBA so needs to be built from scratch.

For example changing the background colour in the original program required a few lines of code:

However for VBA we need to create additional code to get this to work as there is no “.fromArgb” functionality in VBA.

We also take the opportunity to improve on the original by checking how bright or dark the background colour is and adjusting the foreground colour accordingly:

Further code

However it is not always the case that we need to create additional code to ensure the new application will work. As we are creating the new application in the Access system, the database functionality in Access can make the code less complicated. For example whenever we need to perform database functions such as “Update”, “Delete”, etc, Access normally allows a SQL statement to do this rather than having to deal with virtual data tables as the old application uses.

Example of old VB.Net code

Example of updated VB code

Implementation

Once all the old code had been converted into new, the new programs were transferred and tested to ensure no additional issues had been added in the conversion process. Doing this showed file permissions (eg Delete) were not granted in the same way using Access. The code was adjusted accordingly to compensate for this.

Contact us to find out more about our Access Database development and support.


Contact