Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    9

    Unanswered: SQL vs Access Database: Advice Needed Please

    Hi,

    I am just beginning to look at moving our Access 2000 database(s) to SQL server.

    The Access systems we use are back end server databases and front end clients across our workgroup, 5 to 7 users using system 5 days a week, around 10 hours a day.

    All Systems are multi-table. Also I have created a intranet for the company and within that, I have a number of ASP pages via IIS4 that access these databases via DSN link, around 40 sites and 120 computers have access to the ASP pages. Can view and insert data in the databases. (Infrequently at the minute may change soon)

    One database holds around 130,000 records.

    I have been asked to create a new system for the 40 sites, 120 computers to work from, Do I stick with Access or move to SQL and for what reasons?

    The front end I am OK with, its what benefits will SQL backend bring.

    Hope this information helps.

    Many Thanks

    Darrell..

  2. #2
    Join Date
    Aug 2002
    Posts
    20
    Hi,

    I’m not good at Access but here is some reason why you should choose SQL server.
    * With “one” sql database you take just a backup from one source, instead of many.
    * You can take backup on the transactions logs.
    * Sql is a transaction based service but Access is a “file-based” system in it’s own runtime environment.
    * You can “trace” activity on SQL server. To be honest I don’t know if you can do that with Access.
    * The amount you write about (130,000 rows, 120 PC’s connected…) is nothing unusual for a sql server. You may get problem but that is depending on database design and/or hardware in that case.

    /Mats

  3. #3
    Join Date
    Sep 2002
    Posts
    3
    Can you be more specific about the front end? Are you using the data access pages that ship with access 2000 for the web deployment, or a traditional ASP/ADO connectoin to a file server w/Access?

    Of course, the main advantage is that SQL server is a true server database. As you know, Access (technically, Jet database - you are only really using Access when you invoke some Access object, like a querydef, form, report...) is a file that the client actually executes. When Using ASP/ADO then the server (IIS) is doing all the work (serving the pages and the data-related work). If you have SQL server, then the SQL server will assume the data work and IIS will handle the web pages -- in a nutshell.

    SQL server will handle multiple users much much much better than will Access if you are using multi-user Access forms as a front end with bound controls. Row/page locks are much better with SQL server, plus you get security, triggers, too much to go into.

    Joe

  4. #4
    Join Date
    Nov 2002
    Posts
    1
    Can you justify paying for MS SQL and do you have the hardware to support it, and pay for development time to change DB backends? Following URL shows some differences between Access and SQL.

    http://www.microsoft.com/sql/techinf.../SQLAccess.asp

    If you are wanting more DB ability then goto something more powerful. Look at MySQL if you want to be cheap and get a lot of help else stay with Access. Also might look at legal requirements using MSDE.

  5. #5
    Join Date
    Sep 2002
    Posts
    53

    Anything but access

    Originally posted by hjb_3
    Can you justify paying for MS SQL and do you have the hardware to support it, and pay for development time to change DB backends? Following URL shows some differences between Access and SQL.

    http://www.microsoft.com/sql/techinf.../SQLAccess.asp

    If you are wanting more DB ability then goto something more powerful. Look at MySQL if you want to be cheap and get a lot of help else stay with Access. Also might look at legal requirements using MSDE.
    As a contract web developer I run across this question quite frequently. My personal recommendation is to use anything but Access that is assuming that the company can afford other alternatives and that they have someone to maintain the alternative that they choose.

    Many times there are items that are not considered in this decision, such as: Data Security, Processing speed, Connection Pooling, Data Storage Requirements, Data Backups, Licensing costs and the list goes on. However, most of the time one was chosen for one of the specific reasons above but not all factors were equally weighed and a problem normally arises due to that fact.

    There are many reasons I say anything other then Access but here are the main reasons (I have not checked recently if these bugs/problems have been resolved so this should still be researched.) If not properly secured on the server a user could easily pull down the database (download) and exposing sensitive company information. Access does NOT use an execution plan or stored procedures so if this will eventually be a heavily trafficed site then you are going to experience bottlenecking issues at that point. Access used to have a size limit of 2gig and once it hit that it froze (do not know if this was ever resolved). Concurrency issues are very common in Access driven client/server applications especially in web applications where they did not explicitly use a disconnected recordset or depending on the type of insert/update statements are used, if a connection were not properly closed or the connection object wasn't destroyed. Access does not really have any sort of backup procedures. The list continues to go on but all in all Access was never designed to be a client/server backend and that is why microsoft offers other alternatives and there are many that are better alternatives than Access.

    Good luck and I hope this helped!
    KDK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •