When to convert access to SQL

[Bart Tecter]

At what point (users/database size) should I consider converting an Access
application to SQL? We have 3-4 users with a database that is 200-300 MB in
size. The application has crashed in past and we have been told by a
programmer that we should upgrade (at considerable cost) to avoid future
crashes. I would have thought we were well within the working capability of
Access. Any comments would be most appreciated.

 

[Douglas J. Steele]

Realistically, 200-300 Mb is a moderate size only, and 3-4 users is quite
reasonable for Access.

If your network's a bit flaky, though, it might make sense to upsize: Access
is very sensitive to network problems.

 

[Albert D.Kallal]

I assume that 200-300 megs is after compacting the back end?

Further, you are running a split database...right?

You can read as to why you split here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

As a few others have mentioned, it don't sound liked you reach the end of
the road.
However, often a source of instability and problems is a incorrect setup.
(so, for example...you need to split your database).

So, if you got a good setup, then you should be ok. I assume a backup is
done daily?

Further, perhaps some of the data can be archived out?

So, #1, are you running a split system?

 

[Bart Tecter]

No, the database is not split from the access application. Is this a common
practice? My personal experience goes back to Dbase and Clipper and there
ofcourse the data files, index files and program files were all separate
files. (Honestly, when I first looked at Access it freaked me out a bit to
have everything wrapped up in one file.)

 

[Albert D.Kallal]

No, the database is not split from the access application. Is this a
common
practice?

Yes, it is common practice, and really the ONLY way to ensure reliable
operation in multi-user mode.

If you read that article of mine, then you will agree that most systems you
*did* install the software on each computer.

To be fair, some of those older systems did often let you share the .exe
part of the program, but then again, even they would have been more reliable
if you installed the software on each computer.

The problem is that access has so many features (filters etc) that
temporally get saved with the form. When you got multi-user, that stuff
tends to mess up things, and is just not reliable.

That "single" file concept of ms-access is really a great feature (you can
transfer files, email them etc). However, this single file is also a problem
when you go multi-user. I still love the single file mdb concept. And,
ms-access does have the tools and ability for you to split out the
application part. When you split, you got a great solution, as then you can
change the data back end at will. You can also email a update as a new front
end to people.

Really, to develop, I can't see how any application can be maintained or
developed without having been split. (what does one do..have everyone go
home for the day while you work?). It is just not workable.

As a warning, often people finds the performance tanks when they split, but
if you keep a persistent connection from the front end to the back end, then
performance usually returns to un-split levels.