Alternative to MS-Access/Excel for Spreadsheet Manipulation

Question

I have an MS-Access application that formats the data in two large spreadsheets (20,000+ rows), imports this data into tables, runs a few queries comparing the data and outputs the results as excel files.

The problem is that as the application (and VBA code) grows it's becoming more of a pain using Access and I'm wondering if there is a better approach?

What would be the advantages/disadvantages of a .NET(C#)solution say, compared to MS-Access, and what would be the best libraries etc. to use?

Cheers,

Breandán

Cheers for the responses so far, I forgot to mention though that this application needs to be stand alone, I need to be able to package up the app and and send it to the end user to install on their computer. This only has (of note) MS-Office and .Net Framework installed, so I'm not sure how feasible MySQL etc. would be with no where to host it.

Answer 1

Moving to .Net would allow you to have better tools at your disposal to manipulate the data.

You have to be careful though about what exactly you are doing at the moment with your Access solution: if you're doing a lot of special case handling for processing the data from and to Excel, then chances are you'll still have to do those in whatever language or framework you chose.

If you have a lot of code invested into pulling the Excel Data into Access then you could still keep Access for that part and use .Net to help you in doing the comparisons and creation of the resulting Excel report.

It's a bit hard to really make a recommendation without knowing more about your project.

If you just want to use automation to pull-in data and create your Excel file, then .Net may not offer you a lot as you'll still have to do the exact same things you've already done in Access.

Instead, you may consider using commercial Excel component that use different paradigm to open/create Excel spreadsheet in a nicer way.
There are a few component vendors that have these.

One solution is also to use reporting tools to directly pull the data from Excel and produce a report that you can simply save back to Excel.

My advice would be:

• If your Access solution is stable and it's doing its job, then you may consider keeping it.
  Moving to a new system will cost you time and money and you have to check if the outcome is worth the investment.
• If you feel too constrained by the capabilities of Access, then spend some time experimenting with various solutions and components that allow you to manipulate Excel, for instance using a LINQ Excel provider (1 or 2) may provide a nice abstraction, or try various commercial components until you find one that matches your needs.

If you're going the .Net route, you may end-up not even needing a database for processing the data.
If you do though, you can always use Jet -or its new version, ACE- as a back-end that will create MSAccess databases.
It's already installed on most machines and well supported by MS tools.
Other good options are SQL Server Compact and SQLite as none of these require complex setup, just a DLL to ship with your project.