Need help with Access decision

I am not primarily an Access developer. My background is in C++ and coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

· frequent crashes
· difficulty keeping track of the context in windows containing child
windows
· difficulty controlling when data is entered into the database (for
example, when the user wants to cancel)
· code that crashes, produces errors, etc. until I run the /decompile
switch - then it works
· I have saved, compiled, and run code only to find that all my
"saved" changes are gone
· changing the size of a group box cause a size increase of 1mb in
the mdb file

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I could probably rewrite the whole thing in a few weeks using C# and Windows
Forms. However, this is a small company and I hesitate to recommend this
type of expensive development. Also, it would lock them into C# or VB.NET
development which might be more expensive.

So, my questions a
· Is Access as unstable as I think it is? (the user interface, not
the actual database)
· How does one judge when to move away from Access? In this
application:
o the code is poorly written
o the user interface kinda works, but is clunky and does not allow
the user to cancel operations easily
o validation of data input is poor throughout the app
o the application is not extremely stable
· Is it inevitable that Access user interfaces will be moved to some
other technology?

To be honest, I do not know if most of my problems are due to lack of
experience with Access. I am used to developing in an environment where
everything is more transparent. However, I have talked to several people
who do not think Access is very stable.

Any comments would be appreciated. Thanks.

Alfredo

So, my questions a
· Is Access as unstable as I think it is? (the user interface, not
the actual database)

Not when designed correctly.

· How does one judge when to move away from Access? In this
application:
Generally when size of the db or number of users becomes too high for the
Jet engine to work well...

o the code is poorly written
This can be avoided

o the user interface kinda works, but is clunky and does not
allow
the user to cancel operations easily
Can be fixed in most cases

o validation of data input is poor throughout the app
Can also be fixed

o the application is not extremely stable
All sorts of possibilities here...first thing to check is: is the database
split, and does each user have a copy of the FE on their desktop.

· Is it inevitable that Access user interfaces will be moved to
some
other technology?
I would guess that Access will be around in various forms for a long time
given the number of Access apps out there.

You are correct that locking a small-to medium sized business into a c# or
vb.net developmnet platform is probably a bad thing to do. Not knowing more
about the application, it sounds as if fixing or re-writing in Access may be
the easiest thing for them down the road.

If you want a general conversation about Access and its limitations, please
post back or even contact me offline.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

"aualias" wrote in message
...
I am not primarily an Access developer. My background is in C++ and
coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment
in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

· frequent crashes
· difficulty keeping track of the context in windows containing
child
windows
· difficulty controlling when data is entered into the database
(for
example, when the user wants to cancel)
· code that crashes, produces errors, etc. until I run the
/decompile
switch - then it works
· I have saved, compiled, and run code only to find that all my
"saved" changes are gone
· changing the size of a group box cause a size increase of 1mb in
the mdb file

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I could probably rewrite the whole thing in a few weeks using C# and
Windows
Forms. However, this is a small company and I hesitate to recommend this
type of expensive development. Also, it would lock them into C# or VB.NET
development which might be more expensive.

So, my questions a
· Is Access as unstable as I think it is? (the user interface, not
the actual database)
· How does one judge when to move away from Access? In this
application:
o the code is poorly written
o the user interface kinda works, but is clunky and does not
allow
the user to cancel operations easily
o validation of data input is poor throughout the app
o the application is not extremely stable
· Is it inevitable that Access user interfaces will be moved to
some
other technology?

To be honest, I do not know if most of my problems are due to lack of
experience with Access. I am used to developing in an environment where
everything is more transparent. However, I have talked to several people
who do not think Access is very stable.

Any comments would be appreciated. Thanks.

Alfredo

aualias wrote:


Any comments would be appreciated. Thanks.

Alfredo

I agree with Kevin so I won't do a response by the numbers.

It is hard to tell from here, but a good clean up of what they have may
resolve the stability problems.

I have used Access in several mission critical applications with as many
as 60+ usres (seldom all on at the same time) and have found it stable.

The applications I have seen that are less stable (noting is 100%) tend
to be more fancy in their interface and data handling.

Very large databases and intense large user groups can cause problems.
SQL seems to be the solution of choice when that happens.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Let me add something to what Kevin and Joseph said. You don't say anything
about where this database is stored. I'm assuming it is on a server. If that
is the case, do each of the users have a copy of the frontend on their own
PC? The vast majority of corruption problems are the result of network
traffic and/or problems. Putting a copy of the frontend on each PC will
greatly reduce the need for network traffic.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"aualias" wrote in message
...
I am not primarily an Access developer. My background is in C++ and
coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment
in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

· frequent crashes
· difficulty keeping track of the context in windows containing
child
windows
· difficulty controlling when data is entered into the database
(for
example, when the user wants to cancel)
· code that crashes, produces errors, etc. until I run the
/decompile
switch - then it works
· I have saved, compiled, and run code only to find that all my
"saved" changes are gone
· changing the size of a group box cause a size increase of 1mb in
the mdb file

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I could probably rewrite the whole thing in a few weeks using C# and
Windows
Forms. However, this is a small company and I hesitate to recommend this
type of expensive development. Also, it would lock them into C# or VB.NET
development which might be more expensive.

So, my questions a
· Is Access as unstable as I think it is? (the user interface, not
the actual database)
· How does one judge when to move away from Access? In this
application:
o the code is poorly written
o the user interface kinda works, but is clunky and does not
allow
the user to cancel operations easily
o validation of data input is poor throughout the app
o the application is not extremely stable
· Is it inevitable that Access user interfaces will be moved to
some
other technology?

To be honest, I do not know if most of my problems are due to lack of
experience with Access. I am used to developing in an environment where
everything is more transparent. However, I have talked to several people
who do not think Access is very stable.

Any comments would be appreciated. Thanks.

Alfredo

"aualias" wrote in message
...

I am not primarily an Access developer. My background is in C++ and
coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment
in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

Poorly written software is simply poorly written software. I been
consulting, and writing software on a daily basic for 20 years now. I have
used a LOT of development platforms. Many were mini-and main frame based (I
still consult for those systems). I also consult to company's that develop
and built products around the office application suite. That generally means
VB, and VBA

On the pc side, I have written in assumer, and even written payroll systems
in Pascal without the aid of a database system (had to write my own).

I have coded on just about every platform I can think of. I have used a
good number of pc based database systems. They go all the way back to
Reflex, DataEase, KnowledgeMan, Advanced Revelation, and of course the
veritable dbaseeIII, and FoxPro.

I also currently have software of mine running on Linux computers in 6, or
more counties around the world as I speak.

So, given the above..what is my experience with ms-access?..


· frequent crashes

No, I have not experienced the above at all. I have clients running access
applications, and in most cases they are MORE reliable then email, word, or
most applications that they run on a daily bases. About the ONLY thing you
need to ensure is that the updates to JET, and to the particular version of
office/access are installed. I suppose it goes without saying that service
packs,a nd updates are required to anyone who is serous about software
development, and takes at least SOME pride in their workmanship. (so, I
doubt I even have to point out that ensuing updates tot he software tools
need to be installed).


· difficulty keeping track of the context in windows containing
child
windows

Gee, not sure what you mean by the above. As always, when a developer is
faced with a new tool, they instantly blame the tools, and not their lack of
knowledge, and how the tools is to be used.

Ms-access follows the MIDI interface that Excel, and word have had for about
10+ years. Only since office 2000 has Microsoft started to move away from
the MIDI interface..and gone to SDI. However, I find control of focus and
ease at which forms follow each other VERY easy in ms-access. I would say
that 80% of my forms are model..but that much my preference, and my desire
to "control" users.

I also going to mention that trying to figure out ms-access which has a FAR
steeper learning curve then VB does can NOT be learned in quick afternoon.

I would consider finding a good developer to evaluate this situation for
you. Anytime you get involved in a project, you need someone with some real
good skills.

Here is a great quote on the Sven Stages of Expertise in Software
Engineering

The general levels of expertise a

quote

Stage 1 Innocent (never heard of the product)

Stage 2 Aware (Has read an article about X)

Stage 3 Apprentice (has attended a three-day seminar)

Stage 4 Practitioner (ready to use X on a real project)

Stage 5 Journeyman (uses X naturally and automatically in his job)

Stage 6 Master (has internalized X, knows when to break the rules)

Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)

/quote
Page-Jones, Meilir. "The Seven Stages of Expertise in Software Engineering",
American Programmer, July-Aug 1990

One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.


· difficulty controlling when data is entered into the database
(for
example, when the user wants to cancel)

There are a number of events on the form that you need learn about (before
update is a good example). If you are talking about a general form, and
controlling the update..there is as a general rule FAR better controls here
then what you have in environments like VB.

The ONLY exception to this rule would be when you use sub-forms. (you can't
wrap forms, and sub-forms in a transaction). So, if you are just talking
about a general form, and controlling the update, I have to disagree with
you. If you are taking about sub-forms..then yes..I agree, this is a weak
spot.

· code that crashes, produces errors, etc. until I run the
/decompile
switch - then it works

The production version of your software should be a mde file..not a mdb.
And, this mde is placed on each pc..(right?). Or, are we even talking about
a multi-user application here?

· I have saved, compiled, and run code only to find that all my
"saved" changes are gone

Hum, have not experienced the above. However, as mentioned. access 2000 in
my option was NOT very stable until the service packs came out.

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I find that for small applications, adding forms is not a problem. For
example, I have a small sized application in ms-access. It have 160 forms,
and about 100 reports. it is had a modest amount of code (27,000 lines). (of
course, in a traditional environment like VB, or c#, you don't get 160 forms
very easily..as those 3 months would take 12 or more months to develop the
same thing). I should note that the resulting application is less then 6
megs in size, and this mde can be zipped onto a floppy disk.

What truly great is that to install the application we simply copy it to the
target pc (we had x-copy development for 10 years now..and the folks in .net
are JUST starting to rant and rave about the wonders of such a setup. We
done this for 10+ years!).

It is possible that your application has far more then 160 forms, and is
rather large. However, even up to the 250 to 300 range for forms, you are
still talking about a fairly small mde file.


I could probably rewrite the whole thing in a few weeks using C# and
Windows
Forms.

You cold, but then again, you will spend about 2 to 3 times the effort as
compared to ms-access. Some people rate the development speed in ms-access
much higher..but lets just agree on the low end of the spectrum in terms of
RAD tools. You are talking about a good 3 times the amount of work to
accomplish the same functionality here.

So, my questions a
· Is Access as unstable as I think it is? (the user interface, not
the actual database)

I have found when you ensure the target pc has a good install of access,
then the resulting application is very stable.

· How does one judge when to move away from Access?

I guess it depends on if it is worth saving. In the summer of 2002, I did
consult for firm that had development a application in VB. It really was a
disaster and part of the problem was poor designs, but worse was that so
much code was used to control referential integrity on the data when the
database engine can do so much of this kind of thing. These most offenses
applications I seen are ones written in VB, or c++ that try to do data
management when tools like ms-access would have been much better. They are
going to keep the application...but man, what a mess! (good developers do
NOT necessary make good database developers).

· Is it inevitable that Access user interfaces will be moved to
some
other technology?

Well, what other technology..and at what cost? I mean, we had ms-access out
for 10+ years, and the team is hard at work on the next version (can't say
that about VB6..can we now?). I think the real issues are of how many
desktops are you talking about here? (vb, and .net applications have
advantages when the team of developers grows in size, and also the number of
desktops you plan to support also is gong to be large.Further, as the
project gets larger, then use of class objects etc becomes more important as
developers much work with each other).

I have good document I wrote on converting a application from one platform
to ms-access. You might give the document a good read, as it does have some
good info on the processes involved.

http://www.attcanada.net/~kallal.msn...000000003.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Albert D. Kallal wrote:
....

I also going to mention that trying to figure out ms-access which has
a FAR steeper learning curve then VB does can NOT be learned in quick
afternoon.


Very true and I believe the more you know databases before the harder it
is to move into Access.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Dear Mr. Meehan:

Very true and I believe the more you know databases before the harder
it
is to move into Access.

Forgive me, but I'm a little confused by this statement. Are you saying that
the more you know about databases before beginning to learn Access the
harder it is to learn Access?

Thanks!
Fred Boer

"Joseph Meehan" wrote in message
...
Albert D. Kallal wrote:
...

I also going to mention that trying to figure out ms-access which has
a FAR steeper learning curve then VB does can NOT be learned in quick
afternoon.


Very true and I believe the more you know databases before the harder
it
is to move into Access.

--

I will say that ms-access has some quirks, but you can use the product with
sql-server, or insert your favatore database engine here.

My above point referees to that on initial use, ms-access forms are EASIER
then VB forms. However, you need to spend time learning the ms-access forms
event model, and the very large number of properties that are associated
with ms-access forms (that VB forms do NOT have). Thus, my point was is that
MORE time is needed to learn the complex event model that ms-access has.
Once the ms-access model is learned..then (and only then) does the
productivity of the developers really go up over tools like VB.

Further, I don't really consider ms-access a database. Really, all it is a
forms developer wrapped around the VB language. You database is going to be
JET, Sql - server..or whatever you use. Ms-access is thus only a client
development tool to your favorite database.

I can't really say, or think of any real major issue that prevented me from
learning ms-access, or anything that made it difficult based on my numerous
experiences with other database systems. All ms-access is a collection of
Microsoft technologies (ado, dao, VB etc). Developing two tried applications
in ms-access is not such much different then say VB or whatever, it just
that you have a bound forms model.

How does previous database experience make learning tools like VB, or c++,
or c#, or ms-access more difficult? All of these products simply allow you
to develop the UI to the database engine of your choice anyway.

I may be miss understanding your point here. I suspect I view ms-access as
only a client tool, where as you view it as the database product it self
(this would explain your point of view).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Fred Boer wrote:
Dear Mr. Meehan:

Very true and I believe the more you know databases before the
harder it is to move into Access.

Forgive me, but I'm a little confused by this statement. Are you
saying that the more you know about databases before beginning to
learn Access the harder it is to learn Access?

Yea. It goes about so many things from a different perspective that you
have to un-learn much and then re-learn other things, only to find out that
most of the problem was their choice of nomenclature, and it really is the
same. By then you have wasted a lot of time. Even with that, I like the
product.


Thanks!
Fred Boer

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

1) The Access 2000 development environment was and is a bit unstable.
By reputation, the 2002 and 2003 versions are a bit better. What are
you using?

Because the IDE is a bit unstable, it can damage your VBA project.

If you do development in Access, you need to learn to avoid things
that can crash your development environment. Basically, Close and
Save frequently, close objects when switching between design and
data view mode, decompile and compact occasionally, avoid situations
that cause code exceptions. You also need to ensure that your
Windows environment is stable, including any patches you have
applied.

There are also a few things that can crash the runtime environment:
basically damaged installations of Access or required components,
which can in particular cause Access to crash instead of displaying
an error message when a code exception occurs. Basically, you need
to ensure that your Windows environment is stable, and that you fix
any coding bugs before deploying the application.

2) I don't have any difficulty keeping track of context: I guess
that must be just familiarity.

3) Cancellation is tricky in Access: By default Access will
automatically commit stuff that you type in. If you don't
want this automatic action, you may have to give up on some
of the 'free' features of Access, (like forms bound directly
to your data tables), and do some coding yourself. All of
my critical data entry is done on local tables: the result
is committed to the remote data by coded actions. On the
other hand, all of my static data uses forms directly bound
to live data: the tables are small, and if the user doesn't
like what they get, they can change it back again.

4) Our large application has now well over 1000 forms, and
is commercially deployed: we are not experiencing any
stability problems.

5) Some of our stuff is written in C++ and VB. The user
interface on all the VB stuff is clunkier than in Access:
it's just a lot more work to get the basic stuff going,
and there never seems to be client interest in spending
money on making the interface look good. The user interface
on all the C++ stuff is better than in Access: Firstly,
Access ties you to a particular version of the MS interface,
so you aren't using the latest and greatest look, secondly,
the kinds of clients we get who choose C++ are so in love
with the product they are developing that they are willing
to hire graphics people to get the look right.

(david)


"aualias" wrote in message
...
I am not primarily an Access developer. My background is in C++ and
coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment
in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

· frequent crashes
· difficulty keeping track of the context in windows containing
child
windows
· difficulty controlling when data is entered into the database
(for
example, when the user wants to cancel)
· code that crashes, produces errors, etc. until I run the
/decompile
switch - then it works
· I have saved, compiled, and run code only to find that all my
"saved" changes are gone
· changing the size of a group box cause a size increase of 1mb in
the mdb file

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I could probably rewrite the whole thing in a few weeks using C# and
Windows
Forms. However, this is a small company and I hesitate to recommend this
type of expensive development. Also, it would lock them into C# or VB.NET
development which might be more expensive.

So, my questions a
· Is Access as unstable as I think it is? (the user interface, not
the actual database)
· How does one judge when to move away from Access? In this
application:
o the code is poorly written
o the user interface kinda works, but is clunky and does not
allow
the user to cancel operations easily
o validation of data input is poor throughout the app
o the application is not extremely stable
· Is it inevitable that Access user interfaces will be moved to
some
other technology?

To be honest, I do not know if most of my problems are due to lack of
experience with Access. I am used to developing in an environment where
everything is more transparent. However, I have talked to several people
who do not think Access is very stable.

Any comments would be appreciated. Thanks.

Alfredo