Re: when to use SQL??
John W. Vinson/MVP
database engine is very fast and competent. Some other issues to consider
are number of users (Access can easily handle some thirty or forty
concurrent updating users, and a hundred or so read-only) and system
architecture (Access is best on a fast, stable LAN and does not "play nice"
over a WAN or over the internet).
For a small business, at one site, I'd say Access is the application of
choice.
"instauratio" <insta...@discussions.microsoft.com> wrote in message
news:B9EE4168-C26B-4892...@microsoft.com...
>I don't know a lot about databases - I believe that there are choices
> sometimes between Microsoft Access and "SQL". I currently have customer
> that
> has 1800 records (address, case history, social services data...ect.).
> They
> are interested in moving to Access.
>
> Because they will be getting a Microsoft Small Business Server soon, I
> have
> mentioned that one version comes with SQL. Is is possible that I'm
> steering
> them wrong. Can someone please give me advice or comment on the direction
> I
> could or should take?
> Thanks
'69 Camaro
>I don't know a lot about databases - I believe that there are choices
> sometimes between Microsoft Access and "SQL". I currently have customer that
> has 1800 records (address, case history, social services data...ect.). They
> are interested in moving to Access.
This is a good reason to network with other specialists/consultants. When a
customer asks questions that are outside your area of expertise, know which
expert to ask or which expert to recommend to your customer.
1800 records is a small database, which Access can handle and other
client/server databases are probably overkill. However, there are many other
considerations besides the present size, such as:
1.) Is it mission-critical? Does the customer need the database up and
running during all business hours? Would revenues be lost if the database
was off-line for half an hour or more while the Access developer restored
from the backup? If so, a client/server database is the better choice. If
the customer can deal with restoring from a backup of the file from the night
before, then Access is acceptable.
2.) Does it require that all transactions (record entries) be correct and
recoverable? If Access fails, the last entries may not have been saved.
Client/server databases use transaction logs to either roll back or roll
forward transactions so that the data is consistent. Access doesn't have
this capability. For example, if an entry were made to delete $100 from a
savings account and add $100 to a checking account, but the power went out
before the second part of the transaction executed, the client/server could
recover to a point just before this transaction started so that the $100
would still be in the savings account. Access would have $100 missing from
the savings account and it never would be transferred to the checking
account. Clearly, the client/server would be the better choice in this case.
3.) How many concurrent users will use a desktop application? Access can
handle 20-30 concurrent users if the database is designed well and there
aren't heavy update, append, and delete operations. If the database has
heavy usage such as this, five or six users might be more reasonable for
sufficient performance. For read-only usage (especially Web-based apps),
Access can handle much more, possibly up to 100-200 concurrent users,
depending upon the design and complexity of the database, and the network
capabilities.
4.) Is security required? Anyone who has access to the Access database can
copy the necessary files and get the tools necessary to retrieve the data,
even if Access user-level security has been implemented. If it's
confidential data or data that needs security, then Access is not among the
choices.
5.) How big will it grow? Access 2000 and newer versions can handle 2 GB
database files. If the customer needs more room than this within the next
one to two years, then use a client/server database.
6.) How robust does it need to be? For a heavy-duty database that must be
reliable at all times, use the client/server.
Remember that in each of these cases, if a client/server database engine is
selected for data storage and data manipulation, Access can be used to
develop a front end (user interface) to the data, so the choices aren't
mutually exclusive.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
alphonse
acces
"instauratio" <insta...@discussions.microsoft.com> wrote in message
news:B9EE4168-C26B-4892...@microsoft.com...
Douglas J. Steele
someone use a stereo instead of a tuba. Yes, they both produce sounds, but
they're nothing alike.
SQL Server is a database engine. It's meant for storing data. There's no
real user interface (unless you consider having your customers create their
own queries to be a user interface...)
Access is a development environment that's specifically intended to work
with databases. It ships with two database engines (Jet and MSDE, a
"personal" version of SQL Server), but it works with many other DBMS
(including SQL Server)
Yes, a SQL Server database is more robust than a Jet database, but you still
need a front-end to use it. 1800 records is nothing to Jet, so unless you
need some of the additional features Gunny outlined (sufficient reliability
for mission-critical use, recoverability, increased concurrency, advanced
security, and the like), there would appear to be no reason to use SQL
Server.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"alphonse" <admini...@alphamaktech.net> wrote in message
news:umCke2t...@TK2MSFTNGP10.phx.gbl...
Albert D.Kallal
One more thing however:
>I believe that there are choices
> sometimes between Microsoft Access and "SQL".
It is imortant to note that you can purchase sql server, (or use the "copy"
that comes with SBS). However, sql server, like Oracle, Sybase, or most
"server" based products is ONLY a database engine.
That menas that programs writeen in VB, or you write a systsem on your web
server, and it connects to sql server. And, of couse, you can also build
the appcation in ms-access, and it can also connect to sqlw server.
I guess I am tyring to point out that ms-access, or sql server is not a
either/or choice.
You can choose to use sql server, but what will you build the applcation
part with (forms, reports, write code etc.). Sql server is a data store
system, but is NOT a software develpument system. You CAN NOT build a data
entry form with sql server. (you have to use VB, or ms-access, or your web
server to do that part).
So, keep in mind that the issue of your software devfelpument tools are a
seperate issue of that of your database system that you choose to USE WITH
your software.
Albert D.Kallal
You got some great answers here.
One more thing however:
>I believe that there are choices
> sometimes between Microsoft Access and "SQL".
It is important to note that you can purchase sql server, (or use the "copy"
that comes with SBS). However, sql server, like Oracle, Sybase, or most
"server" based products is ONLY a database engine.
That means that programs written in VB, or you write a system on your web
server, and it connects to sql server. And, of course, you can also build
the application in ms-access, and it can also connect to sql server.
I guess I am trying to point out that ms-access, or sql server is not a
either/or choice.
You can choose to use sql server, but what will you build the application
part with (forms, reports, write code etc.). Sql server is a data store
system, but is NOT a software development system. You CAN NOT build a data
entry form with sql server. (you have to use VB, or ms-access, or your web
server to do that part).
So, keep in mind that the issue of your software development tools are a
separate issue of that of your database system that you choose to USE WITH
your software.