Why choose SQL Express over Access?

  • Thread starter Thread starter ljh
  • Start date Start date
L

ljh

Why would you choose SQL Express (which requires an installed application to
work) over the simplicity of an Access database which has no dependencies?
 
Well, I'm not sure what you mean by "requires an installed application
to work". I just downloaded it, ran the setup & it worked. And, since
your reluctance to do that is the *ONLY* requirement you specify for
your database needs, it's really hard to answer your question.

So, the advantages of SQLExpress over Access, as I see them.
1) It's free.
2) It's directly compatible with Sql Server.
 
Short incomplete list of reasons -

- SQL Express (or SQL Server in general) will scale better to multiple
users.
- It will give you a "way out" when your DB exceeds 4GB
- It will be easier to maintain from a DBA point of view (centralized
backups *.*)
- It will give you a much richer feature set - notification, SQLCLR, better
T-SQL*.*
- It will give you better performance (No OleDb necessary)
- You won't have to compact it as often
- Better support for data types/indexes etc. etc.
- Other reasons.

The only advantage Access gives you is "File based deployment". And frankly
SQL Anywhere (or was it everywhere - I loose track in all these name
changes) should be a better choice for desktop-ish applications anyway.

- Sahil Malik
http://www.winsmarts.com
 
I mean that you can use Access databases simply by including the .mdb files
with your application, whereas using SQL Server Express requires that you
install SQL Server Express and have it running in the backgound.

In the event that I use SQL Server Express and need to redistribute it with
my app, is there a silent install available?
 
I can't find anything on SQL Anywhere - even on the Sybase site.

The closest I can get is a SQL Anywhere link that actually shows you
something called "Remoteware" (whatever the hell that is) -
http://www.sybase.com/products/mobilesolutions/sqlanywhere .

I don't really trust Borland anyway. They jumped right in line with the
whole .Net mantra - when they had a better way of doing things and they have
abandoned Kylix without ever admitting to doing so.

They do enough to keep the Borland name alive....but that seems to be about
it.
 
ljh said:
I can't find anything on SQL Anywhere - even on the Sybase site.

The closest I can get is a SQL Anywhere link that actually shows you
something called "Remoteware" (whatever the hell that is) -
http://www.sybase.com/products/mobilesolutions/sqlanywhere .
Click to expand...

Yeah, they change product names every 5 minutes, which is why they'll
never amount to anything. However, I used the product a couple of years
ago and it is solid. It has all the drivers and easy of use and
surprising performance and all that.
I don't really trust Borland anyway. They jumped right in line with the
Click to expand...
I don't think Sybase has anything to do with Borland. You got your
vendors confused.
 
Sahil said:
The only advantage Access gives you is "File based deployment". And frankly
SQL Anywhere (or was it everywhere - I loose track in all these name
changes) should be a better choice for desktop-ish applications anyway.
Click to expand...

There is one more Access advantage: it'll run on Windows XP Home
Edition, while SQL Express will not (requires XP Pro). So if you are
targeting mom&pop shops or the home market, either do Access or stick to
MSDE.

Regards
 
ljh said:
Why would you choose SQL Express (which requires an installed application to
work) over the simplicity of an Access database which has no dependencies?
Click to expand...
Sql Express is a lot more performant and scaleable than access.
If you get a db size over 1/2 GB or so in access you will probably run
into trouble with corruptions etc.
If you eventually need to upgrade to full Sql Server then its a straight
backup / restore operation of the database and you are up and running.
If you only need and will only ever need a very simple db mechanism then
access might be the way to go.

JB
 
Another option for you may be SQL Everywhere. I think will be released this
summer. 1.4mb with 7 dlls.
http://blogs.msdn.com/stevelasker/archive/2006/04/10/SqlEverywhereInfo.aspx

--
William Stacey [MVP]

|I mean that you can use Access databases simply by including the .mdb files
| with your application, whereas using SQL Server Express requires that you
| install SQL Server Express and have it running in the backgound.
|
| In the event that I use SQL Server Express and need to redistribute it
with
| my app, is there a silent install available?
|
| | > Well, I'm not sure what you mean by "requires an installed application
| > to work". I just downloaded it, ran the setup & it worked. And, since
| > your reluctance to do that is the *ONLY* requirement you specify for
| > your database needs, it's really hard to answer your question.
| >
| > So, the advantages of SQLExpress over Access, as I see them.
| > 1) It's free.
| > 2) It's directly compatible with Sql Server.
| >
|
|
 
It is Sql Everywhere. Check out the faq in the linq I gave above. For the
most part, it is Sql Mobile with a license change that will run anywhere
(XP, etc).

--
William Stacey [MVP]

|I can't find anything on SQL Anywhere - even on the Sybase site.
|
| The closest I can get is a SQL Anywhere link that actually shows you
| something called "Remoteware" (whatever the hell that is) -
| http://www.sybase.com/products/mobilesolutions/sqlanywhere .
|
| I don't really trust Borland anyway. They jumped right in line with the
| whole .Net mantra - when they had a better way of doing things and they
have
| abandoned Kylix without ever admitting to doing so.
|
| They do enough to keep the Borland name alive....but that seems to be
about
| it.
|
| | > Short incomplete list of reasons -
| >
| > - SQL Express (or SQL Server in general) will scale better to multiple
| > users.
| > - It will give you a "way out" when your DB exceeds 4GB
| > - It will be easier to maintain from a DBA point of view (centralized
| > backups *.*)
| > - It will give you a much richer feature set - notification, SQLCLR,
| > better T-SQL*.*
| > - It will give you better performance (No OleDb necessary)
| > - You won't have to compact it as often
| > - Better support for data types/indexes etc. etc.
| > - Other reasons.
| >
| > The only advantage Access gives you is "File based deployment". And
| > frankly SQL Anywhere (or was it everywhere - I loose track in all these
| > name changes) should be a better choice for desktop-ish applications
| > anyway.
| >
| > - Sahil Malik
| > http://www.winsmarts.com
| >
| >
| > | >> Why would you choose SQL Express (which requires an installed
application
| >> to work) over the simplicity of an Access database which has no
| >> dependencies?
| >>
| >>
| >>
| >
| >
|
|
 
I have a company that runs several (6 - 10) small stores that does the
rent-to-own thing. The software they have now is generic and the service
sucks. They want a personalized solution for thier rather unique product
line.

Thing is......they want all stores to share information (real-time of
course), they want information stored locally in case there is an internet
outgae, and they don't want to buy a SQL Server license for every store.

So.....I need to come up with a free way to have shared, locally replicated
data at each store.

Theoretically it is pretty simple.

Practically, its a pain in the ass.
 
Awesome!

It doesn't look like they'll let us use it to power webservices under IIS
though.

That would suck. Allowing its use under IIS would make hosted web
development so much easier!

It would let small companies with big ideas give those ideas a shot at
succeeding without spending several thousand dollars for SQL Server.

But, I don't think MS is all that interested in helping small businesses
grow. And, that's too bad. Doing so would actually fatten the bottom line
at MS as the little businesses need more MS licenses and maybe a "grown up"
version of SQL Server.

If they did, who knows, it might even unseat MySQL as the web db king.

Thanks for the great link!
 
You're right..... I got Sybase and Borland mixed up. Must've been that
whole JBuilder collaboration thing that threw me.

They are definitely not the same company.
 
If you really are looking for a lightweight, fast, no - deployment database
then why not look into SQLite? There's an ADO.NET and an ADO.NET 2.0 provider
(thanks to Robert Simpson) and it screams compared to MS Access. Not even an
MDAC dependency.
Peter
 
I'm trying it out. But, I can't even get it to create an empty db so that
I can use the VS.Net 2005 tools to create the tables, columns, etc.

I type "sqlite3 test.db" at the C:\ prompt (where I have a copy of
sqlite3.exe. Then, I type ".exit" and look for the empyt db....but there is
nothing there.

Any suggestions?
 
Got confirmation from MS that they're deliberately breaking compatability
with SQL\e running under IIS - they call it "soft-blocking".

Sounds all fluffy and sweet....but what it means is that you won't be able
to use SQL\e to easily deploy webservices (actually you can;t use it to
deploy them at all - easy or not).

This goes right along with my experience with every single MS product I have
ever purchased. Thier slogan should be "When you need it *almost* right."

Oh well.....I'm still looking into the free SQLite. It has an add-in for
VS.Net 2005 support and is not limited to 4GB of data. Oh yeah...it's only
one file (no dependencies) and it's completely free.

I'll let you know how my testing with it goes.
 
| It doesn't look like they'll let us use it to power webservices under IIS
| though.

Couldn't you use SQL Express for that?
 
| Got confirmation from MS that they're deliberately breaking compatability
| with SQL\e running under IIS - they call it "soft-blocking".

Not sure how they would do that other then via license. I mean a sql query
is not going to look any different coming from asp.net page or from your own
host?

| Sounds all fluffy and sweet....but what it means is that you won't be able
| to use SQL\e to easily deploy webservices (actually you can;t use it to
| deploy them at all - easy or not).

Again, I think you should at least be able to use SQL Express and it is
free. IMHO, that product is a sweet and generous gift from MS.
 
Back
Top