Access vs SQL

[Dan.]

hi there,
a while back i got into a bit of ASP programming and needed database
access. since msAccess was installed on the system i was using at the time i
used that and have used that since. but then i heard something about sql and
that it's better to change to that as early as you can because it's hard
converting access databases and all the code that goes along with it. i
would like to know what advantages the sql holds over access.

thanks in advance
Dan.

[sai]

I guess the major different is that the speed, stability , security
and stored procedure. MSSQL is the strongest DBMS on Microsoft Product
line. There is no doubt in its speed and stability.

stored procedure make you simplify your asp procedure and centralize
in to the DB. It also can speed up your development time.

how to choose the suitable database, which depend on what's your
project size and how complex of it.

sai.

Post by Dan.
hi there,
a while back i got into a bit of ASP programming and needed database
access. since msAccess was installed on the system i was using at the time i
used that and have used that since. but then i heard something about sql and
that it's better to change to that as early as you can because it's hard
converting access databases and all the code that goes along with it. i
would like to know what advantages the sql holds over access.
thanks in advance
Dan.

[John Shaw]

Hi Dan,

I guess the biggest difference is that Access is single-threaded. If
two users hit it at the same time, it has problems. My org wanted to
use a single-threaded postal address scrubber a few months ago. I ran
scripts from two different machines that looped through 50 addresses.
I killed it every time.

[Tony Toews]

Post by John Shaw
I guess the biggest difference is that Access is single-threaded. If
two users hit it at the same time, it has problems.

But the Access back end is on a file server? It's a shared file. Multiple users can
update it at the same time just fine.

Post by John Shaw
My org wanted to
use a single-threaded postal address scrubber a few months ago. I ran
scripts from two different machines that looped through 50 addresses.
I killed it every time.

What do you mean by killed it? Was this postal address scrubber written in Access or
what?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Greg D. Moore (Strider)]

Post by Tony Toews

Post by John Shaw
I guess the biggest difference is that Access is single-threaded. If
two users hit it at the same time, it has problems.

But the Access back end is on a file server? It's a shared file.

Multiple users can

Post by Tony Toews
update it at the same time just fine.

Post by John Shaw
My org wanted to
use a single-threaded postal address scrubber a few months ago. I ran
scripts from two different machines that looped through 50 addresses.
I killed it every time.

What do you mean by killed it? Was this postal address scrubber written in Access or
what?

Ultimately my experience is that while SQL Server will scale better, etc...
one can write poorly written apps on a SQL Server that will kill it and one
can write well written apps on Access that will fly.

I found out the local ITT school has a teacher teaching students that Access
has a hard limit of something like 24 users. I confirmed this with two
students. Note the teacher wasn't suggesting that as a soft limit to use as
a rough guide, but taught it as a hard limit.

Fortunately I was able to correct at least two students.

Post by Tony Toews
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Jim Kennedy]

Post by Dan.
hi there,
a while back i got into a bit of ASP programming and needed database
access. since msAccess was installed on the system i was using at the time i
used that and have used that since. but then i heard something about sql and
that it's better to change to that as early as you can because it's hard
converting access databases and all the code that goes along with it. i
would like to know what advantages the sql holds over access.
thanks in advance
Dan.

SQL is not a database it is a language. (Structured Query Language)
SQLServer is a database. Don't confuse the two.
Jim

[David Portas]

These articles may be helpful.
http://www.aspfaq.com/show.asp?id=2195
http://www.aspfaq.com/show.asp?id=2214

[Trevor Best]

(non existant groups removed from x-post)

Post by David Portas
http://www.aspfaq.com/show.asp?id=2214

From that site:
<---
SQL Server handles NULL values differently. Access assumes NULL = NULL,
so two rows where a column is <NULL> would match a JOIN clause comparing
the two. By default, SQL Server treats NULLs correctly as UNKNOWN, so
that, depending on the settings within SQL Server, it cannot state that
NULL = NULL. If you are trying to determine whether a column contains a
NULL value, the following query change should be made:
-- Access:
[...] WHERE column = NULL
[...] WHERE column <> NULL

-- SQL Server:
[...] WHERE column IS NULL
[...] WHERE column IS NOT NULL
--->

WTF? Where on Earth did that information come from? That is totally
incorrect. Access has never treated a null as equal to another null.

[David Portas]

I'm not an Access expert but I think you are right. That section of Aaron's
article appears to be wrong. Certainly Access 2003 doesn't treat NULLs as
equal when using = or <> operators although I haven't recently used earlier
versions.

[Steve Gerrard]

"David Portas" <***@acm.org> wrote in message news:cP6dncagQZ1LznzcRVn-***@giganews.com...
| I'm not an Access expert but I think you are right. That section of
Aaron's
| article appears to be wrong. Certainly Access 2003 doesn't treat NULLs
as
| equal when using = or <> operators although I haven't recently used
earlier
| versions.
|
| --
| David Portas
| SQL Server MVP
| --
|

The Jet database engine has never joined two records on a field
containing null.

Access, VBA, and VB have always followed a simple rule:
Any expression containing Null evaluates to Null.

So while the expression (10 = 10) evaluates to True,
the expression (Null = Null) evaluates to Null.

The only exception is the concatenation operator, which will allow
"A" & Null & "B"
to evaluate as "AB".

[David Portas]

Post by Steve Gerrard
Any expression containing Null evaluates to Null.

The rule is actually that a *comparison* to NULL using any of the basic
comparison operators (=, <>, >, <) gives a result of *UNKNOWN*. This is not
the same as saying that NULLs are always propagated in expressions. For
example, the expression: (NULL=NULL OR x=y) can evaluate to TRUE or UNKNOWN
but never to FALSE. The same applies to the expression: x IN (NULL, y).
That's the ANSI / ISO standard behaviour of NULLs in three-value logic
anyway - I believe Access follows those rules.

[Steve Gerrard]

"David Portas" <***@acm.org> wrote in message news:u_6dnRO_TtqJ5XzcRVn-***@giganews.com...
| > Access, VBA, and VB have always followed a simple rule:
| > Any expression containing Null evaluates to Null.
|
| The rule is actually that a *comparison* to NULL using any of the
basic
| comparison operators (=, <>, >, <) gives a result of *UNKNOWN*. This
is not
| the same as saying that NULLs are always propagated in expressions.
For
| example, the expression: (NULL=NULL OR x=y) can evaluate to TRUE or
UNKNOWN
| but never to FALSE. The same applies to the expression: x IN (NULL,
y).
| That's the ANSI / ISO standard behaviour of NULLs in three-value logic
| anyway - I believe Access follows those rules.
|

Oops, quite right.
(Null Or True) evaluates to True, not Null.

In VB, here is the result with different operators:

(Null Or True): True
(Null XOr True): Null
(Null And True): Null

(Null Or False): Null
(Null XOr False): Null
(Null And False): False

Is that ANSI / ISO standard behaviour?

[Trevor Best]

Post by David Portas

Post by Steve Gerrard
Any expression containing Null evaluates to Null.

The rule is actually that a *comparison* to NULL using any of the basic
comparison operators (=, <>, >, <) gives a result of *UNKNOWN*. This is not
the same as saying that NULLs are always propagated in expressions. For
example, the expression: (NULL=NULL OR x=y) can evaluate to TRUE or UNKNOWN
but never to FALSE. The same applies to the expression: x IN (NULL, y).
That's the ANSI / ISO standard behaviour of NULLs in three-value logic
anyway - I believe Access follows those rules.

There is one difference in the way that SQL Server and Access treat
nulls, that is <>, e.g.

Select * from table where column <> null;

All non-null values are returned from Access/jet whereas SQL Server will
return 0 rows. IOW Access behaves as if you put:

Select * from table where column is not null;

Indeed, if you enter "<> Null" into the query grid designer in Access,
it will change it to "is not null".