[WEB4LIB] MS Access data page

Sat Apr 16 02:05:06 EDT 2005

Liz,

I'm probably going about this all wrong, but this is the only option I have
right now.  We have an MS Access database which we'd like to be able to query
(but not update) via the Web.  I've been able to build a data access page
in Access, but it insists on keeping the path what I originally gave it (a
mapped drive), and will not accept a URL.  However, most people in the college
do not have this drive mapped, nor do they have access to the server.  However,
the file resides on a publicly accessible Web server.  I feel like I'm so
close to making it work, but am not quite there.  I do not know ASP so that's
not an option right now.  Does anyone have a suggestion?

I am sorry to tell you, but the only non-ASP way *I* know how to do this 
is through either an ODBC connection (or JDBC, if you're a Java-ite) or 
something like Perl's DBI system. So, in Perl (ActiveState, on a Win2k 
server, using ODBC):

1.) You initalize/open the connection--

use Win32::ODBC;

my($DSN) = "digitize";
my($db1) = new Win32::ODBC($DSN) || die qq(Cannot open ODBC connection 
to "$DSN"), Win32::ODBC::Error, "\n";

2.) You query the database through a SQL query, passed through ODBC--

$query = "SELECT * FROM Digitize WHERE LastName=\'$lastnamequery\' ORDER 
BY LastName";
$db1->Sql($query);

3.) Read the results into variables--

while ($db1->FetchRow()) {
    my(%data1) = $db1->DataHash;
    $itemnumber = $data1{'ItemNumber'};
    $typeoffile = $data1{'TypeOfFile'};
    $thumbfilename =  $data1{'FileName'};
    $creator1 = $data1{'Creator1'};
    $creator2 = $data1{'Creator2'};
    $creator3 = $data1{'Creator3'};

etc. etc.

4.) Display the newly-filled-in variables into a templated HTML form:

print "Content-type: text/html\n\n";
print <<EOF;
<head>
<link rel="stylesheet" href="$cssvar" media="screen">
<title>IDA Records beginning with the letter $letter</title>
</head>
<BODY>
<h1 align="center"><FONT FACE="ARIAL">All IDA Records Beginning with the
Letter <i>$letter</i></font></h1>
<table border="1" width="100%">

...

<table border="1" width="100%">
  <tr>
    <td width="33%"><p align="center"><font size="1">$imagesourceurl 
</font></td>
    <td width="33%"><p 
align="center"><em><strong>$locationstr</strong></em></td>
    <td width="34%"><p align="center"><strong><a
    href="/cgi-bin/ida/briefidarecord.pl?record=$itemnumber">Detailed 
Record</a><br>
    Record No. $itemnumber</strong></td>
  </tr>
</table>

<table border="1" width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td width="25%" valign="top" align="right"><font 
FACE="ARIAL"><strong>Title:</strong></font></td>
    <td width="75%" valign="top" align="left"><strong><font 
FACE="ARIAL">&nbsp;$itemtitle</font></strong>
    </td>
  </tr>
  <tr>

etc. etc.

Poof! Insta-page.

Please feel free to contact me off-list if you want to try a Perl 
solution and want more shiny happy code.

Andy Bullen
Digital Plumber, Illinois State Library