Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Seeking suggestions on web script/software for MS Access database interface

I am a fairly new web designer, and I have a client that has a (MS Access) database of several hundred local businesses (with phone numbers, addresses, etc) that they want me to have accessible via their website. Previously, I undertook the painstaking process of manually creating separate HTML pages for each category of business (health/fitness, finance, automotive, etc) and manually entering each business on that page. They have decided to redo that part of the site, and now I am looking at doing that process again, unless I can find a way to install some kind of script or software on the server that can simply interface with their database and display the information inside, based upon searches that the web user does. I havent tackled this kind of project before, and my research into this topic thusfar has been fruitless. I am hoping some of you may have experience with this and can point me in the right direction. Thank you so much!
  • 6
  • 4
  • 4
1 Solution
You didn't specify which language you're planning on using, but if you want to use Perl, you'd want to read up on the DBI and DBD::ODBC modules which are used to access the database.

DBI - Database independent interface for Perl

DBD::ODBC - ODBC Driver for DBI

Short guide to DBI (The Perl Database Interface Module)
I wish we had an "edit" feature...I forgot a couple other items that you'd want to read.

CGI - Simple Common Gateway Interface Class

HTML::Template - Perl module to use HTML Templates from CGI scripts
rhequiemAuthor Commented:
Thanks for the suggestions, FishMonger (I keep hearing that line from "Hamlet" when I see your username hehe). I'm thinking I may not have been clear in what I'm hoping to find. I'm really looking for software (maybe calling it "script" is misleading here on EE) that I can install that will be a front end for the data contained in the DB. I'm almost positive that I've seen these kind of things before, which web designers use to make a searchable front end (possibly with  drop down boxes) for the different data in the DB. I was thinking it would be something I'd install on the web hosting server, and have interface with the MS Access database. From the initial batch of research that I did, I think the software/script type Im looking for *might* be called like "directory scripts" or something of that nature. Am I making any sense?
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

I understand what you're wanting to find, but I doubt that there is any ready made program that will do exactly what you want/need.  If you want a DB driven web site that is clean and professional, you'll need to use a scripting language such as Perl, or PHP, or ASP, or just about any language that you prefer and develop a custom web app.  If you build it using templates, you can work on the html side and partner up with someone that can write the scripts.  Going this route would be easier than the painful work you did building the static web pages.
rhequiemAuthor Commented:
Hmmm... That surprises me, actually. Just for more information, I dont want the whole site to be database driven, just their business database.

And if it helps, the link to the site I'm talking about (and the business directory thing I made) is here http://www.mainstreethanford.com/merchant_index.html . Maybe seeing it might spawn some other suggestions. I'd like to have a drop down menu where people can just select the business type they are looking for, and then have the server reference the db and spawn a page with the matching listings on it. In this day and age, I'm having trouble accepting that there isnt some kind of prepackaged software that could facilitate this hehe
Hi, rhequiem

We need 2 points to define:
1. Do you want the MS Access database to be directly updateable from the internet page or do you just want to publish the contents of the database in www (with search option of corse)
2. On which type of webserver this pages will have to be published (a rented webspace, a own server, is the server linux or Microsoft, etc ?)

regards, Franz

rhequiemAuthor Commented:
Bonjour, Bonjour hehe

1. I would like for the database to be directly updatable from the internet, as this would allow my client to do it, which is something I think they would like.

2. It's a rented webspace (hostgator.com), and is using Apache as the OS.

Looking forward to hearing any further suggestions :)
hi rhequiem,

maybe we need to straighten out further detail:

1. actually the customer has a access-mdb locally in his office
2. the contents of this db need to be in the net with nice features lik searxhing, sorting
3. will the customer keep the local access-db or does he want to work only in the web in the future

it is not possible to link a local access-db to a outsid-website front end.
it is not possible to have access-db on a linux/apache server
you can:
- automatically generate and upload web-pages from a local access-application
- have a mysql-db at the webhoster with a php-application
- have XML-data instead of a database at the webhoster (local access-application updates this XML-files)

there is a lot of open-source soluions for  mysql/php applications - usually they will not fit 100% to your needs, some PHP-progamming knowledge will be needed to do the necessary customizing.

my personal favorite would be the XML-data method, if the amount o fdata is not too big - the link you posted indicates approx 200 companies
the downside of th XML-method is, that it will only display in a modern browser (IE 6+ , Firefox 1.5+)

Regards, Franz
rhequiemAuthor Commented:
Thank you for the response, Franz. The database can reside on the web, and I dont even think the customer would mind re-entering the data into a MySQL type thing, if it gave them the ability to edit it on the web whenever they liked. I guess what Im looking for is hte most user-friendly solution possible, as I dont have much experience with web languages outside HTML and a *little* PHP (Im a network tech and do graphic design a little), so doing XML sounds daunting.

I'd be fine with the data displaying in only modern browsers, as that should include 95%+ of the people visiting the site, and I dont think that accessibility standards are something the client is that concerned with.

Do you have any specific suggestions for suitable PHP/XML scripts that could handle what it is Im looking to do with this? Thanks again!
For XML/PHP you will need to build up some basic skills.
A good Starter would be: http://www.sitepoint.com/print/management-system-php
If you play around with this, you will be able to adapt this concept for your project.

For my opinion the fastest solution for you would be, to generate the HTML-pages from Access
I did this for small projects in the past: Following this receipe:

1. take "manual" HTML-page  as e.g.  http://www.mainstreethanford.com/merchants/beautyspa.htm
replace the part with the adresses with a placholder e.g. '§adresses§' and save this file as a template for the access application. i do it as memo-field in a database table.

2. Write a function, which builds the adress html: e.g.
Private Function makeadr(myname,myadr1,myadr2,myphone)
   dim tmpstring
   tmpstring = '<p align="left" class="style10"><span class="style11">' & myname & </span><br>
   tmpstring =tmpstring  & myadr1 & '<br>'
   tmpstring =tmpstring  & myadr2 & '<br>'
   tmpstring =tmpstring  & myphone & '<br>'
End Function

3. Wrap this function in a loop
  dim myadrtable As String
  dim rs as DAO.reccordset
  ' Fieldnames of tabel are assumptions
  sett rs=CurrentDb.OpenRecordset('Select from tblMYADR where((................))')
  myadrtable='<td colspan="4" valign="top" bgcolor="#000000">'
  Do While Not rs.EOF
    myadrtable=myadrtable & makeadr(rs.Fields('CName'),rs.Fields('CAdr1'),rs.Fields('CAdr2'),rs.Fields('CPhone'))

4. Replace the palceholder('§adresses§') in the template with the content of 'myadrtable'  
  'load the template to a stringvariable
  myWrk = DLookup("tjunk", "TEMPLATES", "tid='" & myTemplate & "'")
  ' write the html
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set f = fs.CreateTextFile("myoutput.html", True)
  f.Write (Replace(myWrk, '§adresses§', myadrtable))

I hope this brings you on the track

Regards, Franz
rhequiemAuthor Commented:
Thanks again for the prompt response, Franz! I think, at this point, it would probably be quicker and easier for me to update the pages manually, rather than learning XML and PHP in order to finish this project up. I was hoping that, with the proliferation of web scripts and CMS systems out there today, there would be a module that I could use that would simply be able to be configured to interface with an existing database and display the content based on buildable searches.

A quick search I just did revealed this page to me, which is done in MS Front Page, of all things. http://www.frontpagehowto.com/searchwdrop.asp That's a very simplified version of what I was wanting to do. Here are a few other sites Ive found with something similar to what I'm wanting to do:


I think all I would really need is one drop down menu item for "business type" that would contain the 15 or so business types that my client has listed, and then that would list the businesses of that type.
>>> at this point, it would probably be quicker and easier for me to update the pages manually, rather than learning XML and PHP.....

That is exactly the point. In the last 6 months i looked at serveral CMS's. All of them need either time or programming skills. The simpler ones need some adaption. The bigger ones (Typo3, Joomla etc.) need quite  time to get familar with the basic concept and the main functions.

>>> I think all I would really need is one drop down menu item for "business type" that would contain the 15 or so business types that my client has listed, and then that would list the businesses of that type.

the example you posted are no simple techniques : the farmers site is a serveside php application and the receipes ara an asp-applicatioon(MicrosoftI). In both cases the html input is sent to the server and processed there.

what you want can be achieved without serverside Programming using new XML methods. Unfortunately no fiting example available.
to build it and test it will take some hours, i cannot afford at the moment as i am working on a customers project. If you invest 2 or 3 days in basic knowhow of XML-technology, you can do it on your own.
what you will need: Xpath-Syntax ( how to build xslt-Stylesheets for xml files)
how to integrate this in a htmlpage for IE and firefox
best resource seems to me: http://www.topxml.com/xsl/tutorials/intro/default.asp

Regards, Franz
The bottom line is if you want to be a web designer, you'll need to learn at least 1 of the server side languages.  Long gone are the days where a web designer only needs to know HTML and javascript.  If you're not willing to learn, then you should look into some other business.
rhequiemAuthor Commented:
Thank you again for the information, Franz. I was hoping for an easier solution, but that's not always how life works huh? hehe I appreciate the info, and will look into it when I have more time. I think for now, making the pages by hand will be the quickest solution.

It's not that Im not willing to learn - I LOVE learning, actually (and I don't think I implied anywhere in my posts that Im unwilling to learn - I think I pointed out time as the main factor in my decision here..). It's just that I dont have the TIME to learn XML or bring my PHP chops up to speed to complete what I was hoping there may be a pre-packaged solution for, when I have a lot of other stuff on my plate right now.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now