MS Access versus SQL Server

Question

Post reply

MS Access versus SQL Server

JackBNimble

Valued Member

Points: 69
More actions
September 11, 2003 at 2:48 pm

#64941

There must be some empirical criteria where one could choose between SQL and MS Access. Does anyone know where to find some good papers or other reference material?
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

Try http://msdn.microsoft.com I seem to remember reading some of there papers about it a while ago.

Don't forget MSDE from your research.

Steven

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I don't know of 'white papers' on this topic, but I think if you state your needs as to

security

scalability

useability

performance

budget

...

the decision should be fairly simple

Frank

Wenn Englisch zu schwierig ist?

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Keith Henry Hall of Fame Points: 3759 More actions · Answer 3

Just to confuse things Access 2000 on can use sql as the back end. The choice is not Access vs SQL but "MS Jet 4.0 Engine" vs "MSDE|MSSQL Engine"

The only reason you would ever choose Jet over SQL was if the entire app had to be file (rather than service) based, and even then you're better off with Fox Pro!

Keith Henry

Edited by - keithh on 09/17/2003 08:39:30 AM

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

Gil_Adi SSCommitted Points: 1669 More actions · Answer 4

Lets Put Some Numbers on The Tables

I will recomend that if you have more than 5 Users at the Same time for the Data base Use SQL Server

Large Data Sets Are Handled better by SQL Server And I Mean A bout 10,000 Records And Above (Access Can Hadle That to But It Lacks The Advanced Angine That SQL Server HAs)

Sequrity is Much More Advance on SQL Server

SQL Server Has Trigers Wich Access Lacks

Access Is easyer to Program And IS A very Good For Rapid Development

I will Prefer to combine

Access As The UI With Linked Tables From SQL Server

I you need to Deploy Your Application With Out Having The Ability To Connect To A Network Than Access Is The Tool.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 5

quote:
Access As The UI With Linked Tables From SQL Server

Never never ever use linked tables, they are the worst way to access data going and will slow down your SQL box!

Instead use an Access Project and you can have the speed of Access front end development but with SQL as a proper data/back end

quote:
I will recomend that if you have more than 5 Users at the Same time for the Data base Use SQL Server

More than 1 use sql. Access breaks down even with just 2

Keith Henry

DBA/Developer/BI Manager

Edited by - keithh on 09/18/2003 02:20:40 AM

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

quote:
Access Is easyer to Program And IS A very Good For Rapid Development

well, that just a matter of what you are used to. Per se no system is superior.

Access is better for RAD or Prototyping, but I'm not sure if that's what you want to do with SQL Server.

quote:
Access As The UI With Linked Tables From SQL Server

Linked tables are not really nice.

AFAIK with linked tables you leave control to the Jet engine.

So you'll get no real Client-Server system.

One thing that speaks for Access is the IMHO exceptional reporting engine.

What hasn't been mentioned yet are the cost. SQL Server is more expensive than Access. Maybe that's a point.

It depends....

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

adamcrv Hall of Fame Points: 3056 More actions · Answer 7

A lot of us may be biased here but SQL server is defineatel better than access for applications requiring a solid, sound and secure (as long as its patched) data backend. MSDE might be more cost effective for you if the volume of users is low.

FYI, Our product utilised Access for the FE and Access for the BE and we had about 5+ users hitting it doing all manner of stuff on it. The database took a year of pounding before it gave up. We sent the BE to SQL server and have never had a prob with that client since 🙂

Good luck in your choice though 🙂

------------------------------
Life is far too important to be taken seriously

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 8

Using Access as a database engine, you need to realize that your database can only grow to 2GB. So here's my breakdown on when to use Access vs. SQL.

1. Use Access for databases less than 2GB.

2. Use Access/JET for low end databases (speed not a requirement).

3. Use Access/MSDE for low end databases that need the SQL database engine. The MSDE software is on the Access 2002/Office XP installation CD.

4. Use SQL Server for > 2GB databases and speed.

All the best,

Dale

Edited by - DALEC on 09/22/2003 09:50:24 AM

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

PBirch SSCrazy Eights Points: 8493 More actions · Answer 9

I started with Access so I understand the limitations.

Access is fine for prototyping, or creating a system that is not mission critical. It is great for translating information between databases or whatever you like. The reporting tool is good. Most of the customer I had (underline the "had") used Access because it was "free" (part of Office).

PROBLEMS with Access

It is not robust. Adding more users is never a solution.

Security is a joke.

Logging is basic.

The database can freeze and die. Backup early and often.

Did someone say FoxPro?

Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.

Patrick

Quand on parle du loup, on en voit la queue

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 10

In favor of Access, it makes a great front end for our SQL Server. That's assuming you are familiar with Access projects.

All the best,

Dale

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

scking SSCrazy Points: 2598 More actions · Answer 11

I've developed using the Jet engine and SQL Server from Access an Access/ASP front end. You need to determine what your requirements are before you pick ANY database engine. Never say Never because each was developed to satisfy different needs for different customers. There are significant advantages to using Access/Jet and for using SQL Server, then each has reasons you may not want to choose them. Some critical criteria include;

- Size of completed database (mentioned)

- Number of concurrent users (mentioned)

- Configuration (direct access vs network)

- COST (this is pretty big if you are not a corporation with resources to plunk out for a Database Server, Server licenses, SQL Server, etc. AND have a small application)

- Security (SQL Server has significantly improved security BUT the real question is, is it needed)

- Application (Is it a legacy system to be converted OR new system. Some legacy systems may be easier to rebuild from scratch with a new design)

- Etc, Etc

I do strongly recommend that if there is any possibility that the database will grow or require any of the features Jet isn't designed to satisfy then you develop the application in SQL Server. It is not fun or simple to upgrade an Access/Jet database to Access/SQL Server or Anything/SQL Server. The upgrade path may require complete redesign to take advantage of the features in SQL Server.

Please consider this as Steve's opinion. You should establish your own criteria and make your own choices. I will say that I have significant experience in Access/Jet with larger applications and a fair amount of experience converting some of those legacy applications over the past year.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 12

MSDE (the FREE version of SQL) beats Access in every possible way. Therefore cost is no reason to pick Jet.

The Jet engine does, will and always has monumentally sucked. If you run 5 users for more then a few days then you are lucky. I have supported large scale Access databases as back ends and I have to say it way an amazingly dumb way to run things.

So Access forms and reports

Everything else

Keith Henry

DBA/Developer/BI Manager

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

zort2001 SSC Enthusiast Points: 115 More actions · Answer 13

I understand the issues MSDE vs. JET.

When explaining the issues to Management, they will ask for Documentation FROM MICROSOFT that directly points out the 5 user performance governor.

Can anyone point to such documentation! I have searched for this over and over.

Thanks,

Zort2001

bigdmoore SSChasing Mays Points: 630 More actions · Answer 14

Just my 2 cents...

I have developed using Access for over 6 years and mostly agree with what has been posted so far.

However I would say that I have successfully run JET databases with 30+ users successfully. You just have to be careful with how you handle concurrency and record locking. One way is to use unbound forms and check for record locking when you save.

Also, it is possible to set up security using JET, but most people don't take the time to figure it out since it's fairly complex.

I have had repeated problems with database corruption, which is probably the biggest downside to using JET. However this is usually easily fixed by getting the users out and then compacting/repairing the database.

All in all, I would recommend Access/JET if you have a small number of users, don't plan on scaling and your data is not mission critical. It is useful for very small apps (1 user) running on the desktop as a convenient way to store data where you don't want to manage the complexity of setting up and maintaining the MSDE.