Solved

ACCESS DATABASE VS SQL FOR WEB APPLICATION

Posted on 1998-07-25
3
195 Views
Last Modified: 2010-03-19


    The intent of the project was to offer to web users for a membership fee the possiblity
     to customize their own site. Few customization features were being offered:
    Background selection, main picture selection among 7, Personnal Header selection,
    selection of one or more of the 14 database pictures collection available.

    The project has been developped using Internet Database Connector (IDC/HTX) as well as
     MS ACCESS 97 for the database application.

    The project intended purposes was to generate dynamic content to multiple web users.
    The project is web-based and is suppose to handle massive web traffic and multiple Webuser queries.
    The project was supposed to run on a NT server using IIS 3.0 as well as the appropriate ODBC
    Drivers.


    We already consulted several microsoft experts and would like external expert opinions.
     We would very much appreciate your help on that matter.

    Here are the facts and statements that we would need confirmation on.

    - MS ACCESS  IS NOT BUILT FOR INTERNET APPLICATIONS USING EXTENSIVE AND
    SIMULTANEOUS WEB QUERIES

    - MS ACCESS USING ODBC AND IIS3.0 ON AN NT SERVER CAN NOT HANDLE MORE THAN 1
    (Some experts said 5) SIMULTANEOUS
      QUERIES WITHOUT CRASHING

    - NT SERVERS REQUIRE AN AUTHENTICATION OF A WEB MEMBER , AT EACH PAGE.  i.e EACH
    TIME A MEMBER ACCESSES
      ANY PARTICULAR PAGE INSIDE THE MEMBERS SECTION OF THE SITE, HIS IDENTIY WOULD BE
    CHECKED AND ONE
      QUERY WOULD BE MADE TO THE DATABASE.

    - NO MORE THAN ONE USER  CAN QUERY THE MS ACCESS DATABASE AT A TIME. (CONCEPT
    OF DATA UPSIZING).
    - THE RESOURCES REQUIRED TO HANDLE A WEB-BASED  DEMAND WOULD BE SO HIGH IN CPU
    USAGE THAT THE SERVER
       WOULD CONSTANTLY CRASH.

    - THE MS ACCESS DATABASE WOULD BE UNABLE TO PROCESS THE WEB QUERIES AS SOON
    AS ONLY FEW MEMBERS WILL BE USING
      THE SERVICE.

    - ONLY AN SQL SERVER IS ABLE TO HANDLE MULTIPLE QUERIES FROM MANY WEB USERS.

    - TRANSFERING THE DATABASE TO A SQL SERVER IS A MUCH MORE EXPENSIVE OPERATION
    WHICH PRICE IS BASED ON A
      PREDETERMINED NUMBER OF SIMULTANEOUS USER.
     

    => THE CHOICE OF USING MS ACCESS 97 AS DATABASE APPLICATION, NT SERVER RUNNING
    IIS 3.0 ALONG WITH ACCESS ODBC
          DRIVER AS THE SERVER AND DATABASE CONNECTIVITY AND CHOOSING IDC/HTX TO
    GENERATE THE DYNAMIC CONTENT,
          DOOMED THE FEASIBILITY OF THE PROJECT RIGHT FROM THE START.

    - ACCORDING TO ALL THE EXPERTS CONSULTED  REGARDING THAT MATTER  IT WAS
    IMPOSSIBLE FOR THE PROJECT TO WORK
      USING THE ABOVE PROGRAMMING,  SOFTWARE APPLICATION AND SERVER
    CONFIGURATION.


    Thank you for your prompt reply.
0
Comment
Question by:jeffos
3 Comments
 
LVL 3

Accepted Solution

by:
altena earned 250 total points
ID: 1089228
- MS ACCESS  IS NOT BUILT FOR INTERNET APPLICATIONS USING
  EXTENSIVE AND SIMULTANEOUS WEB QUERIES.

The statement in itself is true. However that does not mean
that Access cannot be a usefull component in an internet
application.

- MS ACCESS USING ODBC AND IIS3.0 ON AN NT SERVER CAN NOT HANDLE
 MORE THAN 1 (Some experts said 5) SIMULTANEOUS QUERIES WITHOUT
 CRASHING

This is complete b.s. A well designed application using Access
can easily handle 25-50 users. The problem with access is that
it is about impossible to find decent (do not even look for "good") developers.

- NT SERVERS REQUIRE AN AUTHENTICATION OF A WEB MEMBER , AT EACH PAGE i.e EACH TIME A MEMBER ACCESSES ANY PARTICULAR PAGE INSIDE THE MEMBERS SECTION OF THE SITE, HIS IDENTIY WOULD BE CHECKED AND ONE QUERY WOULD BE MADE TO THE DATABASE.

This is another nonsense statement. Please refer to the IIS documentation.

- NO MORE THAN ONE USER  CAN QUERY THE MS ACCESS DATABASE AT A TIME. (CONCEPT OF DATA UPSIZING).

This statement is not true, No more than one user can lock one
table at one time. Perhaps the statement should be rephrased as:
MS-SQL and Oracle databases can handle a larger client load than MS-Access.

- THE RESOURCES REQUIRED TO HANDLE A WEB-BASED  DEMAND WOULD BE SO HIGH IN CPU USAGE THAT THE SERVER WOULD CONSTANTLY CRASH.

In a poorly written application: Yes. New question: Is this the fault of the server?

- THE MS ACCESS DATABASE WOULD BE UNABLE TO PROCESS THE WEB QUERIES AS SOON AS ONLY FEW MEMBERS WILL BE USING THE SERVICE

Again, this depends on the quality of the application.


- ONLY AN SQL SERVER IS ABLE TO HANDLE MULTIPLE QUERIES FROM MANY WEB USERS.

Also bonanza: a poor;y designed application will choke both
SQL-Server And MS-Access. Albeit: Access will crash sooner.

- TRANSFERING THE DATABASE TO A SQL SERVER IS A MUCH MORE EXPENSIVE OPERATION WHICH PRICE IS BASED ON A PREDETERMINED NUMBER OF SIMULTANEOUS USER.

True, If the developer does not know what he/she is doing.
It is very well possible to build a high performance solution
that requires limited client connections.
Again (You should get the message by now) This depends on
the expertise of your developers.

=> THE CHOICE OF USING MS ACCESS 97 AS DATABASE APPLICATION, NT SERVER RUNNING IIS 3.0 ALONG WITH ACCESS ODBC DRIVER AS THE SERVER AND DATABASE CONNECTIVITY AND CHOOSING IDC/HTX TO
GENERATE THE DYNAMIC CONTENT, DOOMED THE FEASIBILITY OF THE PROJECT RIGHT FROM THE START.

This is the kind of explanation you get from bad developers.
period. If your developers were soo smart, they should have seen it coming very very soon. (i.e. huge problems in their first prototype) If the design was "decent" (again, not good or brilliant) they could have proposed to switch ti SQL-Server without too much trouble.

- ACCORDING TO ALL THE EXPERTS CONSULTED  REGARDING THAT MATTER  IT WAS  IMPOSSIBLE FOR THE PROJECT TO WORK USING THE ABOVE PROGRAMMING,  SOFTWARE APPLICATION AND SERVER CONFIGURATION.

You definitely need new experts. (Or the information you gave in
the questions/remarks is not correct).

What I read here is a classic case of bad management and development. Obviously: The easy way out is to blame it all on a bad initial choice: "Access for such an App will NEVER work, The project was doomed from the start". That explanation is too little, too late.

This about answers your points I think. If you are interested in a real "post-morten-review" of the solution-design, database design and any code that may have been produced. (If only to adjust the "expert" status of some individuals) Then feel free to start a dialog by sending me e-mail at: AltenaR@Yahoo.com

0
 

Author Comment

by:jeffos
ID: 1089229
Thank for your comments
0
 
LVL 1

Expert Comment

by:mativare
ID: 1089230
Access alias name is "desktop" database, it is not ready for multiuser environment, but altena is right too top developer
can use access nicely using DAO/ODBC concurrency.
but to be short, my opinion is do not even think about using Access
I like to let you know that Office 2000 beta is available right now, new Access 2000 can save files directly into MSSQL server they are first time compatible right now. Perhaps this helps you when you design your future application and you must commit less
transformation job. My point is if you move your data tables
from access 97 to MSSQL Server using MSSQL Server Beta data transformation services you lose all indexes and
relationships.
Try upgrade from access 97 to Access 2000 first. and then save into MSSQL server
Hope this gives you some new idea.


0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
SQL 2012 and SQL 2014 in memory database 11 32
Report Builder 9 31
sql calculate averages 18 32
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now