comparison between MSSQL and Access

Anon

what are the major facilities provided by SQl which are not provided by Access. In what ways SQL is much more better than Access?
thanx

[deleted]

MS-SQL is an RDBMS, Access is a fancy interface to a flatfilesystem.

MS-SQL has raw power, Access has... well... it works.. most of the time...

Anon

SQL is not an RDBMS (Relational Database Management System). SQL is a database manipulating language. MSSQL is an RDBMS like Vincent stated. MSAccess is very umm.. useless with regards to handling data. It's functionality is very limited. It lacks a lot of 'true' relational database power, simply because it's useful for just creating tables and views/queries. For tables, it is very limited in normalizing data the way it should be done.

Anon

JB,

I've just accepted a position where I'm inheriting a bunch of Access "databases" and programs. I'm going to need to write up a business case for migrating this information (which currently works fine) from Access in to a RDBMS. I come from a Unix background using Oracle/MySQL/Informix so I know plenty about RDBMS' but I know next to nothing about Access. So I was wondering if the information that you are giving here is from personal experience, or something that you've read somewhere, or just a gut feeling.

I agree with what you said, but I need to back up my arguments with proof if I'm going to get management to allow me to change the way things are done. I plan on doing my research on this as well, but if you've already done some I'd appreciate any links or information that you have.

Thanks

[deleted]

Access is not a real RDBMS, and thus it sucks as a database, but it does interface nicely with the rest of MS-crappy products. (yes I take great pleasure in calling MS products crap🙂 )

Unfortunately, most managers and higher-ranked persons care only about how things look, not how they work. (wizard-lovers)

Informix is pretty much unbeatable as a database, but have you tried getting a report out of it with a point-and-click interface? :-)

If you have small numbers of records, you don't care about data-integrety and you have all the time in the world, stay with access.
If you need a reliable fast database, use MS-SQL

But, the easiest proof of which is better is in an experiment.

Create a database in access and MS-SQL, and fill both with 4 Million records.
Access will keel-over and die before you are done inserting, and MS-SQL will keep running and ask if that's all you've got. (ok, informix doesn't get interested untill you go over 10M rows, but that's not the issue)

If that's not a good reason to take your business-critical data out of access and into a real RDBMS, I don't know what is.

Anon

Trust me, I agree whole heartedly. As I told them in my interveiw I beileve that Access is only useful for keeping recipie's and address books. But I need to have something to back me up when I go before them. So I'm looking for some hard numbers and testing information. I can run some tests like the one you suggested but it'd be nice to have more than just my numbers to show.

frewuill

ok.. grab a DB book.. a real one about theory on relational database (i.e. Database Systems Elmasri/Navathe)and then read what a database is.. what a database should do.. restrictions, integrity, security,normalization, etc.. then look at access and look MSSQL, ORACLE, SYBASE, or MYSQL.. etc then you'll be capable by yourself to prove before your boss why they shouldn't use access if they are planning a critical appplication with thousans of records.. or in the other hand.. why they should not spend a lot of money (in MSSQL, ORACLE, SYBASE, INFORMIX, etc )if all you'll record is just 5 records a month

Hope this helps.

Anon

Thanks, but I didn't realize that I was comming across as not knowing anything. I've been working as a database applications developer for the last 6 years. I know what a real RDBMS can and can't do. My problem is that I've never worked with Access for more than a couple of minutes (that's all it took for it to piss me off) so my thoughts on it may be biased and not based completely on re-creatable data.

I can and will write a very compelling argument for moving off of Access an on to any RDBMS, but what I want is someone elses published facts and figures to help support my case. I haven't started looking yet since I haven't started the job and don't know what the databases hold. I know one has become mission critical for the organization and 1 is mission crititcal for a certain department, but I don't know how much data is stored in them yet, or how much use they get. If it's only 5 records a month, and only 3 people look at the data for anything, and keeping the data for 100 years isn't important, then ofcourse there is no real reason to move from a system that works and spend money on something else. But I don't get that impression from the people who hired me since they first brought up the idea of porting off of Access.

Trust me I know what I'm talking about I'm just looking for some published data on the subject.

Anon

Simple case for MS SQL. Multiple clients. In my experience, having anything over a dozen absolutely kill Access. MSSQL will handle many, many more. For an even better demonstration, have some of those clients try to update the tables that the others are reading from. You will run screaming shortly.

Sorry I don't have any numbers or such.

kparker

My problem is that I've never worked with Access for more than
a couple of minutes (that's all it took for it to piss me off)

Hey, there's one answer:

"If we use access, all of our best developers will quit."

kparker

Why not arrange a few simple comparative benchmarks of the type Vincent suggested?

Anon

I'd planned on doing something like that. I was just hoping to find data from other people to help validate my finding. Thanks for the suggestion.

Anon

Thanks, I'll have to run some test like this. I hadn't thought about connectivity issues.

Anon

Excuse me if I was a little rude, now I can see you know what you are talking about, if i found something i think could be usefull for you i'll let you know it.

Sometimes people post here just looking for others to do the homework they doesn't want to do, i'm not refering to you but trying to explain my initial attitude.

Anon

I understand that annoys me also. It's so easy to just look up some of the things people ask here, and yet they don't do it.