Jet vs MSDE vs postgresql

[David P. Lurie]

Acccess 2003 Developer Extensions

An electronic medical records system for my office is being developed, using
Jet for the initial prototype. The eventual production version may need to
use a different backend.

The number of users at any time will be light, less than five, but the
database size will eventually grow past 2GB. Data once entered would rarely,
if ever be changed, but old data needs to be available for queries.
Archiving records yearly to new Jet or MSDE databases would keep the size
within limits; I was planning to use UNION queries to concurrently retrieve
data from archived years. The office server runs SCO OpenServer 5.0.7, such
that it can't run IIS, but the number of users is low enough that IIS on my
XP system could host data access pages.

Replication (or possibly just one or two local and remote(LAN) scheduled
database backups per day) is needed to ensure system availability in the
event of hardware failure. Remote access via VPN is needed for both myself
and my transcriptionist. That would require MSDE or postgresql rather than
Jet, unless data access pages are used.

I've gotten client-server functionality from access to postgresql to work,
but staying within Access is easier.

Questions:

1. If data access pages are used, and office web components installed on
systems without MS Office, are data access pages enabled for data entry on
those systems, or just data display? If just for display, would installation
of an Access runtime on such a system enable data entry? Access help
describes View-only mode for office web components, but only lists
spreadsheets, charts and pivot tables. The application woud only expose
forms and parameterized reports as data access pages.

2. What type of data integrity and repair is available with MSDE, such as
with power or non-hard disk hardware failure? The level available with Jet
is probably sufficient, and the automatic recovery of postgresql is even
better.

Thanks,

David P. Lurie

 

[Arvin Meyer]

Both JET and the MSDE have a 2 GB limit. That ends your quest. You must use
another product.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[David P. Lurie]

Both JET and the MSDE have a 2 GB limit. That ends your quest. You must use
another product.
--

What about linking to other archived Jet or MSDE databases from a current
database kept under 2GB? Data archived in the archived database(s) is only
needed for queries. I thought that the 2GB limit only applied to tables
within the current database, and that overhead from the linked tables only
consumed a modest amount of space.

Also, what type of data integrity/crash recovery is available for MSDE? None
of the documentation describes repair/crash recovery.

I was leaning towards MSDE, with linked tables to any archived MSDE
databases. I will probably go with postgresql if linking to archived tables
isn't feasible.

David P. Lurie

 

[david epsom dot com dot au]

Yes, Jet can handle data > 2GB by using multiple linked tables.
However, the file system is not optimised for files that large,
and it will be very slow for some operations.

Don't know about MSDE direct support for multiple databases.

I'm sure that the standard operations for data integrity/crash
recovery are available for msde, but possibly not the tools
that make it workable: one of the differences between msde and
SQL Server is that some of the tools and documentation are missing.

I think that your biggest problem is going to be the problem
you are already experiencing: lack of documentation, support,
and general knowledge about using DAP or MSDE for a large
critical data set.

I personally would not dream of using SQL Server, even in its
MSDE form, for a critical data set, without the support of a
professional DBA. (and it was unpatched copies of MSDE that
brought down large sections of the web early last year).

And for whatever reason DAP never really became popular: not many
people here or anywhere have any experience.

(david)