473,899 Members | 3,313 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access vs SQL

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.
Jul 20 '05 #1
19 5759
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." <no@ddress.fr > wrote in message news:<c8******* ***@news.tiscal i.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.

Jul 20 '05 #2
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.
Jul 20 '05 #3
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
Jul 20 '05 #4

"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:mv******** *************** *********@4ax.c om...
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

Jul 20 '05 #5
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.ne t> wrote in message
news:91******** *************** ***@posting.goo gle.com...
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.


Jul 20 '05 #6
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.
Jul 20 '05 #7

"Dan." <no@ddress.fr > 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
Jul 23 '05 #8
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
--
Jul 23 '05 #9
(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
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

63
5987
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy in Visual Studio to create reports and labels as it's in Access?` The advantage of VS.net is that not every user needs Access, right? And that would eliminate the Access version problem as well I guess.
13
2961
by: bill | last post by:
I am trying to convince a client that dotNet is preferable to an Access project (ADP/ADE). This client currently has a large, pure Access MDB solution with 30+ users, which needs to be upgraded. I believe a dotNet solution is better, but I'm trying to be as convincing as possible -- and maybe I'm wrong! I would appreciate any input or references which could help me.
1
4358
by: Dave | last post by:
Hello NG, Regarding access-declarations and member using-declarations as used to change the access level of an inherited base member... Two things need to be considered when determining an inherited base member's access level in the derived class: its access level in the base class and the type of inheritance (public, protected, or private). After this determination is made, the following possibilities exist for manually changing the...
13
13383
by: Simon Bailey | last post by:
I am a newcomer to databases and am not sure which DBMS to use. I have a very simplified knowledge of databases overall. I would very much appreciate a (simplifed) message explaining the advantages and disadvantages of both programs. Many Thanks Simon
0
2993
by: Frederick Noronha \(FN\) | last post by:
---------- Forwarded message ---------- Solutions to Everyday User Interface and Programming Problems O'Reilly Releases "Access Cookbook, Second Edition" Sebastopol, CA--Neither reference book nor tutorial, "Access Cookbook, Second Edition" (O'Reilly, US $49.95), by Ken Getz, Paul Litwin, and Andy Baron, delivers hundreds of practical examples, up-to-date suggestions, and handy solutions to real-world problems that Access users and...
20
3377
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to speed. I like books with practical exercises, and also with test questions (like cert books) *2*
64
5293
by: John | last post by:
Hi What future does access have after the release of vs 2005/sql 2005? MS doesn't seem to have done anything major with access lately and presumably hoping that everyone migrates to vs/sql. Any comments? Thanks
1
3357
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ... www.soft30.com/download-1-11975.htm - 31k - Cached - Similar pages MDBSecure 1.0.8.0 - Soft30.com Utility which makes it easy to create secure MS Access Databases, ... MS Access 2000/2003 format. 30 day money back guarantee, 30 day trial. ...
17
4434
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting there, but is there a way they can find out if that application was put there from a CD or email or created at work? Hint: It's not on a client/server database, just native jet database mdb created on Access 2003 (default 2000)...
37
5290
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why would you even continue to use Access as a backend when you have a much superior option in SQL express? What about as a future front-end development tool? Let's get serious. Microsoft continues to publish numerous articles and videos on how you...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10863
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9666
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4300
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.