Re: What database shall I use???

I didn't say that, it depends upon how critical the data is to you and how much stability you need.
One could give examples where that would be the right choice and one can give examples where it is not the right choice. It depends upon what the business need is.

For example, someone has a olap need for marketing that draws from a variety of data sources (standard etl stuff). While the group that uses the information is small, the number of workstations is small, the quantity of data might be very large, the need to do this olap analysis might be a critical business need, and since the data is coming from a large number of sources it might be difficult to reproduce quickly if lost. In that case Access as a database would be a poor choice. (doesn't scale well, limited olap capabilites, limited OS choices - 1, not multithreaded etc.)

On the other hand, one can certainly come up with business needs where an Access database would fit quite well.

Jim
"David W. Fenton" <dXXXfenton_at_bway.net> wrote in message news:91CF9D56Fdfentonbwaynet_at_news-server.nyc.rr.com...
> kennedy-family_at_attbi.com (Jim Kennedy) wrote in
> <LIfj8.20536$702.11968_at_sccrnsc02>:
>
> >"David W. Fenton" <dXXXfenton_at_bway.net> wrote in message
> >news:91CEE9458dfentonbwaynet_at_news-server.nyc.rr.com...
> >> corey.lawson_at_worldnet.att.com (corey lawson) wrote in
> >> <3c8c2550.31870118_at_netnews.att.net>:
> >>
> >> >If you're only doing read-only access to Access [sic], then
> >> >Access isn't too bad, even for large MDB files. It starts
> >> >bogging down hard when your MDB size gets over ~20MB or so and
> >> >>5 simultaneous read-write users, generally.
> >>
> >> You're simply WRONG.
> >>
> >> Wrong.
> >>
> >> You obviously don't know how to design a properly functioning
> >> multi -user Access database.
> >>
> >> I have a client with a 375MB Access database (3 tables have over
> >> 300K records in them) with 5-10 simultaneous read/write users.
> >> Performance is just fine, there is no corruption and there are
> >> no concurrency problems.
> >
> >Any of these file based databases where there really isn't a
> >server, but is client based - which an Access database is - the
> >corruption factor - which when it hits does not tell you at the
> >moment it occurs - is very dependent upon the stability of ALL the
> >pieces of the network. For example, I have seen it have terrible
> >problems in places where the infrastructure is not as stable as
> >yours presumably is. In non-IT places there can often be problems
> >with stable power which can wreck havoc on an Access and other
> >file based systems database.(They are just not designed for such
> >abuse.) Granted people should have stable networks, but the
> >reality is that there is a lot of variance in quality of
> >infrastructure. Commercial RDBMS's (eg Oracle, DB2 , et al) are
> >designed to weather such abuses. They do cost more and require
> >more server hardware. But hey the right tool for the right need.
>
> So, you're telling me that my clients with 3 PCs, no file server,
> and peer-to-peer networking should buy a dedicated server to run
> Oracle/DB2, whatever?
>
> Hello? Are you nuts?
>
> The post I responded to said that 5 simultaneous users running
> against a 20MB MDB was the limit for an Access back end.
>
> This is so completely false that it deserves to be disputed
> vigorously.
>
> In regards to corruption:
>
> I've been in this business since late 1995 (developing Access
> databases), and the only times I've seen corruption at all has been
> in these circumstances:
>
> 1. Access crashed while writing the data for an update query (a
> large 300K-record append query; don't know why it crashed; had to
> send the file to Peter Miller for recovery; cost: <$300; downtime:
> 2 hours while diagnosing and restoring backup; data lost: none --
> the morning's work was recovered from the fixed data file when we
> got it back from Peter the very next day).
>
> 2. Client was using peer-to-peer network, and the PC that hosted
> the MDB file was running WordPerfect v. 6.0 on Win3.x. As WP6 was
> extraordinarily unstable, it would crash periodically, taking
> Win3.x down with it. Eventually, after enough such incidents, the
> file became unrecoverable. This would have been no problem if the
> client had set up their backup properly (it was backing up the
> wrong data), or if the user had been following my instructions
> (keep backup copies of the data file on her workstation, just in
> case the overnight backup didn't work). None of these configuration
> issues were my responsibility -- had they been, they would have
> been changed immediately (and they *were* changed after they lost
> data). The client lost 3 months of data, but if they'd been running
> their network properly (not using WP in the first place, verifying
> that their backup tapes were backing up what they *thought* they
> were backing up and that they were restorable, and the end user
> doing the manual backups according to my written instructions
> instead of relying on her imperfect memory of them), they wouldn't
> have experienced corruption in the first place, but if they had,
> they would have lost a day's work at the very most.
>
> 3. Access 2K back end on Win2K server with opportunistic locking
> ON, the index of the primary key of one table became corrupt. About
> 200 records were lost, but we recovered all but 3 of those from
> backups. This was very shortly after the point at which MS
> identified the OpLocks problem with Jet 4 databases. I don't like
> A2K, in any case, and keep most of my clients away from it, where
> possible.
>
> 4. A replicated database kept losing replicability, while
> otherwise, data integrity remained fine. The problem was eventually
> traced back to an Exchange Server hot fix that had been applied to
> the server on which the replicated MDB lived, despite the fact that
> the client did not even *use* Exchange Server. Backing out the hot
> fix ended the corruption. No data were lost, since I used a replica
> farm and scheduled synchs to keep data backed up at 15-minute
> intervals. Perhaps some data entered in the 15 minutes before the
> loss of replicability was lost, but the client was not concerned
> about this at all.
>
> That's it.
>
> We're talking about about 30-50 different databases, all shapes and
> sizes, all of them Access applications talking to Access back ends,
> running in networks of 2-25 PCs, with 2 to 15 simultaneous users
> (15 is the point at which client/server starts to pay off, but it
> also depends on the specific application and the size of the data
> store).
>
> When I am hired to create an Access database application for the
> client, part of the specification and planning process is a review
> of the client's network infrastructure. If there is anything
> questionable, that has to be replaced and brought up to spec before
> I'll agree to do the work. Why? Because it's a part of the cost of
> having an Access application -- you must have a stable, reliable
> network. But the cost of that is actually not all that high when
> combined with the cost of the Access application overall,
> especially when compared to the cost of the alternatives.
>
> In short, it's far cheaper to clean up your network (and also
> improves productivity when you get rid of old clunker PCs) than it
> is to take on the administrative expense of a database server.
> Installing Oracle or SQL Server just to avoid upgrading your
> network is one of those penny-wise, pound-foolish moves that I
> would never recommend to a client. If any client suggested it to
> me, I'd probably withdraw -- I don't need clients like that.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc
Received on Tue Mar 12 2002 - 20:39:14 CST