~15 data sets w/ 25k rows & ~20 columns + quick one time reports = Access VS SQL?

Question

I am working with around 10-15 datasets each containing around 25,000 rows and around 20 columns. Data is text in name/adress/desc and the rest numbers.

I need to be able to calculate certain fields, pull out data on demand for certain list of customers (10, 20, 30) and generally extract data based on certain criteria.

Am I better off using MS Access or using SQL?

Please ask if You need any additional info..

Answer 1

Access is a perfectly fine database system for small scale individual-user apps. Here are some criteria for shifting:

• Multi-user applications. Access doesn't do concurrency very well, so a multi-user database - particularly one where multiple users are making modifications to data - will bring Access to it's knees.
• Requirement to support advanced SQL functions (windowing, partitioning, MERGE, triggers etc).
• Large datasets. Access used to have hard limits on .mdb files at 2GB (IIRC), and I found the likelihood of corruption increases rapidly when the total DB size gets over a gigabyte. (Note that I haven't used Access 2010, so I don't know if things are better now in this regard.)

Note that if you have a front-end built in Access and just want to shift the database away from JET (Access's internal DB engine) onto a "proper" RDBMS, you can do so by migrating the data across and setting up linked tables inside Access to the new data source.

Answer 2

Access has one of the best report writers in the business. Not only is it highly capable in its own right, but it's fully programmable with VBA.

In my younger days, I designed and built Access applications for law firms. I also gave several presentations at local Bar Association events. (In the early days of personal computing, lawyers at the biggest firms were expected to design and build their own litigation support databases.)

I've had 80 concurrent users, and quarter-second response time on normal queries with 2 million rows. But designing Access apps to do that isn't trivial. For a few concurrent users, you don't need to do anything exotic. Number of rows doesn't matter much. (Much.)

Still, I think your best first step is to build an Access app with two files: one for the tables and their relationships, and another for everything else--forms, reports, links to the other file, and so on. That way, should SQL Server become necessary, you can just migrate the tables to SQL Server and relink them in Access.

Answer 3

It's hard to say, without knowing what kinds of queries you're doing. Calculations may or may not make a difference, depending on the kinds of calculations I imagine that a simple calculation that just multiplies a column's value by a constant or another value would be less expensive than calculations involving analytic functions.

If the tables are indexed properly, 25k rows should not be a problem.

In my experience, Access might give you a quicker start-up time, in the sense that you get reports and results a little sooner than if you had to construct a database on a server, but Access has many other quirks and problems that I always find slow me down, such as the SQL editor for query editing is terrible IMO.