Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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..

share|improve this question
    
How is a "line" different from a "row"? Do you mean lines of text within a text-field? –  FrustratedWithFormsDesigner Mar 6 '12 at 21:18
1  
Not native in English, so I edited question for clarification. But by lines I meant rows actually, wrong expression.. –  Velletti Mar 6 '12 at 21:44
add comment

3 Answers 3

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.

share|improve this answer
    
Access provides an "Up-size" wizard to do just this shift onto SQL Server... –  Nate Mar 7 '12 at 21:20
    
Thanks, I also think I might model it first in Access. –  Velletti Sep 13 '12 at 10:33
add comment

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.

share|improve this answer
    
Thank You so much. –  Velletti Sep 13 '12 at 10:30
add comment

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.

share|improve this answer
    
Queries are rather simple. I'll get a list of customers and I want to extract specific column from specific tables for each one of them. Calculations are only basic math. And reports are not standard in most of cases. –  Velletti Mar 6 '12 at 23:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.