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.

"Dan." <[e-mail]> wrote in message news:<c8**********@news.tiscali.fr>...

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]

jm****@weir.net (John Shaw) wrote:

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.
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\)]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:mv********************************@4ax.com...

jm****@weir.net (John Shaw) wrote:

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.

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.


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

[david epsom dot com dot au]

Access is not single threaded. In fact, one of the many problems
that users have with Access is that some applications (eg IIS)
have problems dealing with a multi-threaded process such as the
Jet database engine.

Jet was designed to operate as a partially distributed database
engine. If you need to operate Jet as central database engine
you should increase the number of threads. If you need to operate
Jet as a central database engine in a context that can not handle
thread creation, you should reduce the number of threads to 1.

(david)
"John Shaw" <jm****@weir.net> wrote in message
news:91**************************@posting.google.c om...

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.

[Ryan]

It's difficult to say exactly which is better as it depends on the
task you want to do.

I've been using both Access and SQL for several years and generally
use Access for smaller applications (one off's) with low numbers of
users. (such as the one on www.ryan.dial.pipex.com - my own stuff
which I'm working on at the moment - as you will see, you can do quite
a lot with it) This isn't because of any limitations of Access, but a
preference to keep our main apps (at work) consistent. SQL is used for
anything likely to be a permanent release and with lots of users. For
instance SQL handles record locking better IMHO. Access is easy to
develop in very quickly with limited knowledge (and powerful if you
know what you are doing).

Access queries will provide you with SQL statements which are almost
right for using in SQL itself. It doesn't take long to learn what
changes you may need to make as they are only subtle differences. I
prefer SQL as I can lay out the code in a neater manner, but Access
has a better graphical front end.

SQL is your back end and Access can be both the front and back end or
you can use a mix (Access front end, SQL back end). This is a nice
compromise if development costs are low (ish).

I found Access to be very useful in learning about database
programming and coding in VBA. It's all in one package so easy to work
with.

SQL can be a little harder, but it's well worth the effort. Once you
get up to speed and comfortable with it, you should find it better
than Access, but will still need a development language. I use Delphi
as my main development language and the two work well together.

The good thing about SQL is that there are a lot of people willing to
help out on this forum. The Access one seems to have been taken over
so I don't bother with it any more.

[Jim Kennedy]

"Dan." <[e-mail]> wrote in message news:c8**********@news.tiscali.fr...

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

--
David Portas
SQL Server MVP
--

[Trevor Best]

(non existant groups removed from x-post)

David Portas wrote:

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.

--
This sig left intentionally blank

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

--
David Portas
SQL Server MVP
--

[Steve Gerrard]

"David Portas" <RE****************************@acm.org> wrote in message
news:cP********************@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]

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

--
David Portas
SQL Server MVP
--

[Steve Gerrard]

"David Portas" <RE****************************@acm.org> wrote in message
news:u_********************@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]

David Portas wrote:

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.

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

--
This sig left intentionally blank

[Erland Sommarskog]

Trevor Best (no****@besty.org.uk) writes:

David Portas wrote:

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.

An even funnier thing, is that SQL Server can behave as described for
Access above. These days you need to apply a SET command for this, but
originally, this was default behaviour for SQL Server.

Access I don't know anything about, so I believe anything you say.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Trevor Best]

Erland Sommarskog wrote:

Trevor Best (no****@besty.org.uk) writes:

David Portas wrote:

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.

An even funnier thing, is that SQL Server can behave as described for
Access above. These days you need to apply a SET command for this, but
originally, this was default behaviour for SQL Server.

Access I don't know anything about, so I believe anything you say.

In that case you owe me $500 <g>

AFAIK no previous version of Access has behaved like that either, I
started on version 2 so I can't speak for 1.0 or 1.1. I did skip 95 and
2000, which were truly dreadful versions but I can't imagine they would
have either, unless that was one of the many bugs in them :-)

--
This sig left intentionally blank

[david epsom dot com dot au]

Note: Access/Jet SQL with "=Null" is normally converted to
"is null" when you open a query in design view.

For simple queries '[fld]=null' returns the same values
in Access as '[fld] is null', but the operator precedence
is different, so an Access query that uses "=null" may
behave differently after accidental conversion.

(david)

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Trevor Best (no****@besty.org.uk) writes:

David Portas wrote:

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.

An even funnier thing, is that SQL Server can behave as described for
Access above. These days you need to apply a SET command for this, but
originally, this was default behaviour for SQL Server.

Access I don't know anything about, so I believe anything you say.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Trevor Best]

david epsom dot com dot au wrote:

Note: Access/Jet SQL with "=Null" is normally converted to
"is null" when you open a query in design view.

For simple queries '[fld]=null' returns the same values
in Access as '[fld] is null', but the operator precedence
is different, so an Access query that uses "=null" may
behave differently after accidental conversion.

No, if you put "Null" in the criteria box, it will convert to "is null"
and "not null" is converted to "is not null" but "=null" will not be
converted for some reason.

--
This sig left intentionally blank

[david epsom dot com dot au]

There may be a difference in different versions of Access.

If I put "null" in the criteria box, it is immediately corrected
to "Is Null"

If I put "=null" in the SQL of a saved querydef, it is shown as
"Is Null" if the query is opened in design view. If the query
is saved in View mode, the SQL will be converted in the saved
copy. If the query is saved in SQL mode, or not saved, the original
SQL will remain.

If I put "=null" in the criteria box, it creates the SQL phrase
"Is Null". If the querydef is saved and re-opened or if the view
is switched between SQL and View mode, the view is re-created from
the SQL, and shows "Is Null"

More significantly, I had forgotten that the behaviour I
describe for '[fld]=null' is Jet 3.51. Using Jet 4.0,
'[fld]=null' returns null, rather than True.

The only useful point to take from this is that "=Null" should
not be used in Jet SQL :)

(david)

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...

david epsom dot com dot au wrote:

Note: Access/Jet SQL with "=Null" is normally converted to
"is null" when you open a query in design view.

For simple queries '[fld]=null' returns the same values
in Access as '[fld] is null', but the operator precedence
is different, so an Access query that uses "=null" may
behave differently after accidental conversion.

No, if you put "Null" in the criteria box, it will convert to "is null"
and "not null" is converted to "is not null" but "=null" will not be
converted for some reason.

--
This sig left intentionally blank