Access vs. MSDE vs. SQL Server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 15 of 15

Thread: Access vs. MSDE vs. SQL Server

  1. #1
    John Meyer Guest

    Access vs. MSDE vs. SQL Server

    Let's say that I am developing an intranet site where one group of people
    needs to edit the information, and another needs to just print the
    information. Maxinum people that would hit the database is about 40, but
    since most will work on shifts, that could be taken down a little bit,
    _plus_ there is the chance that the people at the top would like to access
    the information through Access. In that case, which should I use: Access,
    MSDE, or SQL Server.

  2. #2
    john Guest

    Re: Access vs. MSDE vs. SQL Server

    SQL SERVER! I STRONGLY advise against using Access...though I'm sure you'll
    hear of some wonderful success stories using Access (though they are few and
    far between). To avoid future headaches, sql server is the best choice.




    "John Meyer" <john_meyer@geocities.com> wrote in message
    news:Xns910A91CD19ABBjohnmeyergeocitiesco@209.1.14.192...
    > Let's say that I am developing an intranet site where one group of people
    > needs to edit the information, and another needs to just print the
    > information. Maxinum people that would hit the database is about 40, but
    > since most will work on shifts, that could be taken down a little bit,
    > _plus_ there is the chance that the people at the top would like to access
    > the information through Access. In that case, which should I use: Access,
    > MSDE, or SQL Server.




  3. #3
    John Meyer Guest

    Re: Access vs. MSDE vs. SQL Server

    "john" <johnny> wrote in news:3b8ab21e@news.devx.com:

    > SQL SERVER! I STRONGLY advise against using Access...though I'm sure
    > you'll hear of some wonderful success stories using Access (though they
    > are few and far between). To avoid future headaches, sql server is the
    > best choice.



    Actually, I have heard of those nightmare stores: I experienced several of
    them up front. What I really wanted to know is whether or not MSDE would
    be an interim choice.

  4. #4
    Paul Mc Guest

    Re: Access vs. MSDE vs. SQL Server


    G'day.

    MSDE is a good interim choice, however I believe that it has built in limitations
    to encourage movement up the SQL Server proper, such as a performance governer
    that limits performance when 5+ batches are executed concurrently, as well
    as a 2gb max db size...

    Cheers,
    Paul

  5. #5
    G. Allen Guest

    Re: Access vs. MSDE vs. SQL Server


    I've had great luck using MSDE in a situation very similar to what you've
    described and would highly recommend it. I agree with the previous comments
    made about Access: It's a great desktop database tool but is unsuited to
    a multi-user/intranet/Internet type environment.


    john_meyer@geocities.com (John Meyer) wrote:
    >"john" <johnny> wrote in news:3b8ab21e@news.devx.com:
    >
    >> SQL SERVER! I STRONGLY advise against using Access...though I'm sure
    >> you'll hear of some wonderful success stories using Access (though they
    >> are few and far between). To avoid future headaches, sql server is the
    >> best choice.

    >
    >
    >Actually, I have heard of those nightmare stores: I experienced several

    of
    >them up front. What I really wanted to know is whether or not MSDE would


    >be an interim choice.



  6. #6
    Conrad Muller Guest

    Re: Access vs. MSDE vs. SQL Server


    "Paul Mc" <paulmc@nospam.thehub.com.au> wrote:
    >
    >G'day.
    >
    >MSDE is a good interim choice, however I believe that it has built in limitations
    >to encourage movement up the SQL Server proper, such as a performance governer
    >that limits performance when 5+ batches are executed concurrently, as well
    >as a 2gb max db size...


    Access is two separate applications. One is a database client development

    environment. The other is the database engine. There is no problem using

    Access as a client with any popular database engine. Access comes with MSDE

    and the JET database engine. For 40 users, I would recommend SQL Server,

    but there is no problem using Access as the client to any of the servers.

    Access is a quick way to build or prototype a user interface.

    Access has a usable query tool, and wizards to make forms easy. If your users

    want to use Access, help them link to your database server (with appropriate

    security) and you or they can quickly build a client to access the data.


    Conrad


  7. #7
    Pauli Porkka Guest

    Re: Access vs. MSDE vs. SQL Server


    john_meyer@geocities.com (John Meyer) wrote:
    >Let's say that I am developing an intranet site where one group of people


    >needs to edit the information, and another needs to just print the
    >information. Maxinum people that would hit the database is about 40, but


    >since most will work on shifts, that could be taken down a little bit,
    >_plus_ there is the chance that the people at the top would like to access


    >the information through Access. In that case, which should I use: Access,


    >MSDE, or SQL Server.


    If the money is not an object then SQL Server, but MSDE otherwise. We use
    MSDE with our applications for those customers which do not have SQL Server
    installed.
    The really great part is that you can just backup MSDE (both the MSDE 7.0
    and the SQL Server 2000 Desktop Engine) and just restore it to SQL Server
    if you decide to upgrade. Really easy and I believe it does have plenty of
    power for the scenario you described there. How many of those 40+some people
    actually make queries at the same time, and if the database keeps itself
    under 2gb ... well i'd go for the MSDE unless you have money to spend around,
    with msde you can put the money saved in SQL Server purchase to the hardware.



  8. #8
    Phil Guest

    Re: Access vs. MSDE vs. SQL Server


    "Pauli Porkka" <pporkka@iki.fi> wrote:
    >
    >john_meyer@geocities.com (John Meyer) wrote:
    >>Let's say that I am developing an intranet site where one group of people

    >
    >>needs to edit the information, and another needs to just print the
    >>information. Maxinum people that would hit the database is about 40, but

    >
    >>since most will work on shifts, that could be taken down a little bit,


    >>_plus_ there is the chance that the people at the top would like to access

    >
    >>the information through Access. In that case, which should I use: Access,

    >
    >>MSDE, or SQL Server.

    >
    >If the money is not an object then SQL Server, but MSDE otherwise. We use
    >MSDE with our applications for those customers which do not have SQL Server
    >installed.
    >The really great part is that you can just backup MSDE (both the MSDE 7.0
    >and the SQL Server 2000 Desktop Engine) and just restore it to SQL Server
    >if you decide to upgrade. Really easy and I believe it does have plenty

    of
    >power for the scenario you described there. How many of those 40+some people
    >actually make queries at the same time, and if the database keeps itself
    >under 2gb ... well i'd go for the MSDE unless you have money to spend around,
    >with msde you can put the money saved in SQL Server purchase to the hardware.
    >
    >

    If you use either MSDE or SQL for the back end and Access for the front,
    use the Access ADP or you will have the jet engine in the mix.
    That means an ODBC layer instead of just OLE-DB for your ADO connection.

  9. #9
    Graham O'Riley Guest

    Re: Access vs. MSDE vs. SQL Server


    john_meyer@geocities.com (John Meyer) wrote:
    >Let's say that I am developing an intranet site where one group of people


    >needs to edit the information, and another needs to just print the
    >information. Maxinum people that would hit the database is about 40, but


    >since most will work on shifts, that could be taken down a little bit,
    >_plus_ there is the chance that the people at the top would like to access


    >the information through Access. In that case, which should I use: Access,


    >MSDE, or SQL Server.



    Some what unrelated but I'm sure you'll have an answer for us all.

    When using an Access project to access an sql server, some
    tables are read only and others can be updated. So we thought to
    change the properties for the user, but this has NO effect.

    We're totally confused and totally lost.

    We use access2000 projects and sql 7 sp2.

    Thanks in advance.

    Graham.


  10. #10
    Bruce Cassidy Guest

    Re: Access vs. MSDE vs. SQL Server


    "Graham O'Riley" <Graham.ORiley@netdocs.co.uk> wrote:
    >
    >john_meyer@geocities.com (John Meyer) wrote:
    >>Let's say that I am developing an intranet site where one group of people

    >
    >>needs to edit the information, and another needs to just print the
    >>information. Maxinum people that would hit the database is about 40, but

    >
    >>since most will work on shifts, that could be taken down a little bit,


    >>_plus_ there is the chance that the people at the top would like to access

    >
    >>the information through Access. In that case, which should I use: Access,

    >
    >>MSDE, or SQL Server.

    >
    >
    >Some what unrelated but I'm sure you'll have an answer for us all.
    >
    >When using an Access project to access an sql server, some
    >tables are read only and others can be updated. So we thought to
    >change the properties for the user, but this has NO effect.
    >
    >We're totally confused and totally lost.
    >
    >We use access2000 projects and sql 7 sp2.
    >
    >Thanks in advance.
    >
    >Graham.
    >



    Graham,

    What you describe is usually due to Access' dependence on having a primary
    or unique key for a table before it can make it other than read only. For
    your "read only" tables, make sure they have a unique index (most modern
    database designs require a primary index for every table).

    Once you have done that, delete the table links, then recreate them, making
    sure that if Access asks for a unique key on the table, you specify the columns
    that are in the unique key. (normally if the unique index or primary key
    is present, it won't ask.) Don't relink the tables, as this won't change
    the indexing criteria in Access.

    Once you have done that, your read only tables should be read/write.

    Best of luck!

  11. #11
    Ed Bright Guest

    Re: Access vs. MSDE vs. SQL Server


    "Graham O'Riley" <Graham.ORiley@netdocs.co.uk> wrote:
    >
    >john_meyer@geocities.com (John Meyer) wrote:
    >>Let's say that I am developing an intranet site where one group of people

    >
    >>needs to edit the information, and another needs to just print the
    >>information. Maxinum people that would hit the database is about 40, but

    >
    >>since most will work on shifts, that could be taken down a little bit,


    >>_plus_ there is the chance that the people at the top would like to access

    >
    >>the information through Access. In that case, which should I use: Access,

    >
    >>MSDE, or SQL Server.

    >
    >
    >Some what unrelated but I'm sure you'll have an answer for us all.
    >
    >When using an Access project to access an sql server, some
    >tables are read only and others can be updated. So we thought to
    >change the properties for the user, but this has NO effect.
    >
    >We're totally confused and totally lost.
    >
    >We use access2000 projects and sql 7 sp2.
    >
    >Thanks in advance.
    >
    >Graham.
    >


    In order to guarantee tables are updatable, there must be a primary key.
    Also, if you are using Access to reach SQL Server tables via a DSN, be sure
    to answer the question about what field is the unique identifier for each
    table or Access won't know what to use as a primary key. If it can't uniquely
    identify records, you can't always update them.

    -Ed Bright


  12. #12
    Bob Dean Guest

    Re: Access vs. MSDE vs. SQL Server


    Paul,

    I'm in exactly this position now: migrating my app from Access to either
    SQL or MSDE (or both). I'm very unclear about the nebulous constraint Microsoft
    describes for MSDE. I think it is much as you describe it: e.g. 5 concurrent
    "batches". What does this mean? Suppose I have 10 users and they all are
    updating different records in different tables in the same db at about the
    same time. Is MSDE going to bomb? Will it handle their queries? This is important
    because--like others--I gave a lot of my customers the expectation that Access
    would solve their problem. If I can substitute MSDE (free), fine. But if
    I have to get them to buy SQL server, that could be a problem. Thanks in
    advance for any advice on this.

    Bob


    "Paul Mc" <paulmc@nospam.thehub.com.au> wrote:
    >
    >G'day.
    >
    >MSDE is a good interim choice, however I believe that it has built in limitations
    >to encourage movement up the SQL Server proper, such as a performance governer
    >that limits performance when 5+ batches are executed concurrently, as well
    >as a 2gb max db size...
    >
    >Cheers,
    >Paul



  13. #13
    Istvan Lorincz Guest

    Re: Access vs. MSDE vs. SQL Server


    Hi,
    The best choice SQL server over 5 user. MSDE is OK for 1 -4 user, cheaper
    then SQL server. And there is no reason to use Access.
    Istvan
    "Paul Mc" <paulmc@nospam.thehub.com.au> wrote:
    >
    >G'day.
    >
    >MSDE is a good interim choice, however I believe that it has built in limitations
    >to encourage movement up the SQL Server proper, such as a performance governer
    >that limits performance when 5+ batches are executed concurrently, as well
    >as a 2gb max db size...
    >
    >Cheers,
    >Paul



  14. #14
    David Jones Guest

    Re: Access vs. MSDE vs. SQL Server


    There are some specific things that Microsoft has said about MSDE vs SQL Server,
    but they relate to functionality (i.e. no OLAP) and Database size limitations.
    Microsoft has never gone on-the-record giving an upper limit of the number
    of current users that MSDE will support, or the speed of MSDE after 5 users.
    ALL they say is that MSDE is tuned / optimised etc for 5 users max.

    In my organisation (we ship a vertical application on SQL Server and MSDE)
    we stick to 5 users maximum on MSDE.
    My advice is to try it out (and maybe warn the users / CFO first)

    David Jones
    CARDIFF
    UK


    "Bob Dean" <rcdean@juno.com> wrote:
    >
    >Paul,
    >
    >I'm in exactly this position now: migrating my app from Access to either
    >SQL or MSDE (or both). I'm very unclear about the nebulous constraint Microsoft
    >describes for MSDE. I think it is much as you describe it: e.g. 5 concurrent
    >"batches". What does this mean? Suppose I have 10 users and they all are
    >updating different records in different tables in the same db at about the
    >same time. Is MSDE going to bomb? Will it handle their queries? This is

    important
    >because--like others--I gave a lot of my customers the expectation that

    Access
    >would solve their problem. If I can substitute MSDE (free), fine. But if
    >I have to get them to buy SQL server, that could be a problem. Thanks in
    >advance for any advice on this.
    >
    >Bob
    >
    >
    >"Paul Mc" <paulmc@nospam.thehub.com.au> wrote:
    >>
    >>G'day.
    >>
    >>MSDE is a good interim choice, however I believe that it has built in limitations
    >>to encourage movement up the SQL Server proper, such as a performance governer
    >>that limits performance when 5+ batches are executed concurrently, as well
    >>as a 2gb max db size...
    >>
    >>Cheers,
    >>Paul

    >



  15. #15
    Euan Garden Guest

    Re: Access vs. MSDE vs. SQL Server

    The limitation is stated as 5 concurrent workloads, this level of workload
    could be triggered by 1 user or 20 users, however it is most likely that 5
    users will trigger it. A workload means the connection is asking SQL Server
    to do something, run a select, an SP, an insert etc.

    -Euan

    "Bob Dean" <rcdean@juno.com> wrote in message
    news:3b8f292f$1@news.devx.com...
    >
    > Paul,
    >
    > I'm in exactly this position now: migrating my app from Access to either
    > SQL or MSDE (or both). I'm very unclear about the nebulous constraint

    Microsoft
    > describes for MSDE. I think it is much as you describe it: e.g. 5

    concurrent
    > "batches". What does this mean? Suppose I have 10 users and they all are
    > updating different records in different tables in the same db at about the
    > same time. Is MSDE going to bomb? Will it handle their queries? This is

    important
    > because--like others--I gave a lot of my customers the expectation that

    Access
    > would solve their problem. If I can substitute MSDE (free), fine. But if
    > I have to get them to buy SQL server, that could be a problem. Thanks in
    > advance for any advice on this.
    >
    > Bob
    >
    >
    > "Paul Mc" <paulmc@nospam.thehub.com.au> wrote:
    > >
    > >G'day.
    > >
    > >MSDE is a good interim choice, however I believe that it has built in

    limitations
    > >to encourage movement up the SQL Server proper, such as a performance

    governer
    > >that limits performance when 5+ batches are executed concurrently, as

    well
    > >as a 2gb max db size...
    > >
    > >Cheers,
    > >Paul

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center