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

[Bullen, Andrew]

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