Attn MVPs: MS Access Issues Imposed By IT Dept.

[Tiffany]

Attn MVPs: MS Access Issues Imposed By IT Dept.

I have learned so much from the Access Newsgroups. I really do not know
what I would have done without the help I have received from the various
MVPs/Access Experts that have responded to my inquiries. THANK YOU! Since
everyone has been so helpful in the past, I want to pick your brains again.
I am in dire need of your input.

Background: Beginning in July of 2004 I started a new job on a brand new
team within my company. More money, more challenging, higher-level position.
At that time, we started creating and implementing the use of MS Access 97
databases. We will get to why we are still using Access 97 shortly. We
started using Access because we found ways to increase productivity, and
provide managers with better reporting than previously available. One of the
databases we created was so well received that our IT department created an
Web-based version (that will communicate with Acorde) to roll out to the
entire company. Our team will be the administrators of it. Great, right?
We have created a couple of other databases that would benefit the entire
company, especially the managers, but we have now been told that we cannot
use MS Access. All of our Access projects are now on hold.

Access Issues: Our IT department has placed us on hold for the following
reasons:

1. Access is not secure enough and leaks information.
2. Access has data corruption issues.
3. Access is not suitable for more than 4 simultaneous users.

I realize that there are probably more advanced programs out there, but
can’t these issues be overcome?

The reason we are still using Access 97 is simply that IT does not like
Access, and considers my team to be nothing more than “rope programmers.†If
we are going to continue using it, then we must upgrade to 2003, which IT
does not want to do because of the cost involved in obtaining the 700+
licenses that would be required.

Instead of allowing us to bring Access up to date with the corporate
security policy and test Access to find out for certain, IT has just stopped
us altogether. I am under the impression that measures can be taken to avoid
the problems that IT has adamantly stated exist. Everything I have read
indicates that good database design, proper implementation of security
measures, additional security measures that can be implemented by using VBA,
regular database maintenance and proper network design (among other things)
ensure a strong and secure database.

Questions:

Am I totally off base?

Can these issues not be avoided?

If these issues can be avoided what can I do to convince the skeptics?

If these issues can’t be avoided what other options/solutions/programs
(besides Oracle, etc.) are available?

How can my department over come the image of being “rope programmers�

What is the point of using Access if it has all these problems?

Any input is greatly appreciated.

Thank you in advance

Tiffany

 

[Allen Browne]

Hi Tiffany

You are not alone in facing these issues. IT people have a whole range of
issues to handle. Typically they want to support what they already know,
rather than learn lots of technologies. That's perfectly understandable, but
if they already know some database, they don't want to learn or support
another. You are not likely to change that no matter what you say.

Being ignorant of Access, they then make the kinds of absurd excuses you
refer to. If you are going to communicate with your IT people, you will need
to do it on a human level, where you come to understand where they are
coming from, and (if they are reasonable) they can see how they can benefit
from your experience.

It may help for you to be aware of the limitations of Access. It is not
suitable if any of the following apply:
a) The database must operate 24x7, i.e. cannot come off-line to make a
backup.
b) You have many dozens or hundreds of simultaneous users;
c) You envisage many millions of records in some tables.
d) The database must operate over unstable connections such as dial-up,
broadband, VPN, radio, or even WiFi.
e) Security is a serious issue.

There are ways around some of these limitations, e.g. for d) you can run
sessions on the server through Terminal Server.

If Access is suitable, it is almost certainly the most cost-efficient and
reliable solution you could design, and that's a major reason for it being
the best-selling database of all time. While I do experience corruptions
during the design of a database, it is extremely rare to see a corruption
once I install the database (an MDE where the forms, reports and code are
not being modified) in a stable environment (reliable power and network)

I have hundreds of Access databases out there, from single-user up to one
that performs centralized stock control on a network of 6 companies, with
hundreds of thousands of clients each. Each company runs their own copy of
the sales database (6 Access databases), and these databases use a push-pull
system so that the centralized database is self-correcting. That one was fun
to design.

There are some features in the newer versions that you might enjoy, but
Access 97 is still an extremely stable and workable database. I still get
clients asking for new databases in A97, so pushing for expensive updates
might only serve to destroy your argument that Access is the most
cost-effective solution for your department. That's up to you.

But since your question was primarily about how to communicate with your IT
department, the crucial answer will not lie in arguments about technology,
but in genuinely seeking to understand their reasons for not wanting to
support more things, and in open and honest discussions about the best and
most cost-effective way to achieve the aims of your department. Lots of
listening and trying hard to understand why they have the perspective they
do.

All the best.

 

[Tiffany]

Thank you so much for your input.

Just for clarification...The Access upgrade was an issue brought up by our
IT department - not by my department - I apologize for not being very clear
about that. We actually stopped them from upgrading or it would have already
been done. IT was not aware of how many people had utilized A97. In my
opinion, they are saying that we must upgrade to 2003 in order to completely
halt our use of Access 97 (because the expense involved in obtaining 700+
licenses is not anything we can overcome without serious approval from
management). I have been perfectly happy to use 97 and was against the
upgrade from the beginning. At this point, I would not mind upgrading to
2003, I have some experience using it, and there are some features I would
enjoy but our team never pushed for the upgrade. I have not come across any
task that I have not been able to do in Access 97. Certain connectivity
issues (Mail Merge to Word) are a little outdated when using queries but
still workable with the DDE connection.

The security issue became of serious concern for us when I created a
timesheet database that has saved managers approximately 2-3 hours every two
weeks minimum. Plus, one helpful tool is that employees can find out with
the click of a button how much vacation, sick, and discretionary time they
are allowed, have used, and what is left. Previously, managers had to
calculate this manually. We store employee IDs (not SSNs, and not DOBs,
though) in this database and as a result, security is more important. HR had
a conniption because of this database. I believe that this database can be
made secure enough for everyone, including HR, but I can see the writing on
the wall already: Our team is not going to be given an opportunity to make
the database compliant with our corporate security policy. I would at least
like to show them the changes that can be made to make the database more
secure. If I am proven wrong, then at least I learned something about
securing electronic information and spent my time working on something
worthwhile instead of working crossword puzzles. ;-)

In any case, I very much appreciate your time and advice. BTW, your Audit
Trail code is AWESOME!

 

[Albert D.Kallal]

I can't add too much to Allen's response (Gee, Allen, that is a great
response...)

A few things more:

Access Issues: Our IT department has placed us on hold for the following
reasons:

1. Access is not secure enough and leaks information.

Ms-access is no less, or no more security then oracle, or sql server. The
security of ms-access is really dependent on what data engine you use.
Remember, ms-access is not the database, but is in fact a developers tool to
create software. You can not create a screen with Oracle. You can't create a
screen with sql-server. So, I think for the clarity here, you might want to
define what exactly ms-access is. You no more can call c++, or vb a
"database". The same applies to ms-access.

Ms-access works great as a front end to JET (the default engine), or
oracle, or sql server. As mentioned, sql server does NOT let you create a
form.
The oracle database also does not create a form. So, regardless of tools you
use, if you decide to use sql server, then what will your build the
interface with?

So, keep in mind that ms-access is developers tool like VB, and not a
database. If you want to expand on this concept, and perhaps have some
reading for your IT people, you can take a read of using ms-access in a
mulit-user environment here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

2. Access has data corruption issues.

No, ms-access does not. The JET database engine might, but you can use
ms-access with oracle, and if the data is corrupting, it is the fault of the
database engine, NOT ms-access. ms-access no less, or no more corruption
data when used with oracle as compared to vb, or c++. They all perform the
same in terms of data speeds (since you are using oracle), and I seen no
deviance of increased corruption when using ms-access with oracle as
compared
to VB. Without question, the JET file share engine can suffer corruptions,
but that is JET issue, and not one of ms-access.

3. Access is not suitable for more than 4 simultaneous users.

No, the above is 100% false. There are installations right now with 1000
simultaneous users all hitting the same database at the same time (in this
case, the database engine is sql server....but as I previous mentioned,
when you use VB, c++, or ms-access, you can CHOOSE what data engine you use
to store the data). So, for a two tiered environments, ms-access performance
the
same as using c++, or VB, there is NO difference in performance here, and
the
number of users you can scale to is the SAME for all 3 development tools in
a 2 tiered environment. (I repeat this again: the performance is the SAME
between all tools when used in a two tiered environment).

The reason we are still using Access 97 is simply that IT does not like
Access, and considers my team to be nothing more than "rope programmers."
If
we are going to continue using it, then we must upgrade to 2003, which IT
does not want to do because of the cost involved in obtaining the 700+
licenses that would be required.

Why purchase licenses for ms-access? You can purchase one copy of the
developers edition, and deploy royalty free to all users. People do this
with
VB, people do this with C++, and people do this with ms-access. In all 3
cases, the company does not have to purchase a copy of VB, or c++ for each
computer. So, why not deploy using the ms-access runtime? To me, the only
question here is why is there no knowledge of purchasing the runtime here?
(this would make me question the competency of the ms-access developers
here.....why not was this issue brought up? - you kind have to be living in
a sealed vacuum cave to be a ms-access developer, and not be aware of the
royalty free deployment options for ms-access. If this paragraph sounds
like a strike against the ms-access developer team..it is, since why was
this
option not offered? (there is NO reason to thus purchase ms-access in this
case).

You can also read about deploying ms-access in a WAN (wide area network) as
opposed to a LAN (local area network) here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Allen Browne]

Thanks, Tiffany.

Albert also had some good suggestions on how to present Access as very cost
effective. The runtime might be worth considering.

 

[Larry Linson]

Am I totally off base?

Can these issues not be avoided?

If these issues can be avoided what
can I do to convince the skeptics?

If these issues can't be avoided what
other options/solutions/programs
(besides Oracle, etc.) are available?

How can my department over come
the image of being "rope programmers"?

What is the point of using Access if it
has all these problems?

Any input is greatly appreciated.

Tiffany, I have been in the computer business since 1958, most of that time
spent in large organizations, and over half with the software part of a
major computer manufacturer. Alas, in [many | most] companies, the IT
department has "issues" with user departments programming, contracting out
application and database development, or licensing other services.

In a few companies, enlightened IT departments try to assist the user
departments to solve computing problems in the way that is best for the
users. In another few companies, user management has sufficient "clout" to
ensure that IT cannot block their user departments from doing what is best
for themselves.

Certainly, Allen and Albert have given you some good suggestions. I can
verify that Access, coupled with a server database back end (I've worked
with MS SQL Server, Informix, and two flavors of Sybase, as back ends) can
handily handle hundreds of users. I can verify, also, from personal
observation and from observations by people I trust, that an Access
multiuser (Access UI - Jet database) can support over 100 concurrent users
if all factors are right, and easily multiple tens of users, even if some
factors aren't quite right. All factors would have to be just about as far
from perfect as possible for a 4-user limit. I, if someone really tried,
they could create an Access database that wouldn't even support one single
user. Jet's security can be broken, but not every application is "high
security". And, when an Access client is used with a server database, it is
the server that protects the data -- in that case, Access is not an issue.

But, all that notwithstanding, what you have is a problem of internal
politics.

If your user management is sufficiently interested, and can either stand up
to IT management, or can work with IT management, your Access databases, or
Access client applications can probably be used and can likely provide your
company with the benefits you describe.

If IT management "rules the roost" when it comes to all computing in your
organization, they probably will not.

I have had customers using a mainframe "decision support" tool that could be
a front end to relational databases, who merely needed a development
database to test with, but whose IT deparments would not provide it, nor
would they let the users create their own -- the applications were never
completed, the company did not benefit, and no one was happy. Other customer
companies had IT departments that were eager to help, and did -- the
applications were successful, the company benefitted, and both the user
departments and IT were happy.

In my time as a consultant, all of the companies for whom I have done work
were of the latter kind. In many cases, the IT department were the ones who
brought Access in, provided training for the users, and nurtured power users
and developers in the user departments.

I am not sure what a "rope programmer" is. Closest I can come to that is
that certain computers on the Apollo spacecraft had their programs stored in
something called "memory ropes". But, believe me, the programmers of those
computers were professionals of exceptionally high caliber.

If those claims were all true, Access would not be as useful as it is, nor
as successful. It is the largest-selling database software of all time (and
comes with two different servers, the default Jet, and an installable
limited version of SQL Server called MSDE).

I hope my comments are of some help to you. Good luck.

My suggestion would be, if your management doesn't "win the day", that they,
not you, talk to IT about creating a server database to contain your data,
and you creating the user interface with Access to use that data in the
server DB. Most of my paying Access work has been working with Access
clients to server databases. And, a bonus for you, if you do this, you will
have gained some skills and you will be a more valuable employee -- to your
company, or perhaps some other company.

Larry Linson
Microsoft Access MVP

 

[Tony Toews]

Any input is greatly appreciated.

You've got some great answers.

One more thought. If IT takes away the use of Access by the users
then they will use Excel. Which in turn has so many problems when it
comes to sharing data, etc, etc. And if they take Excel away folks
will use Word. <smile>

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

 

[Larry Linson]

One more thought. If IT takes away the
use of Access by the users then they will
use Excel. Which in turn has so many
problems when it comes to sharing data,
etc, etc. And if they take Excel away
folks will use Word. <smile>

etc., etc. back all the way to "primitive word processing" (gasp!), a pad of
paper and some #2 pencils.

Larry Linson
Micrsoft Access MVP

 

['69 Camaro]

One more thought. If IT takes away the use of Access by the users

then they will use Excel. Which in turn has so many problems when it
comes to sharing data, etc, etc. And if they take Excel away folks
will use Word. <smile>

Or PowerPoint slides. And no, I'm not joking. If NASA didn't let Boeing
Space Shuttle engineers use their ancient mainframe databases, which, until
quite recently, the U.S. taxpayers had spent tens of millions of dollars on
per year for decades, these engineers would only have had the thousands of
PowerPoint slides and handfuls of Word documents they had handed in to
management in periodic reports over the years to use as their knowledge base
database on "best engineering practices" for repairs on the Space Shuttles.
But many of the engineers still dug through their colleagues' PowerPoint
slides to find previous solutions to problems because searching through
thousands of files on dozens of PC's was easier -- and quicker -- than
hunting for the same information in NASA's ancient mainframe computers.

People wonder why the average cost of launching a Space Shuttle and
returning it to Kennedy Space Center is reportedly about $1 billion. I
don't.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Tiffany]

Thank you all so much for your input. It has been very helpful!

Sincerely,

Tiffany