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

[Houghton,Andrew]

> From: xml4lib-bounces at webjunction.org 
> [mailto:xml4lib-bounces at webjunction.org] On Behalf Of Bullen, Andrew
> Sent: 04 April, 2007 11:36
> To: xml4lib
> Subject: [XML4Lib] XML database with an MS-Access front end
> 
> 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).
> ... [snip] ...
> Does anyone have any ideas how I might:
> 
> A.) Allow ISL staff to use existing 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

A) You said you are moving to a MS-SQL installation, which I
will assume is SQL 2005.  This should allow ISL staff to use
their existing Access queries and will not be an issue.  As
an alternative, ISL staff could use the freely downloadable
SQL 2005 Express Management Studio instead of Access.

B) Since you are using SQL 2005 (assumption) you have several
choices.  SQL 2005 allows SOAP based queries directly to stored
procedures that can emit XML back to the client without the
need to have a separate Web server.  You might take a look at
this feature, but you will have to see whether your hardware
will be up to the task, e.g., expected access and it's impact
on other database operations.  Note you could setup multiple
SQL installations, one for in-house access and the other for
the world, then setup a replication set to keep the data in
the world's SQL database up to date.  This will alleviate
the world from taking your in-house production database 
instance down, e.g., they overload their own instance and it
goes down, but when you bring it back up the replication set
will insure that their data is up to date.  The other choice 
is to use ASP.NET or WCF Web services as a front end to your 
SQL 2005 database.  This option may give you the best 
flexibility especially if you are already running a public
facing IIS Web server.

C) This is a sticky security issue, as your security folks
are probably aware of, especially with Access and ODBC
directly accessing the SQL database instance.  Depending 
upon what the contributing library need to do, you might 
be able to satisfy their needs by building an ASP.NET Web 
application, or a .NET Windows form or WPF application.  
This could be a little difficult if you are allowing them 
to create their own queries like you indicated in-house 
staff do.  An alternative here would be to allow them to 
use SQL 2005 Express Management Studio (in place of Access) 
and create an encrypted connection between them and the 
SQL 2005 database instance.

D) I think I covered this one in A), B), and C).


Hope this helps, Andy.