[Novalug] [Off-list] Re: database schemas

[Bryan J. Smith]

Big-time correction here ...

MS Access is a 100% _front-end_only_ solution.  A huge mistake people
make is that MS Access is a "back-end."  It has never been and will never
be a "back-end" database.

MS Access is ... and I'll quote Wikipedia here ...

  "a relational database management system from Microsoft that
   combines the relational Microsoft Jet Database Engine
   with a graphical user interface and software development tools."

Which leads to the Jet Database Engine ... quoting Wiki again ...

  "Jet has become almost synonymous with Microsoft Access, to the
   extent where many people incorrectly refer to a Jet database as
   an
"Access database".[citation needed] Even Microsoft themselves
   do this sometimes, but this nomenclature
should always be seen
   as incorrect. Jet is a database and Access is a
database application
   development tool (database builder)."

When people "plop down" an Access database on a file share, they
are using Jet.  And Jet likes Op-Locks, which are the default in Windows
Servers and cannot be disabled (except server-wide with a registry
hack).  Op-Locks regularly result in Access file corruptions, as Windows
clients row-lock and corrupt each other.  And even when I disable Op-
Locks on Samba servers (which can be done on a per-share basis,
which a major performance hit), I still regularly get corruptions.

Jet was at the heart of ActiveDirectory, MS Exchange and early
versions of SharePoint as well.  Jet is the reason why AD and Exchange
are known for their extensive set of corruptions -- largely when you have
more than one server involved with data sharing (although issues were
far more mitigated by the fact the service handles the data exchange,
and the Jet engine isn't directly tapped by a remote server).

Over the years Microsoft had enough issues that it tried to pare down
its MS SQL engine into various forms that could be bundled.  This is
known as the Microsot Desktop Engine (MSDE):  
  http://en.wikipedia.org/wiki/Microsoft_Desktop_Engine  

Despite the fact that the MSDE forces a "centralized server" locking
engine with at least the Atomic (of ACID) being enforced, many users
and Verticals ignore it because it "complicates" setup.  They still fall
back to Jet and other hacks.

Or worse yet, they are purposely trying to sell you the "Enterprise
Server" version of their vertical app.  I had one accounting department
"override" my concerns on a $25K GAAP Accounting System.  I argued
we'd be better off buying a $75K ERP System (from Oracle -- this was
2000) that ran on Linux, natively.  We had corruption after corruption and
spent another $30K just on the "Enterprise Server" version which was the
sole Windows NT 4.0 Server in the company (they didn't even have a
Windows 2000 Server version yet -- a full year after 2000 was available).

In the end I found that it just needed an ODBC store, and used Postgres
with the Postgres ODBC client for Windows (yes, back in 2000 ;).

To make matters worse, if you base anything on Access, the specific
version of Access in use by the vertical app may _conflict_ with the
version of Access Data Objects (ADO) / Microsoft Access Data
Components (MDAC) of the installed MS Office version.  Especially
if the vertical application is older.  The only way to deal with this is,
not surprisingly, virtualization -- or run an older MS Office version.
This was a real PITA because this vertical app was Access 95 based
and it was impossible to run MS Office 97 (let alone 2000 when they
finally gave us the 97 update).

This has not changed in recent years either.  There are still many
vertical apps out there that are Access 2000 based and have issues
with people running MS Office 2003 or 2007, even if they don't have
the versions of Access 2003 or 2007 either.

It's really a PITA when a single, vertical app forces you into this pickle.
And it is completely unheard of outside of the Windows world too
(UNIX/Linux have multi-version lib support, and MacOS components
are self-contained verticals).  I highly recommend virtualization to
avoid such issues, if they need to run MS Office as well.

Remember that there are countless "visual database front-ends" in
the Open Source world that let you do everything that Access does.
They just force you to have a real client-server store, with no Jet
option.  They are no more difficult to setup than MSDE, and have
far, far more capability.  PostgreSQL is not difficult to configure at all
for access, and even the ODBC for Windows (and/or JDBC for Java).

If you search Freshmeat, you might be surprised how many different,
home-grown vertical apps are out there that handle similiar needs.
Many can be adapted or even used as-is.



----- Original Message ----
From: Opus <opus at slowlanecafe.com>

greg pryzby wrote:
> I was talking with a non-profit today and they are looking at purchasing 
> a 'solution', campbrain.com. It looks fine. Of course it is Access and 
> about 4k for what they want and I think they can do better-- using open 
> source, of course!
>  
Hi Greg,

I don't know of any products tailored to that particular market.  I'm 
currently building a different product for a client using PostgreSQL 8.4 
with a Java UI (yeah, yeah, I know... but I like Java, the JRE is freely 
available, and it is, in my experience, reasonably cross-platform - 
please no flames as this is not the focus of my post.

So what is the focus of my reply to Greg?  IMHO, this is one of the 
shortfalls in Open Source and Linux software - the "vertical market" 
apps that integrate a database backend (usually Access (ugh!), BDE, or 
Pervasive) with a custom UI (again, a lot of MS Access) that implements 
some business rules, policy, and intelligence.  I guess that developers 
target Windows because it has the largest market share.  I wonder if 
this will change as businesses migrate to SaaS and web apps, etc.

Greg, I know this was not helpful, but I needed to get it out of my 
head.  I hope you find an Open Source solution.