when to upgrade?

M

Mike Painter

DH said:
How do you know you've outgrown access? What's the next step?
Click to expand...
When you find tools in other applications that make what you are having a
hard time with in Access easy.
The next step might be a better tool or a lower level language.
 
R

Rick Brandt

DH said:
How do you know you've outgrown access? What's the next step?
Click to expand...

If you mean using an Access MDB to store your data that is largely determined by
security requirements and concurrency requirements although in some cases the
actual size of the database can be the reason. Security and concurrency will
become a problem when they become a problem. Trying to predict the point where
that will occur is difficult because so much of it is dependent on how well your
app is set up.

For most people the "next step" is a server back end like SQL Server. I always
caution people to be careful about going to managment about a "need" for SQL
Server unless it is about security, data integrity, and concurrency. I would
never sell it as a way to "speed things up" although that could happen in
certain situations. However; if there is already a SS box on the LAN or
management has already decided to put one in place I would also say that there
is never a reason NOT to move the data there from any existing MDB except for
the most trivial apps.

If you mean using Access as the front end interface I have seen no evidence that
you can *ever* outgrow Access as I have not seen any requirements that I
couldn't satisfy using it. The most common reason to replace the interface is
when it becomes a requirememtn to implement something that can be used in a web
browser. Other than that I see no reason not to use Access.
 
A

Alex White MCDBA MCSE

Totally agree with the Access as a front-end, I have never seen a situation
where someone has outgrown Access as a front-end, as a back-end I don't
think anyone mentioned 2GB file limitation, hit this and you have a problem,
if you have more than 1.5 gb of data and do lots of inserts/deletes/edits
you can hit this limitation quickly if you are not compacting the database
frequently.

I have a simple rule that I run by and it is this volume of data * max
concurrent users > 2-3gb I start to prime my client about SQL as a back-end.
The other situation I come across (inherit) quite often is this main mdb,
with loads of archived mdb's because they has run into problems with volumes
of data.

As a front-end it works great, I don't think developments systems like vb6,
vb.net produce better performing front-ends than Access, they may look
better, may feel more robust but in the real world Access is an excellent
front-end database tool.

The other plus point of Access as a development system is, in my personal
view 2-3 times quick in producing database aware applications than programs
like vb6 and vb.net.

As has already been stated be careful of 'selling' to others things like
SQL is a better performing system, because out of the box, upsized
applications will NOT perform any better than the equivalent MDB back-end,
SQL brings scalability and robustness to the equation, to get performance
better than MDB's requires a lot of understanding of how SQL works and a
major re-write to your front-ends to squeeze every last bit of performance
of out SQL.

How often to you compact and repair?
 
A

Arvin Meyer

Like the others, I agree that you'll never outgrow an Access front-end, with
the possible exception of needing a web front-end for an Internet based
application. I'd be careful about the "need" for an Internet based
application because they are significantly more time consuming and expensive
to build, fail more frequently, and have the potential of being less secure.
Always think long and hard before choosing to go the Internet route.

There is one universal reason for using a SQL-Server engine instead of the
Access Jet engine, and that is security. Without question a server based
engine will be more secure than and a file based engine. The file based
engine is portable, the server is not (at least not without extreme effort).
Most applications do not require much more than a secure network, but some,
such as medical databases that are require to conform to HIPPA laws, should
not be used in Access if any outside data-loss is possible. Of all the
databases I've written over the last 12+ years, less than 10% had a security
requirement, and all of those were easily accommodated by Access security
due to no further requirement than a secure network.

Now we come to user count on the back-end. My biggest user count thus far is
51. That's more than I'd normally recommend, but the way the database is
designed, I can probably accomodate 50 more without problems. The "official"
limit is 255. Well, maybe for read-only, but I would never recommend
anywhere near that number. A well-designed database can easily handle 20
users, I've seen quite a few with up to 50. The telling time to move to a
server based solution is when you start getting record locking conflicts. If
they happen frequently, SQL-Server is in your not too distant future.

The largest Access database that I'ver ever seen was 30 GB (yes I said
Gigabytes) It was built in Access 2.0 and ran from a DEC Alpha server with 2
users. It was able to achieve that size by chaining 79 separate tables in 79
separate databases to a single front end. That is an unusual case. A typical
Access database will become slow and unwieldy after 500 MB to perhaps 1 GB
in versions 2000 and later. Even if you don't have corruption problems
performance won't be snappy, especially with more than 5 or 6 users.

Performance is an issue only when table-size is large and query design is
poor. With the availablity of cheap memory and fast networks, JET can often
out-perform SQL-Server until larger numbers of users start querying more
records. Then network packet collisions will require resending some packets,
and SQL-Server will ride over top of Access without so much as a "hello
world" on the way by. Stored Procedures and Views run on a powerful server
can eliminate the network traffic problem. Server "robustness" is really
transaction logging, which, if turned off, doesn't make the server much more
robust than a well maintained workstation with an intelligent operator. When
turned on, it eats up some of the touted performance gain, so the trade-off
requires an intelligent database administrator as well as a good developer.

I still hold to my first answer without any specific criteria to work with.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
T

tina

such as medical databases that are require to conform to HIPPA laws

just as an aside, that's HIPAA: the Health Insurance Portability and
Accountability Act of 1996, also called Public Law 104-191.
 
W

wuzhangwu126

Mike Painter said:
When you find tools in other applications that make what you are having a
hard time with in Access easy.
The next step might be a better tool or a lower level language.
Click to expand...
 
Top