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]

Re: Access vs SQL

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." <no@ddress.fr > wrote in message news:<c8hugi$sv 6$2@news.tiscal i.fr>...[color=blue]
> 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.[/color]

[John Shaw]

Re: Access vs SQL

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]

Re: Access vs SQL

jmshaw@weir.net (John Shaw) wrote:
[color=blue]
>I guess the biggest difference is that Access is single-threaded. If
>two users hit it at the same time, it has problems.[/color]

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.
[color=blue]
>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.[/color]

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

Re: Access vs SQL


"Tony Toews" <ttoews@teluspl anet.net> wrote in message
news:mvsfb09o57 hbku3cv9f6lnacl hnm8gllpe@4ax.c om...[color=blue]
> jmshaw@weir.net (John Shaw) wrote:
>[color=green]
> >I guess the biggest difference is that Access is single-threaded. If
> >two users hit it at the same time, it has problems.[/color]
>
> But the Access back end is on a file server? It's a shared file.[/color]
Multiple users can[color=blue]
> update it at the same time just fine.
>[color=green]
> >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.[/color]
>
> What do you mean by killed it? Was this postal address scrubber written[/color]
in Access or[color=blue]
> what?[/color]

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.

[color=blue]
>
> 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[/color]

[david epsom dot com dot au]

Re: Access vs SQL

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" <jmshaw@weir.ne t> wrote in message
news:91422298.0 405281610.6f22a 1a3@posting.goo gle.com...[color=blue]
> 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.[/color]

[Ryan]

Re: Access vs SQL

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]

Re: Access vs SQL


"Dan." <no@ddress.fr > wrote in message news:c8hugi$sv6 $2@news.tiscali .fr...[color=blue]
> 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[/color]
i[color=blue]
> used that and have used that since. but then i heard something about sql[/color]
and[color=blue]
> 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.
>
>[/color]
SQL is not a database it is a language. (Structured Query Language)
SQLServer is a database. Don't confuse the two.
Jim

[David Portas]

Re: Access vs SQL

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]

Re: Access vs SQL

(non existant groups removed from x-post)

David Portas wrote:[color=blue]
> http://www.aspfaq.com/show.asp?id=2214[/color]

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]

Re: Access vs SQL

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]

Re: Access vs SQL


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:cP6dncagQZ 1LznzcRVn-hA@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]

Re: Access vs SQL

> Access, VBA, and VB have always followed a simple rule:[color=blue]
> Any expression containing Null evaluates to Null.[/color]

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]

Re: Access vs SQL


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:u_6dnRO_Tt qJ5XzcRVn-gw@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]

Re: Access vs SQL

David Portas wrote:[color=blue][color=green]
>>Access, VBA, and VB have always followed a simple rule:
>>Any expression containing Null evaluates to Null.[/color]
>
>
> 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.[/color]

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