Outgrown Access? What next?

[Stonewall]

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

 

[Douglas J. Steele]

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.

 

[Sylvain Lafontaine]

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.

 

[jim whitaker]

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???

 

[Stonewall]

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]

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.

 

[Stonewall]

Thank you so much Sylvain.

Stonewall

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.

 

[Albert D.Kallal]

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....

 

[Sylvain Lafontaine]

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?

 

[Albert D.Kallal]

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...

 

[MAK]

Stonewall,
I work with a team deploying a system built to replace an MS Acces 97
version for the same reasons you describe plus the fact that we had
"outgrown" access on many of our projects using the system, so the rewrite
was justified to some degree.

If I may submit some experience-based feedback, I would suggest that
you stay with your current deploymentwhere at all possible and investigate he
possibility that your audience perception is partly based on current system
design. We found that changing small things is the UI made huge differences
in user opinion. If you find yourself in a situation similar to ours where
major change is inevitable, I wish you luck.

Regards,
MK

 

[Armen Stein]

Stonewall,
I work with a team deploying a system built to replace an MS Acces 97
version for the same reasons you describe plus the fact that we had
"outgrown" access on many of our projects using the system, so the rewrite
was justified to some degree.

If I may submit some experience-based feedback, I would suggest that
you stay with your current deploymentwhere at all possible and investigate he
possibility that your audience perception is partly based on current system
design. We found that changing small things is the UI made huge differences
in user opinion. If you find yourself in a situation similar to ours where
major change is inevitable, I wish you luck.

Regards,
MK

Many developers have opinions on when an upgrade from Access/Jet to SQL
Server is warranted. Here are mine:

- you have a lot of concurrent users (say over 15 - 20)
- you have a lot of complex or highly-accessed records (say over
100,000)
- you need to use the data extensively on a web site
- your connection between the clients and the database is slow or dodgy
- you need database-level triggers when data is changed *
- you need true security where the users do not have direct access to
the database file *

* these last two are more fact than opinion - Access/Jet just doesn't
have them.

If you answer No to all of these questions, then you can stick with
Access/Jet if your application is designed well. There is a lot of bias
against Access, especially in the IT community. This is probably
because (due to its easy initial learning curve) many truly awful
applications are built with it. Access is taking the blame for the
developers' inexperience.

Access can be used to create useful, consistent and stable database
applications that don't crash, become corrupt or upset users. We've
built hundreds of them over the years. But like any technology, the
design and construction has to be done right, and that takes experience
and skill.

Even if you do need SQL Server for the back-end, Access makes a great
client-server front-end application for it. This too takes expertise to
do properly, and many books and newsgroup posts have addressed this
approach. Upsizing a bad Access application to SQL Server without a
redesign will just create a more expensive bad application.

 

[Tony Toews]

Many developers have opinions on when an upgrade from Access/Jet to SQL
Server is warranted. Here are mine:

- you have a lot of concurrent users (say over 15 - 20)
- you have a lot of complex or highly-accessed records (say over
100,000)
- you need to use the data extensively on a web site
- your connection between the clients and the database is slow or dodgy
- you need database-level triggers when data is changed *
- you need true security where the users do not have direct access to
the database file *

Above all the points you'd mention I'd add the following:

- how mission critical is the data and can the data be rekeyed if you
lose a day.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[dbahooker]

you should use ACCESS DATA PROJECTS-- they allow you to keep a lot of
your forms and reports..

-Aaron

 

[dbahooker]

Tony;

do you really have that little faith in Access??

you should stop sticking up for MDB then; grow some balls and learn
Access Data Projects.

-Aaron

 

[dbahooker]

and for the record

ALL DATA is mission critical.

assholes like you are the reason that people put unsecured lists
containing Social Security Numbers on someones desktop.

ALL DATA IS MISSION CRITICAL

If you care enough to build a database; grow some balls and learn SQL
Server

Access Data Project rock my world.


I CAN STILL REUSE SOME OF MY FORMS AND REPORTS!!!