Looking for an alternative to Access

[steve12173]

I have an Access Database that is becoming too large and slowing down
dramatically. I am looking for an alternative. I have a team of about 40
reps that enter information about customers. I need to have the fuctionality
of Access (Forms, Reports and, Queries) and be able to update the records.
Is Sharepoint a viable option or do you have any other ideas?

 

[Roger Carlson]

I'd move the data to SQL Server before I'd move to Sharepoint. You could
move your data to SQL Server and keep your existing Access application
(forms, reports, etc.). Putting your data in SQL Server, however, doesn't
automatically speed things up. You may need to modify your application to
improve performance.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Albert D. Kallal]

I have an Access Database that is becoming too large and slowing down
dramatically. I am looking for an alternative. I have a team of about 40
reps that enter information about customers. I need to have the
fuctionality
of Access (Forms, Reports and, Queries) and be able to update the records.
Is Sharepoint a viable option or do you have any other ideas?

SharePoint is really for smaller workgroups. However, there is a free
on-line edition

www.officelive.com

So, it is very cheap and affordable (can't beat free). However, in your
case, I don't think SharePoint is the solution.

Without more details it's hard to ascertain what your best solution would
be, but as the other ghost is in this thread hint, the most reasonable
solution in these cases is to move your back and data part to SQL server and
keep the rest of the functionality and continue to use access as you do now.
the such a setup should allow you to scale the hundreds of users, and you'll
have a more secure robust and more reliable system. the beauty of such an
approach is that you're able to continue to use your standard access
development tools, but you put the data on a high speed data server.

I assume that in your current environment, you currently have a split system
in which the front end (application part) is placed on each users
WorkStation, and the data file resides on some file server? correct?

The next step up in evolution would then be to take the backend part and
move that data part to SQL server, This would allow you to continue to use
your current development system (access) Enable you to scale to many more
users.

 

[John W. Vinson]

I have an Access Database that is becoming too large and slowing down
dramatically. I am looking for an alternative. I have a team of about 40
reps that enter information about customers. I need to have the fuctionality
of Access (Forms, Reports and, Queries) and be able to update the records.
Is Sharepoint a viable option or do you have any other ideas?

How large? Approaching 2GByte compacted? How many rows in the largest table?
How many *concurrent* users (you've got 40 reps, are they all hitting the
database at the same time)? Have you (I HOPE!!!) split the database into a
backend containing the tables and individual frontends?

It's very likely that moving the tables to SQL will be helpful, but whether
you do so or not, it's really important to optimize your tables' indexes and
the structure of the queries; forms should be based on selective queries
(ideally returning only one record) rather than directly on tables, etc. There
are lots of tricks to optimize performance, and many of them are even MORE
important with a SQL backend than with native JET.

 

[Larry Linson]

I have an Access Database that is becoming too
large and slowing down dramatically. I am looking
for an alternative. I have a team of about 40 reps
that enter information about customers. I need to
have the fuctionality of Access (Forms, Reports
and, Queries) and be able to update the records.

The first thing you should do, the very first, is to look at websites that
address the issue of performance. There's a good list of MVPs' websites at
http://sp.ntpcug.org/accesssig/default.aspx. Three that I would suggest as a
start are Tony Toews', Jeff Conrad's, and Allen Browne's -- but others on
that list will aso have information on the subject.

As has been said, splitting into a Front End and Back End would be a start;
"selective retrieval", that is, not displaying an entire table for the user
to select a single record, but selecting using a query to limit the number
of records that actually have to be retrieved (it's a revelation to realize
how many times the number of records that should be retrieved is "one" if
the record exists, or "zero" if the desired record does not); appropriate
indexing, but not "over-indexing" your tables are additional items -- you'll
find other good tips at those websites.

Moving to a server back-end can, in some cases, improve performance; in
other cases, it can degrade performance. Don't move to a server expecting
an instant improvement.

It your 40 users are not all _concurrently_ updating the database from their
individual Front End apps, and if they are on a LAN (not WAN) with the Back
End in a shared folder on a server into whose domain they are logged, then
there's a very good chance that you won't have to change the backend (at
least, not yet).

Is Sharepoint a viable option or do you have any other ideas?

Microsoft, IMNSHO, included functionality in SharePoint and Access to allow
Access to be a Front End to SharePoint, because they did not have a front
end that did not require web implementation. They did not do so in order to
give Access a performance-oriented back end.

We see/hear enough complaints about the responsiveness that I am certain
this is not a productive approach for you to pursue at this time.

Larry Linson
Microsoft Office Access MVP

 

[James A. Fortune]

I have an Access Database that is becoming too large and slowing down
dramatically. I am looking for an alternative. I have a team of about 40
reps that enter information about customers. I need to have the fuctionality
of Access (Forms, Reports and, Queries) and be able to update the records.
Is Sharepoint a viable option or do you have any other ideas?

I feel that SharePoint has an outstanding feature that has been totally
neglected by Microsoft's marketing department, but who am I to argue
with success? However, that feature is not the solution to your
problem. Access, for some of the reasons you mention and for some you
did not mention, is very tough to beat. Lyle likes Visual Web Developer
from Microsoft:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/43a04f8f17dcaac0

My decision upon reaching a similar point was to convert any forms that
are likely to be used concurrently for editing by over forty users to
unbound forms. To keep things simple I noticed that for most of the
editing being done nobody minded if the last edit overrode the previous
one. That allowed me to keep running Access in an environment with
about 70 concurrent users and about 110 total users. It also allowed me
to have less changes required when converting Access to a web
application, but I did have to be more careful about avoiding nearly
simultaneous edits with things like semi-random delays when trying to
grab a record exclusively for enough time to update the record.

My next stage will probably involve a combination of Access, the .NET
Framework and Axum, but I'm always looking for better ways to do things.

James A. Fortune
(e-mail address removed)

(Warning: Book spoiler alert!)

Graham Hancock, in his book "The Sign and the Seal," postulates that the
Ark of the Covenant was hidden away for hundreds of years in the Kingdom
of Axum (now Ethiopia). He further speculates that a letter from Axum
to the Papacy complaining that the Knights Templar were about to take
control of the Ark of the Covenant was instrumental in influencing Rome
to take action that ultimately led to the dissolution, but not total
destruction, of the Order.

 

[Armen Stein]

I have an Access Database that is becoming too large and slowing down
dramatically. I am looking for an alternative. I have a team of about 40
reps that enter information about customers. I need to have the fuctionality
of Access (Forms, Reports and, Queries) and be able to update the records.
Is Sharepoint a viable option or do you have any other ideas?

I agree with the other responses that moving to a SQL Server back-end
database might be a good solution, but it can't do miracles. Your
application needs to follow some basic client-server principles to get
the real power. Without them, it's possible for the SQL back-end to
be even slower.

I've written a slideshow on techniques for using Access as a
client-server front-end to SQL Server databases. It's called "Best of
Both Worlds" at www.JStreetTech.com/Downloads. Coincidentally, I'm
presenting the same topic at TechEd in LA in just a few hours.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com