[XML4Lib] XML database with an MS-Access front end

[Cary Gordon]

It is certainly possible to do what you want. SQL Server 2000 has decent XML
support and 2005 has very good XML support.

This kind of thing is easest to do in .net. You could make selected data
available via HTTP or HTTPS with the potential for decent security. No ODBC
required.

It would take a lot more effort to do this in Perl.  I am not, in any way, a
.net promoter, but you can be sure that someone has done this in .net and
documented it.

Cary Gordon
The Cherry Hill Company
http://www.chillco.com


-----Original Message-----
From: xml4lib-bounces at webjunction.org
[mailto:xml4lib-bounces at webjunction.org] On Behalf Of Bullen, Andrew
Sent: Wednesday, April 04, 2007 8:36 AM
To: xml4lib at webjunction.org
Subject: [XML4Lib] XML database with an MS-Access front end

All--

I am in the process of moving our database of Illinois libraries
(http://www.eliillinois.org/, ELI), currently in a MySQL database, onto a
MS-SQL server installation behind our Secretary of State's firewall (the ISL
is part of the Sec. of State's office). We are doing this so that Illinois
State Library staff can use their MS Access local installations to
manipulate the data, use pre-exisitng queries, etc. At the same time, I have
developed a variety of Perl programs that enable all of the other libraries
in Illinois who actually have data in ELI to change their own data and to
view various reports and sorts of the data. 

I find this an incomplete solution, because I would really like to be able
to share the collected data with Illinois libraries so that they can use it
in their own apps. However, because of the extremely understandable security
concerns of the Sec. of State's data center (they have drivers' license
data, etc.), access to this data is limited to non-ISL libraries through
what I can build through web interfaces.

A solution, of course, might be to open up the existing system to allow
libraries (ISL included) to connect to the MySQL database through ODBC;
however, this has serious and severe performance issues were I to enable
ODBC connectivity to 4,000+ entities in Illinois who contribute to ELI.

A better solution might be to store the table records as XML files, allowing
libraries to manipulate the data to their hearts content through XSLT or
other means. That still leaves me with the problem of ISL staff using
existing MS-Access label programs, queries, etc. I can, of course, export
and import XML records to and from Access
(http://office.microsoft.com/en-us/access/HA010345601033.aspx), but that
seems rather silly to do in a dynamic environment such as we face. Does
anyone have any ideas how I might:

A.) Allow ISL staff to use exisiting Access queries (not to mention staff
comfort level and software installation base) to manipulate data in the ELI
database.

B.) Allow the world access to the data through a web interface/Perl cgi
programs

C.) Allow contributing libraries the ability to manipulate the data using an
interface such as MS-Access

D.) Do this magically, without the performance issues of an ODBC
import/export or the clumsiness and data latency problems of an XML
import/export


Any ideas? I hope I am explaining this somewhat cogently.

Andy


Andrew Bullen
Illinois State Library
James R. Thompson Center
100 West Randolph Street
Suite 5-400
Chicago, Illinois  60601

E-Mail: abullen at ilsos.net
Telephone: 312-814-4386
FAX: 312-814-2954

_______________________________________________
XML4Lib mailing list
XML4Lib at webjunction.org
http://lists.webjunction.org/mailman/listinfo/xml4lib