We help IT Professionals succeed at work.

Access database with Front Page

paulvb
paulvb asked
on
I have had it, I need someone to build me a shell to receive information and display information using Front Page 2000 and MS Access. Please respond and name your points to do this.

Thanks
Paulvb
Comment
Watch Question

Wow Paul!  EE frowns on this kind of thing.  That is, having an Expert do the work for you.  But I've helped lots of people with this type of thing on this forum.

What EXACTLY are you trying to do?  How far have you come already?  More importantly, what kind of server are you on because for this type of thing to work you need to be able to support ASP.

Author

Commented:
My web host is using NT and it does support .asp.
I have tried to set up a form and a database but it will not work.
If I had a form and a database already set-up I can take it apart and figure out how it works.

Thanks
Paulvb
Ok, Paul....what exactly do you want the form & access to do?  Only insert info to the DB or pull from it too?

Let's start with the form only then.  Can you give me a form with the fields etc you want?
>>I have tried to set up a form and a database but it will not work.


Show me what you've got so far, and tell me exactly what is not working.

Author

Commented:
I have a database of veterinarians made in access I have made a form results page (saved as .asp) I have set up a search form with only one field (to test it). I have verified the database (named database1). When I uplaod it to my server and test it, the results page list possible problems.

Author

Commented:
I want end users to pull info from the database, and another form to add info to it

Thanks
Paulvb

Author

Commented:
paul@e-scope.com
Ok, post the form page here and/or email me the database and the form results page and the search form.

Paul, I won't get to it until tomorrow I think....I was out of the office all day yesterday and will be gone most of today.

eeFPHelp@classylinks.ca
Hi Paul.


Got your email.  I'll try to get to it today.

Sorry for the delay.  It's pretty busy in ClassyLand.
Ok...here's the page that pulls the info from the db... (I'll email it to you too.)

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>LastName</title>
</head>

<body>

<table width="100%" border="1">
  <thead>
    <tr>
      <td><b>LastName</b></td>
      <td><b>FullName</b></td>
      <td><b>Address</b></td>
      <td><b>City</b></td>
      <td><b>State</b></td>
      <td><b>ZipCode</b></td>
      <td><b>County</b></td>
      <td><b>PhoneNumber</b></td>
    </tr>
  </thead>
  <tbody>
<%          'connect to your database
          set conn = server.createobject("adodb.connection")
          conn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("vettable.mdb")&";"
          conn.open
fp_sQry="SELECT * FROM Veterinarians"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=8 align=left width=""100%"">No records returned.</td></tr>"
          set fp_rs=conn.execute(fp_sQry)
Do while not fp_rs.eof%>
    <tr>
      <td><%=fp_rs("LastName")%>
      </td>
      <td><%=fp_rs("FullName")%>
      </td>
      <td><%=fp_rs("Address")%>
      </td>
      <td><%=fp_rs("City")%>
      </td>
      <td><%=fp_rs("State")%>
      </td>
      <td><%=fp_rs("ZipCode")%>
      </td>
      <td><%=fp_rs("County")%>
      </td>
      <td><%=fp_rs("PhoneNumber")%>
      </td>
    </tr>
    <%fp_rs.movenext
    loop%>
  </tbody>
</table>
<%fprs.close
conn.close%>
</body>

</html>
What you need to know about the above code is that the database is in the same folder as the page.  If you want it in a different folder, you specify that here:

conn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("vettable.mdb")&";"


Next the adding part of your question.  You need to make sure that whatever folder you have the database in has WRITE permissions on it.  Then you can do this:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>LastName</title>
</head>

<body>
<%
If request.querystring("action")="submit" Then

     'connect to your database
          set conn = server.createobject("adodb.connection")
          conn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("vettable.mdb")&";"
          conn.open
         
  addSQL = "INSERT INTO Veterinarians (LastName, FullName, Address, City, State, ZipCode, County, PhoneNumber) VALUES "
  addSQL = addSQL & "('"& request.form ("LastName")&"', '"& request.form ("FullName")&"', '"& request.form ("Address")&"', '"& request.form ("City")&"', '"& request.form ("State")&"', '"& request.form ("ZipCode")&"', '"& request.form ("County")&"', '"& request.form ("PhoneNumber")&"')"
     conn.execute addSQL
     response.write "Your data has been added"

conn.close    
End If
%>

<table width="100%" border="1">
  <thead>
    <tr>
      <td><b>LastName</b></td>
      <td><b>FullName</b></td>
      <td><b>Address</b></td>
      <td><b>City</b></td>
      <td><b>State</b></td>
      <td><b>ZipCode</b></td>
      <td><b>County</b></td>
      <td><b>PhoneNumber</b></td>
    </tr>
  </thead>
 <form method="POST" action="insert.asp?action=submit">  <tbody>
    <tr>
      <td>
        <p><input type="text" name="LastName" size="20"></p>    
      </td>
      <td><p><input type="text" name="FullName" size="20"></p>
      </td>
      <td><p><input type="text" name="Address" size="20"></p>
      </td>
      <td><p><input type="text" name="City" size="20"></p>
      </td>
      <td><p><input type="text" name="State" size="20"></p>
      </td>
      <td><p><input type="text" name="ZipCode" size="20"></p>
      </td>
      <td><p><input type="text" name="County" size="20"></p>
      </td>
      <td><p><input type="text" name="PhoneNumber" size="20"></p>
      </td>
    </tr>
  </tbody>
</table>
          <input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2">
        </form>

</body>

William FulksIT Services Analyst
CERTIFIED EXPERT

Commented:
Dude, just use the Wizards in FromtPage.  Build a form, then right click on the form submit button and go to FormProperties, then select that you want to send the contents of the form to a database, and let FrontPage create the database for you.

Then, make another page using Insert - Database Results and it'll work.

Now if your server does not support ASP, it may not work for the FrontPage database stuff either.  It doesn't matter if you are on a NT server or not - you need to make sure that FrontPage Extensions are installed.
William FulksIT Services Analyst
CERTIFIED EXPERT

Commented:
And this site here has some great tutorials on Frontpage and databases.

http://www.sitebuilder.ws/frontpage/tutorials/default.htm

Author

Commented:
ClassyLinks:

These pages can not be edited with FrontPage, they can only be edited in HTML.
As you know I do not have a clue. I went to The sitebuilder website as suggested above and I obtained some information to get me started on form pages being attached to a database.

Thanks
Paulvb

Author

Commented:
phungus69:

I gave ClassyLinks points because she gave me what I had for, but after watching the tutorials that you had suggested, I realized this is what I needed.
I will post another question with points for you.

Thanks
Paulvb
You can edit the pages in FP...I do it everyday.  But you do need to do it in HTML mode.

If you are going to start using database connections, my best advice is to start learning a little ASP.  Eventually, you're going to need to use it, and even the site phungus gave does start you on that road.

More ASP sites ~ great places to learn.

http://www.activeserverpages.com
http://www.asp101.com