Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Outgrown Access? What next?

Discussion in 'Information Technology' started by Stonewall, Jul 28, 2006.

  1. Stonewall

    Stonewall
    Expand Collapse
    Guest

    I have researched this subject until I will dizzy. I Thought I would stop
    and ask for some advice.

    My company distributes a Microsoft Access 2003 application that is quite
    involved with about 50 tables, 60 forms, lots of complex queries, reports and
    modules filled with custom code. The app works well except for the usual
    Access related complaints like crashes and database corruption. The database
    is relatively small. After 5 years I have seen a few at 100 MB at most. Most
    are between 10 - 30 MB. Also, some clients balk when they find out the app is
    designed In Access since all the talk is SQL this and SQL that. They get the
    impression if it's designed in Access, it's a "chevette" instead of a
    "cadillac". In most client settings, there are no more than 5 or 6
    concurrent users. They store the backend database on the network and the
    front end is installed on each desktop.

    We want to try to improve stability as well as give the client a comfort
    level that the cutting edge technology is being used. In researching SQL, I
    didn't see any options that didn't involved completely rewriting the
    application. (which would be a huge undertaking). Also with only a few users
    at each site, it seems like overkill. Also, the end client doesn't have the
    technology staff to install or maintain SQL Server and they don't want to
    spend the extra money buy SQL. With our Access solution, we simply
    distribute it with Access runtime at no cost to them.

    With all this being said, how can I make everybody happy? Are there any
    options other than a full rewrite to SQL? I've read some about MSDE but is
    it viable or obsolete?

    Thanks in advance.

    Stonewall
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    There's no such things as "a full rewrite to SQL". SQL Server is strictly a
    DBMS: it has no user interface, no ability to create reports, no forms, etc.

    If their requirements have increased to the point where they need to use SQL
    Server as a back-end database, Access is still a fine front-end to use.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    news:CAE362A8-4829-42D0-AB33-95DFB4EF5EC0@microsoft.com...
    >I have researched this subject until I will dizzy. I Thought I would stop
    > and ask for some advice.
    >
    > My company distributes a Microsoft Access 2003 application that is quite
    > involved with about 50 tables, 60 forms, lots of complex queries, reports
    > and
    > modules filled with custom code. The app works well except for the usual
    > Access related complaints like crashes and database corruption. The
    > database
    > is relatively small. After 5 years I have seen a few at 100 MB at most.
    > Most
    > are between 10 - 30 MB. Also, some clients balk when they find out the app
    > is
    > designed In Access since all the talk is SQL this and SQL that. They get
    > the
    > impression if it's designed in Access, it's a "chevette" instead of a
    > "cadillac". In most client settings, there are no more than 5 or 6
    > concurrent users. They store the backend database on the network and the
    > front end is installed on each desktop.
    >
    > We want to try to improve stability as well as give the client a comfort
    > level that the cutting edge technology is being used. In researching SQL,
    > I
    > didn't see any options that didn't involved completely rewriting the
    > application. (which would be a huge undertaking). Also with only a few
    > users
    > at each site, it seems like overkill. Also, the end client doesn't have
    > the
    > technology staff to install or maintain SQL Server and they don't want to
    > spend the extra money buy SQL. With our Access solution, we simply
    > distribute it with Access runtime at no cost to them.
    >
    > With all this being said, how can I make everybody happy? Are there any
    > options other than a full rewrite to SQL? I've read some about MSDE but
    > is
    > it viable or obsolete?
    >
    > Thanks in advance.
    >
    > Stonewall
     
  4. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    MSDE and its replacement SQL-Server Express 2005 are free, so why don't you
    explore this avenue?

    We often hear that using MSDE/SQL-Server/Express instead of a MDB file might
    be an « overkill ». However, I don't think that hearing complaints about
    crashes and database corruption is really a better option. When the gun is
    not powerfull enough to kill the beast, using a more powerfull gun is not
    overkill.

    My advice: offer your clients the possibility of using SQL-Server Express
    2005 and call it the *Pro* version.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    news:CAE362A8-4829-42D0-AB33-95DFB4EF5EC0@microsoft.com...
    >I have researched this subject until I will dizzy. I Thought I would stop
    > and ask for some advice.
    >
    > My company distributes a Microsoft Access 2003 application that is quite
    > involved with about 50 tables, 60 forms, lots of complex queries, reports
    > and
    > modules filled with custom code. The app works well except for the usual
    > Access related complaints like crashes and database corruption. The
    > database
    > is relatively small. After 5 years I have seen a few at 100 MB at most.
    > Most
    > are between 10 - 30 MB. Also, some clients balk when they find out the app
    > is
    > designed In Access since all the talk is SQL this and SQL that. They get
    > the
    > impression if it's designed in Access, it's a "chevette" instead of a
    > "cadillac". In most client settings, there are no more than 5 or 6
    > concurrent users. They store the backend database on the network and the
    > front end is installed on each desktop.
    >
    > We want to try to improve stability as well as give the client a comfort
    > level that the cutting edge technology is being used. In researching SQL,
    > I
    > didn't see any options that didn't involved completely rewriting the
    > application. (which would be a huge undertaking). Also with only a few
    > users
    > at each site, it seems like overkill. Also, the end client doesn't have
    > the
    > technology staff to install or maintain SQL Server and they don't want to
    > spend the extra money buy SQL. With our Access solution, we simply
    > distribute it with Access runtime at no cost to them.
    >
    > With all this being said, how can I make everybody happy? Are there any
    > options other than a full rewrite to SQL? I've read some about MSDE but
    > is
    > it viable or obsolete?
    >
    > Thanks in advance.
    >
    > Stonewall
     
  5. jim whitaker

    jim whitaker
    Expand Collapse
    Guest

    Dang, I cannot help myself. These so called dumb clients should be dumped,
    to smarter clients. Some thought the b-52 was out of date, but it's
    projected to be around till 2043 or later I heard on the news. It was first
    made in the 1950's. We've become "a latest thing", brainless, SUV gas
    hogging society. People don't use their brains anymore. And just one more
    thing, don't forget, people are wearing their pants half down nowadays.
    Don't these dumb clients realize that ms access or FoxPro, or asp, or PHP,
    or asp.net, or something is required to be a front-end to a server database?
    How vulgar dumb can someone be? Oh the pants thing, well I guess pretty
    dumb. I feel that anyone who uses a database should know a little about it
    works. I don't mean a person serving the net who has to fill out a form. I
    mean in a business, the chosen database should at least be understood by
    even the users. They may not ever have to program, but for crying out loud
    learn the tech papers on it, and understand how it all works. You can
    understand and appreciate how a plane cay fly, that does not mean you have
    to be a pilot. And can someone start making people wear their pants
    right???

    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    wrote in message news:egubOQ$hGHA.1864@TK2MSFTNGP02.phx.gbl...
    > MSDE and its replacement SQL-Server Express 2005 are free, so why don't
    > you explore this avenue?
    >
    > We often hear that using MSDE/SQL-Server/Express instead of a MDB file
    > might be an « overkill ». However, I don't think that hearing complaints
    > about crashes and database corruption is really a better option. When the
    > gun is not powerfull enough to kill the beast, using a more powerfull gun
    > is not overkill.
    >
    > My advice: offer your clients the possibility of using SQL-Server Express
    > 2005 and call it the *Pro* version.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: http://cerbermail.com/?QugbLEWINF
    >
    >
    > "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    > news:CAE362A8-4829-42D0-AB33-95DFB4EF5EC0@microsoft.com...
    >>I have researched this subject until I will dizzy. I Thought I would stop
    >> and ask for some advice.
    >>
    >> My company distributes a Microsoft Access 2003 application that is quite
    >> involved with about 50 tables, 60 forms, lots of complex queries, reports
    >> and
    >> modules filled with custom code. The app works well except for the usual
    >> Access related complaints like crashes and database corruption. The
    >> database
    >> is relatively small. After 5 years I have seen a few at 100 MB at most.
    >> Most
    >> are between 10 - 30 MB. Also, some clients balk when they find out the
    >> app is
    >> designed In Access since all the talk is SQL this and SQL that. They get
    >> the
    >> impression if it's designed in Access, it's a "chevette" instead of a
    >> "cadillac". In most client settings, there are no more than 5 or 6
    >> concurrent users. They store the backend database on the network and the
    >> front end is installed on each desktop.
    >>
    >> We want to try to improve stability as well as give the client a comfort
    >> level that the cutting edge technology is being used. In researching
    >> SQL, I
    >> didn't see any options that didn't involved completely rewriting the
    >> application. (which would be a huge undertaking). Also with only a few
    >> users
    >> at each site, it seems like overkill. Also, the end client doesn't have
    >> the
    >> technology staff to install or maintain SQL Server and they don't want to
    >> spend the extra money buy SQL. With our Access solution, we simply
    >> distribute it with Access runtime at no cost to them.
    >>
    >> With all this being said, how can I make everybody happy? Are there any
    >> options other than a full rewrite to SQL? I've read some about MSDE but
    >> is
    >> it viable or obsolete?
    >>
    >> Thanks in advance.
    >>
    >> Stonewall

    >
    >
     
  6. Stonewall

    Stonewall
    Expand Collapse
    Guest

    Thank you. I think I will investigate SQL Express. What do I use to convert
    the backend .mdb database to SQL express? I thought SQL express could not be
    used with a shared database with multiple users running over a network?

    "Sylvain Lafontaine" wrote:

    > MSDE and its replacement SQL-Server Express 2005 are free, so why don't you
    > explore this avenue?
    >
    > We often hear that using MSDE/SQL-Server/Express instead of a MDB file might
    > be an « overkill ». However, I don't think that hearing complaints about
    > crashes and database corruption is really a better option. When the gun is
    > not powerfull enough to kill the beast, using a more powerfull gun is not
    > overkill.
    >
    > My advice: offer your clients the possibility of using SQL-Server Express
    > 2005 and call it the *Pro* version.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: http://cerbermail.com/?QugbLEWINF
    >
    >
    > "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    > news:CAE362A8-4829-42D0-AB33-95DFB4EF5EC0@microsoft.com...
    > >I have researched this subject until I will dizzy. I Thought I would stop
    > > and ask for some advice.
    > >
    > > My company distributes a Microsoft Access 2003 application that is quite
    > > involved with about 50 tables, 60 forms, lots of complex queries, reports
    > > and
    > > modules filled with custom code. The app works well except for the usual
    > > Access related complaints like crashes and database corruption. The
    > > database
    > > is relatively small. After 5 years I have seen a few at 100 MB at most.
    > > Most
    > > are between 10 - 30 MB. Also, some clients balk when they find out the app
    > > is
    > > designed In Access since all the talk is SQL this and SQL that. They get
    > > the
    > > impression if it's designed in Access, it's a "chevette" instead of a
    > > "cadillac". In most client settings, there are no more than 5 or 6
    > > concurrent users. They store the backend database on the network and the
    > > front end is installed on each desktop.
    > >
    > > We want to try to improve stability as well as give the client a comfort
    > > level that the cutting edge technology is being used. In researching SQL,
    > > I
    > > didn't see any options that didn't involved completely rewriting the
    > > application. (which would be a huge undertaking). Also with only a few
    > > users
    > > at each site, it seems like overkill. Also, the end client doesn't have
    > > the
    > > technology staff to install or maintain SQL Server and they don't want to
    > > spend the extra money buy SQL. With our Access solution, we simply
    > > distribute it with Access runtime at no cost to them.
    > >
    > > With all this being said, how can I make everybody happy? Are there any
    > > options other than a full rewrite to SQL? I've read some about MSDE but
    > > is
    > > it viable or obsolete?
    > >
    > > Thanks in advance.
    > >
    > > Stonewall

    >
    >
    >
     
  7. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    For the upsizing process, you can use the Upsizing Wizard that comes with
    Access and you should be done at the end of it in most cases. If you have a
    lot of DAO in your VBA code, you will have to add the option dbSeeChanges
    here and there but Access will tell you everywhere you have missed it when
    you will try to run the code; so it's easy to correct the situation.

    If you have problem connecting to SQL-Server Express:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    http://www.datamasker.com/SSE2005_NetworkCfg.htm

    http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx

    http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

    When it comes to multi-users, SQL-Server (Express or not) is more powerfull
    than Access; so I don't understand where you may have got your last tought.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    news:0667B385-AB2C-4DDB-ABAF-AC91736543B2@microsoft.com...
    > Thank you. I think I will investigate SQL Express. What do I use to
    > convert
    > the backend .mdb database to SQL express? I thought SQL express could not
    > be
    > used with a shared database with multiple users running over a network?
    >
    > "Sylvain Lafontaine" wrote:
    >
    >> MSDE and its replacement SQL-Server Express 2005 are free, so why don't
    >> you
    >> explore this avenue?
    >>
    >> We often hear that using MSDE/SQL-Server/Express instead of a MDB file
    >> might
    >> be an « overkill ». However, I don't think that hearing complaints about
    >> crashes and database corruption is really a better option. When the gun
    >> is
    >> not powerfull enough to kill the beast, using a more powerfull gun is not
    >> overkill.
    >>
    >> My advice: offer your clients the possibility of using SQL-Server Express
    >> 2005 and call it the *Pro* version.
    >>
    >> --
    >> Sylvain Lafontaine, ing.
    >> MVP - Technologies Virtual-PC
    >> E-mail: http://cerbermail.com/?QugbLEWINF
    >>
    >>
    >> "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    >> news:CAE362A8-4829-42D0-AB33-95DFB4EF5EC0@microsoft.com...
    >> >I have researched this subject until I will dizzy. I Thought I would
    >> >stop
    >> > and ask for some advice.
    >> >
    >> > My company distributes a Microsoft Access 2003 application that is
    >> > quite
    >> > involved with about 50 tables, 60 forms, lots of complex queries,
    >> > reports
    >> > and
    >> > modules filled with custom code. The app works well except for the
    >> > usual
    >> > Access related complaints like crashes and database corruption. The
    >> > database
    >> > is relatively small. After 5 years I have seen a few at 100 MB at
    >> > most.
    >> > Most
    >> > are between 10 - 30 MB. Also, some clients balk when they find out the
    >> > app
    >> > is
    >> > designed In Access since all the talk is SQL this and SQL that. They
    >> > get
    >> > the
    >> > impression if it's designed in Access, it's a "chevette" instead of a
    >> > "cadillac". In most client settings, there are no more than 5 or 6
    >> > concurrent users. They store the backend database on the network and
    >> > the
    >> > front end is installed on each desktop.
    >> >
    >> > We want to try to improve stability as well as give the client a
    >> > comfort
    >> > level that the cutting edge technology is being used. In researching
    >> > SQL,
    >> > I
    >> > didn't see any options that didn't involved completely rewriting the
    >> > application. (which would be a huge undertaking). Also with only a few
    >> > users
    >> > at each site, it seems like overkill. Also, the end client doesn't
    >> > have
    >> > the
    >> > technology staff to install or maintain SQL Server and they don't want
    >> > to
    >> > spend the extra money buy SQL. With our Access solution, we simply
    >> > distribute it with Access runtime at no cost to them.
    >> >
    >> > With all this being said, how can I make everybody happy? Are there any
    >> > options other than a full rewrite to SQL? I've read some about MSDE
    >> > but
    >> > is
    >> > it viable or obsolete?
    >> >
    >> > Thanks in advance.
    >> >
    >> > Stonewall

    >>
    >>
    >>
     
  8. Stonewall

    Stonewall
    Expand Collapse
    Guest

    Thank you so much Sylvain.

    Stonewall

    "Sylvain Lafontaine" wrote:

    > For the upsizing process, you can use the Upsizing Wizard that comes with
    > Access and you should be done at the end of it in most cases. If you have a
    > lot of DAO in your VBA code, you will have to add the option dbSeeChanges
    > here and there but Access will tell you everywhere you have missed it when
    > you will try to run the code; so it's easy to correct the situation.
    >
    > If you have problem connecting to SQL-Server Express:
    >
    > http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
    >
    > http://www.datamasker.com/SSE2005_NetworkCfg.htm
    >
    > http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx
    >
    > http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
    >
    > When it comes to multi-users, SQL-Server (Express or not) is more powerfull
    > than Access; so I don't understand where you may have got your last tought.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: http://cerbermail.com/?QugbLEWINF
    >
    >
    > "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    > news:0667B385-AB2C-4DDB-ABAF-AC91736543B2@microsoft.com...
    > > Thank you. I think I will investigate SQL Express. What do I use to
    > > convert
    > > the backend .mdb database to SQL express? I thought SQL express could not
    > > be
    > > used with a shared database with multiple users running over a network?
    > >
    > > "Sylvain Lafontaine" wrote:
    > >
    > >> MSDE and its replacement SQL-Server Express 2005 are free, so why don't
    > >> you
    > >> explore this avenue?
    > >>
    > >> We often hear that using MSDE/SQL-Server/Express instead of a MDB file
    > >> might
    > >> be an « overkill ». However, I don't think that hearing complaints about
    > >> crashes and database corruption is really a better option. When the gun
    > >> is
    > >> not powerfull enough to kill the beast, using a more powerfull gun is not
    > >> overkill.
    > >>
    > >> My advice: offer your clients the possibility of using SQL-Server Express
    > >> 2005 and call it the *Pro* version.
    > >>
    > >> --
    > >> Sylvain Lafontaine, ing.
    > >> MVP - Technologies Virtual-PC
    > >> E-mail: http://cerbermail.com/?QugbLEWINF
    > >>
    > >>
    > >> "Stonewall" <Stonewall@discussions.microsoft.com> wrote in message
    > >> news:CAE362A8-4829-42D0-AB33-95DFB4EF5EC0@microsoft.com...
    > >> >I have researched this subject until I will dizzy. I Thought I would
    > >> >stop
    > >> > and ask for some advice.
    > >> >
    > >> > My company distributes a Microsoft Access 2003 application that is
    > >> > quite
    > >> > involved with about 50 tables, 60 forms, lots of complex queries,
    > >> > reports
    > >> > and
    > >> > modules filled with custom code. The app works well except for the
    > >> > usual
    > >> > Access related complaints like crashes and database corruption. The
    > >> > database
    > >> > is relatively small. After 5 years I have seen a few at 100 MB at
    > >> > most.
    > >> > Most
    > >> > are between 10 - 30 MB. Also, some clients balk when they find out the
    > >> > app
    > >> > is
    > >> > designed In Access since all the talk is SQL this and SQL that. They
    > >> > get
    > >> > the
    > >> > impression if it's designed in Access, it's a "chevette" instead of a
    > >> > "cadillac". In most client settings, there are no more than 5 or 6
    > >> > concurrent users. They store the backend database on the network and
    > >> > the
    > >> > front end is installed on each desktop.
    > >> >
    > >> > We want to try to improve stability as well as give the client a
    > >> > comfort
    > >> > level that the cutting edge technology is being used. In researching
    > >> > SQL,
    > >> > I
    > >> > didn't see any options that didn't involved completely rewriting the
    > >> > application. (which would be a huge undertaking). Also with only a few
    > >> > users
    > >> > at each site, it seems like overkill. Also, the end client doesn't
    > >> > have
    > >> > the
    > >> > technology staff to install or maintain SQL Server and they don't want
    > >> > to
    > >> > spend the extra money buy SQL. With our Access solution, we simply
    > >> > distribute it with Access runtime at no cost to them.
    > >> >
    > >> > With all this being said, how can I make everybody happy? Are there any
    > >> > options other than a full rewrite to SQL? I've read some about MSDE
    > >> > but
    > >> > is
    > >> > it viable or obsolete?
    > >> >
    > >> > Thanks in advance.
    > >> >
    > >> > Stonewall
    > >>
    > >>
    > >>

    >
    >
    >
     
  9. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    The first issues is do you really find a ms-access based solution any LESS
    reliable then any other solution?

    I have deployed many applications in the field. In fact, one application has
    been delayed since about 1997, and to present, I only had ONE case of a
    MINOR corruption. In other words, one client somehow managed to save a
    record that was orphaned. The application however was still complete
    functional, and not other problems occurred. This application is in use by
    MANY customers, and I not had ONE complaint in 9 years of this product being
    used. Most use in single user mode, but a good many use it in mufti-user
    mode.

    In anther application of mine, I deployed a application with 160 forms,
    27,000+ lines of code. It also have been running continues for 6 years. It
    is a tour reservation system (so, about 3-5 people run this application in
    multi-user mode). The number of bookings with this system is in the MANY
    thousands. The main customer table is rather small (only about 50,000
    records). However, some of the detail tables are considerably. In 5 years of
    continues oepstion, I have NEVER RECOVER A PHONE CALL for support of ANY
    ISSUE that was related to stability. In other words, the application runs as
    rock solid. It is FAR more trouble free then outlook, or the browser.
    (internet based stuff tends to eat up more support dollars).

    So, I am not sure why you suggest, or seem to think that ms-access is less
    stable then if you had written the application in VB, or some other
    development platform. My experience pans out that the reliability of
    ms-access based applications is BETTER then MOST applications that a user
    installs on their computer. Compared to other solutions such as VB etc, I
    have found NO difference in terms of stability.

    You ca use VB to connect to sql server, or ms-access. The resulting
    applications should perform the same, and reliability should not be any
    different. And, I would say the same applies to a jet based applications.


    >The app works well except for the usual
    > Access related complaints like crashes and database corruption.


    I have NOT had the above experience.

    > They store the backend database on the network and the
    > front end is installed on each desktop.


    The above seems like a proper setup. I assume each desktop has a mde
    installed?

    > In researching SQL, I
    > didn't see any options that didn't involved completely rewriting the
    > application.


    You obviously did not do much research. If it is COMMON for people to move
    the back end data to sql server, and keep the front end in ms-access, and
    you are NOT aware of this type of migration, then one really has to ask
    where did you do your research? Since a2000, ms-access has shipped with a
    desktop edition of sql server. (that is 3 versions now!!!). That is a long
    time!!

    > Also, the end client doesn't have the
    > technology staff to install or maintain SQL Server and they don't want to
    > spend the extra money buy SQL.


    If they can't afforded sql, then how can you state that they complain that
    the application is not in sql??? Weird statement on your part!!!!!

    > With all this being said, how can I make everybody happy? Are there any
    > options other than a full rewrite to SQL?


    Yes, you migrate the data to sql server, and then in placing of linking
    your tables to the back end mdb, you link the tables to sql server. Fully
    about 90% or more of your code will run as is. You will then have to
    optimize your application to perform better, as most of the time when you
    move to sql server, you will find a slowdown in many areas.

    >I've read some about MSDE but is
    > it viable or obsolete?


    You could use the above MSDE for your 1-5 user systems (as I said, it been
    shipped with ms-access since a2000..what 7+ years now?). However, MSDE has
    been replaced by sql server 2005 express. The new engine will handle 25-100
    users without likely breaking out in a sweat. So, this new engine would be
    IDEAL choice to use with ms-access as the front end....

    Remember, ms-access is a developers tool like c++, or VB. You write the
    application in ms-access, and CHOOSE the data engine you want to use. You
    can use oracle with ms-access, or sql server, or the "default" JET
    engine....

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  10. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    Just to satisfy my curiosity, are you first two exemples of stable Access
    applications using SQL-Server as the backend or running inside Citrix/TS or
    if they use a MDB file as the backend over a typical LAN?

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:Okkkz1DiGHA.3296@TK2MSFTNGP05.phx.gbl...
    > The first issues is do you really find a ms-access based solution any LESS
    > reliable then any other solution?
    >
    > I have deployed many applications in the field. In fact, one application
    > has been delayed since about 1997, and to present, I only had ONE case of
    > a MINOR corruption. In other words, one client somehow managed to save a
    > record that was orphaned. The application however was still complete
    > functional, and not other problems occurred. This application is in use by
    > MANY customers, and I not had ONE complaint in 9 years of this product
    > being used. Most use in single user mode, but a good many use it in
    > mufti-user mode.
    >
    > In anther application of mine, I deployed a application with 160 forms,
    > 27,000+ lines of code. It also have been running continues for 6 years. It
    > is a tour reservation system (so, about 3-5 people run this application in
    > multi-user mode). The number of bookings with this system is in the MANY
    > thousands. The main customer table is rather small (only about 50,000
    > records). However, some of the detail tables are considerably. In 5 years
    > of continues oepstion, I have NEVER RECOVER A PHONE CALL for support of
    > ANY ISSUE that was related to stability. In other words, the application
    > runs as rock solid. It is FAR more trouble free then outlook, or the
    > browser. (internet based stuff tends to eat up more support dollars).
    >
    > So, I am not sure why you suggest, or seem to think that ms-access is less
    > stable then if you had written the application in VB, or some other
    > development platform. My experience pans out that the reliability of
    > ms-access based applications is BETTER then MOST applications that a user
    > installs on their computer. Compared to other solutions such as VB etc, I
    > have found NO difference in terms of stability.
    >
    > You ca use VB to connect to sql server, or ms-access. The resulting
    > applications should perform the same, and reliability should not be any
    > different. And, I would say the same applies to a jet based applications.
    >
    >
    >>The app works well except for the usual
    >> Access related complaints like crashes and database corruption.

    >
    > I have NOT had the above experience.
    >
    >> They store the backend database on the network and the
    >> front end is installed on each desktop.

    >
    > The above seems like a proper setup. I assume each desktop has a mde
    > installed?
    >
    >> In researching SQL, I
    >> didn't see any options that didn't involved completely rewriting the
    >> application.

    >
    > You obviously did not do much research. If it is COMMON for people to move
    > the back end data to sql server, and keep the front end in ms-access, and
    > you are NOT aware of this type of migration, then one really has to ask
    > where did you do your research? Since a2000, ms-access has shipped with a
    > desktop edition of sql server. (that is 3 versions now!!!). That is a long
    > time!!
    >
    >> Also, the end client doesn't have the
    >> technology staff to install or maintain SQL Server and they don't want to
    >> spend the extra money buy SQL.

    >
    > If they can't afforded sql, then how can you state that they complain that
    > the application is not in sql??? Weird statement on your part!!!!!
    >
    >> With all this being said, how can I make everybody happy? Are there any
    >> options other than a full rewrite to SQL?

    >
    > Yes, you migrate the data to sql server, and then in placing of linking
    > your tables to the back end mdb, you link the tables to sql server. Fully
    > about 90% or more of your code will run as is. You will then have to
    > optimize your application to perform better, as most of the time when you
    > move to sql server, you will find a slowdown in many areas.
    >
    >>I've read some about MSDE but is
    >> it viable or obsolete?

    >
    > You could use the above MSDE for your 1-5 user systems (as I said, it been
    > shipped with ms-access since a2000..what 7+ years now?). However, MSDE has
    > been replaced by sql server 2005 express. The new engine will handle
    > 25-100 users without likely breaking out in a sweat. So, this new engine
    > would be IDEAL choice to use with ms-access as the front end....
    >
    > Remember, ms-access is a developers tool like c++, or VB. You write the
    > application in ms-access, and CHOOSE the data engine you want to use. You
    > can use oracle with ms-access, or sql server, or the "default" JET
    > engine....
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
     
  11. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    wrote in message news:udZcpeEiGHA.1260@TK2MSFTNGP05.phx.gbl...
    > Just to satisfy my curiosity, are you first two exemples of stable Access
    > applications using SQL-Server as the backend or running inside Citrix/TS
    > or if they use a MDB file as the backend over a typical LAN?


    The first example, running since 1997 is a split database. mde front end.
    About 1/2 the client base is multi-user. As mentioned, ONE corruption that I
    know of has occurred. And, that was on a un-patched a2000 machine (single
    user only system). This user base of a97, and a2000 users have ALL BEEN
    upgraded to the a2003 runtime.

    The 2nd example I gave was the tour reservation system. It again is split,
    and no sql server. That appcation typically has about 4 users in it all day
    long. This is system is complex, does a lot, and is run HARD all day. (used
    on the phone with clients all day -- it has full contact management and
    remembers like outlook). Again, NOT ONE service call due to ANY kind of
    freeze up in 5+ years of operation. Stability has NEVER come up, and has
    NEVER been a issue for support calls. As mentioned, main table is quite
    small (50,000). However, there is lots of related tables (the application
    has about 50+ related tables). Never ONE problem. You can read about the
    tables, and design of that application in detail here:

    http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000003.html

    So, the above applications do not involved sql server, but standard split
    databases over a typical office LAN...


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     

Share This Page