Access Not Adequate

[angeltink99]

I work for a very large agency that currently uses an access database. We
track thousands of accounts, based on telephone numbers, and a cryptic
billing name that we run many reports based upon. We enter billing
information for these accounts each month. They are all associated to a
tracking section of the database, with assigned tracking numbers. We also
upload information directly into access using CDs with data that we receive
from many different vendors. We track very diverse and cumbersome
information and need to be able to run reports on it all. The current system
we are running on is becoming very corrupt and no one in our organization
have the knowledge to fix it. We don't have the resources or brain power to
scrap it and start again in access either. We need a system that is perhaps
off the shelf that can integrate many types of specialized data and allow us
to manipulate it and share it within our office and outside our office. It
needs to be able to support many users at once. I need to push for a new
platform, but don't know what to suggest or where to go with this one. The
budget is an issue. Any help from anyone who knows a lot about databases and
accounting would be greatly appreciated.

 

[Gina Whipp]

Angeltink99,

I'm not sure that Access is not adequate but seems as though you have made
up your mind. If you are looking for something other then Access I would
suggest 'Googling' whatever type of software you are looking for. This
newsgroup is dedicted to Microsoft Access so asking here for something else
is like going into BestBuy and asking to see shoes for sale.

If you are willing to reconsider your statement you might perhaps be
interested in Access-SQL Server-Crystal Reports which will give you what you
need. I would also consider contacting your IT department or a consultant
in your area (do PLENTY of research before hiring any consultant) to talk
about options.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dirk Goldgar]

I work for a very large agency that currently uses an access database. We
track thousands of accounts, based on telephone numbers, and a cryptic
billing name that we run many reports based upon. We enter billing
information for these accounts each month. They are all associated to a
tracking section of the database, with assigned tracking numbers. We also
upload information directly into access using CDs with data that we
receive
from many different vendors. We track very diverse and cumbersome
information and need to be able to run reports on it all. The current
system
we are running on is becoming very corrupt and no one in our organization
have the knowledge to fix it. We don't have the resources or brain power
to
scrap it and start again in access either. We need a system that is
perhaps
off the shelf that can integrate many types of specialized data and allow
us
to manipulate it and share it within our office and outside our office.
It
needs to be able to support many users at once. I need to push for a new
platform, but don't know what to suggest or where to go with this one.
The
budget is an issue. Any help from anyone who knows a lot about databases
and
accounting would be greatly appreciated.

It sounds like you have a complex and specialized Access application, and
you may have trouble finding any off-the shelf application that won't need
extensive work to make it do what your current system does. What exactly is
it that has convinced you that Acces isn't adequate to your needs? You may
well be right, but it's hard to say from what you've posted. Your
specifications, "share it within our office and outside our office" and
"support many users at once", *might* indicate that Access, or at least the
Jet database engine that Access uses by default, is not suitable for you,
but we'd need more detailed information about exactly how you want to share
it and how many users will be using it at once -- and in what ways -- to
determine that.

When you say your current system "is becoming very corrupt", what exactly do
you mean? There are forms of corruption that can strike Access, but they
are usually not that hard to cure. Also, there are things that can be done
to minimize an Access application's vulnerability to corruption, and
correspondingly there are mistakes that can be made in setting up an Access
app that make it very prone to corruption. It could be that just a little
work and re-architecting would fix your corruption problems, if that is
indeed the sort of corruption you are referring to.

If Jet isn't suitable for your back-end data store, it's possible you could
save yourself the trouble of redeveloping your application from scratch by
upsizing it to a SQL Server back-end. The idea would be to use your current
Access front-end with as few changes as possible, but have the data be
stored in a SQL Server database, which is more robust and can handle many
more simultaneous connections. Of course, you would have to have SQL Server
installed on a server in order to do this.

 

[Albert D. Kallal]

I don't really have a lot to add to Dirk's great answer.

As mentioned, for reliability and to reduce corruptions in a multi-user
database
environment, you need to split your database. I explain this here:

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


As for outgrowing the platform, that is difficult to ascertain with the
information you given. In 99% of the cases in which the application runs
slow
it is usually the fault of the developers and NOT that of ms-access.

Also, keep in mind you can move your data to sql server and continue to use
ms-access as "is" with relatively few changes. In other words, you really
can't outgrow ms-access if you use ms-access + sql server.

Keep in mind that there are several editions of SQL server that are also
hundred percent free to install and use and they work very well with MS
access.

Also, does the Application run slow with 1 user, or is it say after 15 users
it
starts to slow down?

You have to give some additional information as to the number of tables and
rows in the larger tables to start some type of analysis if you in fact
have outgrown ms-access or you have bad designs.

If you really have outgrown ms-access then you can keep using ms-access as
the application part and simply move the data part to sql server. That setup
can scale to 100's of users and tables with millions of rows. This approach
also allows you to continue and keep your current development system and not
lose all the money and time spent building the system that you have now.

If you only have about 12 users and very small tables that are typically
50,000 to 150,000 records in size, then your Application should be stable
and
absolute scream in terms of performance. If you tables are typiclly in the
millions
of rows then sql server would provide an upgrade path and allow you to
continue using your access application as a front end to SQL server.

 

[Tony Toews [MVP]]

We need a system that is perhaps
off the shelf that can integrate many types of specialized data and allow us
to manipulate it and share it within our office and outside our office.

The problem is that "off the shelf" and "specialized data" are mutually exclusive.

Access can support users outside the office either via Terminal Server or via SQL
Server.

It
needs to be able to support many users at once. I need to push for a new
platform, but don't know what to suggest or where to go with this one. The
budget is an issue.

Then I'd suggest staying with the current system and seeing how things can be cleaned
up by competent personnel. Start by listing your pain points of which you've got a
rough idea.

Also read the excellent article by Joel Spolsky. It's nine years old but very
relevant.

Things You Should Never Do, Part I

"Netscape 6.0 is finally going into its first public beta. There never was a version
5.0. The last major release, version 4.0, was released almost three years ago. Three
years is an awfully long time in the Internet world. During this time, Netscape sat
by, helplessly, as their market share plummeted.

It's a bit smarmy of me to criticize them for waiting so long between releases. They
didn't do it on purpose, now, did they?

Well, yes. They did. They did it by making the single worst strategic mistake that
any software company can make:

They decided to rewrite the code from scratch."

http://www.joelonsoftware.com/articles/fog0000000069.html

Also Gina and Dirk have asked some good questions.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Also Gina and Dirk have asked some good questions.

I see Albert jumped in as well and I'm sure others will too.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I work for a very large agency that currently uses an access
database. We track thousands of accounts, based on telephone
numbers, and a cryptic billing name that we run many reports based
upon. We enter billing information for these accounts each month.
They are all associated to a tracking section of the database,
with assigned tracking numbers. We also upload information
directly into access using CDs with data that we receive from many
different vendors. We track very diverse and cumbersome
information and need to be able to run reports on it all. The
current system we are running on is becoming very corrupt and no
one in our organization have the knowledge to fix it. We don't
have the resources or brain power to scrap it and start again in
access either. We need a system that is perhaps off the shelf
that can integrate many types of specialized data and allow us to
manipulate it and share it within our office and outside our
office. It needs to be able to support many users at once. I
need to push for a new platform, but don't know what to suggest or
where to go with this one. The budget is an issue. Any help from
anyone who knows a lot about databases and accounting would be
greatly appreciated.

It sounds to me as though your Access app is badly designed. In
other words, it's not Access that's inadequate to the task, but the
application that someone created in Access.

If you're corrupting data files, then determining why is the first
step towards fixing the problem:

1. if the app is not split, then split it.

2. if it's split and users are sharing a front end, then give each
user a front end.

Those the two most common causes of instability in multi-user Access
apps, failure to split, and front end sharing.

If those things are already in order, you must have an inadequate
network or a problematic software environment. Troubleshooting that
can be extraordinarily complicated. It might just be better to
upsize the back end to SQL Server, but that's not without its
pitfalls, especially if your Access application was not
professionally designed with upsizing in mind.

I would say that whatever the cause of the problems, the most
expensive and time consuming solution will be the one that trashes
the existing application and attempts to build it from scratch.
Don't assume that a commercial package will be any more reliable
(even if you can find one that suits your needs or is customizable).
Lots of people are out there selling things that don't work.

My recommendation is to find a professional Access developer with
good recommendations and have her evaluate the situation and make
recommendations. Likely, you'll get a list of options running the
gamut from fixing the existing app to scrapping it entirely and
starting from scratch. And you'll likely get some ballpark estimates
of the cost for each.

My guess is that you'll immediately see that fixing what you have is
likely to be the cheapest option. But it does require an Access
developer who is actually competent and experienced with this kind
of thing. That's not your run-of-the-mill "professional" Access
developer, unfortunately.

You might try directly contacting some of the developers who post
here, as many of them are very competent.

 

[David W. Fenton]

If you only have about 12 users and very small tables that are
typically 50,000 to 150,000 records in size, then your Application
should be stable and
absolute scream in terms of performance.

I'd make that up to 500K records, as I had an app that I kept
recommending upsizing and the client kept balking at it, but it ran
really fast anyway, and was completely reliable. The main table was
350K records, with a child table with 500K and a second child table
with 650K (rarely used). Sure, I'd done a lot of work to make it
efficient, but I was stunned at how long it ran well with so many
records. It was eventually upsized, and the result has been
underwhelming.

 

[David W. Fenton]

Access can support users outside the office either via Terminal
Server or via SQL Server.

Or Jet replication.

 

[Arvin Meyer MVP]

I just delivered a single user app that the user wanted to run from his
laptop. The data is static but there are over 11,780,000 records. It doesn't
scream, but it can pull reports which contain the sums and averages of
perhaps as many as 10,000 rows in about 1 minute. Previous to my app, there
were 12 text files with about a million records in each, that took hours to
search.

Another app has 53 users both reading and writing with about 250,000 records
in over a dozen tables (about 110 MB). That one was very fast pulling most
reports in a second or 2.

I also am now working on a SQL-Server app that has 7 users and takes 3 to 5
seconds to read through only about 35,000 records. That one needs some
serious indexing done on it to make it more responsive.

Good design, more than which DBMS engine makes good databases. Of course
that's subject to the limitations of the engine specs. My guess is that the
OPs database has been neglected for so long that it needs a skilled database
developer to implement maintenance and some changes.