Excel Export + Different Access Versions

Excel-Access Integration for Volume Data Manipulation + Version Control

Parkinson Associates specialise in Market Research for the diesel engine and power generation industries. The company developed "Genstat" which enables their customers to track their market share for industrial products as well as those of their competitors world-wide.

The Problem

Our client had an existing self-built Access database which was used as a means of distributing information to his customers. Specifically this was the import, export, production and consumption figures of diesel generators across all countries.

We were approached as our client was experiencing problems using the Access Runtime for customers with different versions of Access and/or Windows. This manifested itself as strange errors caused by incompatible DLLs.

Furthermore tables, queries and forms had been duplicated for each country/power-band. This resulted in about 100 of each which was on the limit of manageability. If our client wanted to add a feature this had to be applied to each, which was a laborious task. Customisation of his software for his customers was also difficult to manage, as this resulted in yet more duplication.

Complex analysis and data manipulation of large data sets was required to get it into a format that would be meaningful and useful to his customers. The knowledge and methodology for doing this was our client's strength. Data was collected from a variety of sources each requiring different processing. However, this was extremely time-consuming with the considerable volume of data involved. The resultant spreadsheets had tens of thousands of rows.

The Solution

Our solution was to provide a much simplified “master” database comprising just 2 core tables and 3 main forms, with lookup functionality plus supporting code. This database now stores all the core data for all years in a structured format.

Adding new items of data, such as new countries and power-bands, is easy as all features are now driven automatically from the data. Forms no longer need to be replicated as data filters are dynamically generated by code within the forms.

We added functionality to bulk pre-process incoming data and re-organise existing data. A custom Excel import/export feature allows our client to use his current volume data manipulation processes to provide the comprehensive information required by his customers.

For Parkinsons’ customers we added a sophisticated visualisation and filtering capability making it much easier for them to find the information they need and evaluate it. We also added a function to export to Excel .

Version control of his software has become manageable. The Genstat product is now simple to configure for each customer to enable only the features that have been paid for. This has enabled him to move from annual releases to quarterly releases for his product .

To whom it may concern.

I have no hesitation in recommending PC ACCESS for all your database needs.

As a self taught computer user I developed a highly successful commercial database product over 20 years ago based on Microsoft Access.

The database is updated bi-annually and distributed internationally to multi-national clients.

For most of those years as the business was growing I had to stay with my inadequate knowledge of Microsoft Access to protect cash flow.

The database until relatively recently was distributed on CD and its appearance over the years began to look very dated.

During that period every time I had to update and distribute the product I had sleepless nights over whether the product would work.

Historically I had to deal with such things as :

Updates in Windows
Incompatibility of my original Access Software with Windows
Changes in Jet – whatever that means
Installing and learning to operate new Access software

All of which caused delay in distribution.

It was all akin to days of old when cars were unreliable knowing you had to get up the next day and drive 200 miles to an important meeting and worrying if the car would start.

So much for the history, those days are gone and I now think I’m driving a new Bentley (I wish).

How has that happened : I contacted PC ACCESS.

PCACCESS developed a bespoke system for me which not only looks professional but also one which I can transfer to clients on line.

Together we spent many hours refining the specification so that the product gives the customer “exactly what it says on the tin”.

All my clients have all welcomed the new appearance and I have had no adverse comments whatsoever.

We now look a much more professional organisation.

As to the sleepless nights, they have gone since I now have a qualified Bentley mechanic at the other end of the telephone whenever I need him

George Williamson, Owner, Parkinson Assoiciates