?
Solved

Website linked to Access Database

Posted on 2004-04-13
10
Medium Priority
?
187 Views
Last Modified: 2013-12-24
Hi all,

I am trying to create a website which is linked to a MS Access Database and uses that to display "live" data on an HTML page, allowing users to view and input data.

How would I do this???

Thanks for help...

seb
0
Comment
Question by:sjc2309
  • 3
  • 3
  • 2
8 Comments
 
LVL 12

Accepted Solution

by:
rcmb earned 1000 total points
ID: 10813157
You must be running this site on a webserver with front page server extensions installed for the below to work.

You need to import your database into FrontPage and create an ASP page to display the results.

Click on file, import and navigate to your database and import the file. FrontPage will prompt you to put the file in the fpdb directory.

Enter a name for your database connection (default is Database1) but you can name it anything you desire.

Open a new page and click on Insert, Database, Results - this will open the Database Connection Wizard

In step 1 select the connection you just created
In step 2 select the table you desire to display
In step 3 select Edit List to remove fields you do not desire to display
In step 4 select the format you desire to have the information display as (default is a table)
In step 5 select "display all records together" and clcik on finish

Save the page a something.asp

Click on preview in browser - You will see the results of the database display (if there is data in the database, if not "No records returned" will display)

To input data you need to create a form with text, drop-down, text-area, etc. boxes to gather the data. I recommend naming each form field the same as the database field you will be writing to (right click on the form field and edit the Name).

You have the form submit directly to the database by right-clicking inside the form (dotted lines) and select form properties
Select the option send to database, click on options to select the database connection you created earlier and select the table you desire to send the information to.

Click on the saved fields tab and match your form boxes to the fields you desire them to be written to.

I recommend clicking on the additional fields tab and removing anything in there.

Click ok once complete.

There are much more efficient ways to do this but if you are not familiar with writing ASP then this is probably the easiest way to get you to where you want to go. If you want more information let me know.

RCMB
0
 

Expert Comment

by:arekmalang
ID: 10912603
Hi,

I was trying to do that but I can only retrieve data from Access but unable to insert them.
Anything else I need to do?

Thanks,
Supri
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10912712
Please provide a little more specifics. I included a section in my previos comment on how to insert into a database. Did this not work? Did you need more guidance on that section?

RCMB
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Expert Comment

by:arekmalang
ID: 10913038
Yes, I followed your instruction. Below is the asp code for you to review. When I hit the submit button, I got this confirmation page:

Form Confirmation
Thank you for submitting the following information:

judul: test
komentar: test
Submit: Submit


Return to the form.


However, when I looked up the table in MS Access, I don't see this new record.
Any idea why?

Thanks for the help,

Supri

Note:
Database name is Opiniku.
Table name is Komentar. There are 2 fields in there: judul(type:text) and komentar(type:memo).


ASP Code:

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Session.LCID = 1033
Err.Clear

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
      Err.Clear

      Set fp_conn =  Server.CreateObject("ADODB.Connection")
      FP_DumpError strErrorUrl, "Cannot create connection"

      Set fp_rs = Server.CreateObject("ADODB.Recordset")
      FP_DumpError strErrorUrl, "Cannot create record set"

      fp_conn.Open Application("Opiniku_ConnectionString")
      FP_DumpError strErrorUrl, "Cannot open database"

      fp_rs.Open "Komentar", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
      FP_DumpError strErrorUrl, "Cannot open record set"

      fp_rs.AddNew
      FP_DumpError strErrorUrl, "Cannot add new record set to the database"
      Dim arFormFields0(2)
      Dim arFormDBFields0(2)
      Dim arFormValues0(2)

      arFormFields0(0) = "komentar"
      arFormDBFields0(0) = "Komentar"
      arFormValues0(0) = Request("komentar")
      arFormFields0(1) = "judul"
      arFormDBFields0(1) = "Judul"
      arFormValues0(1) = Request("judul")

      FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0


      fp_rs.Update
      FP_DumpError strErrorUrl, "Cannot update the database"

      fp_rs.Close
      fp_conn.Close

      FP_FormConfirmation "text/html; charset=windows-1252",_
                                    "Form Confirmation",_
                                    "Thank you for submitting the following information:",_
                                    "kirimKomentar.asp",_
                                    "Return to the form."

End If
End If

Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

%>
<html>

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

<body>

<form method="POST" action="--WEBBOT-SELF--">
  <!--webbot bot="SaveDatabase" suggestedext="asp" u-asp-include-url="_fpclass/fpdbform.inc" s-dataconnection="Opiniku" s-recordsource="Komentar" u-database-url="fpdb/opiniku.mdb" s-form-fields="komentar judul" s-form-dbfields="Komentar Judul" startspan --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include file="_fpclass/fpdbform.inc"--><!--webbot bot="SaveDatabase" endspan i-checksum="40548" --><p>
  Judul:</p>
  <p><input type="text" name="judul" size="20"></p>
  <p>Komentar:</p>
  <p><textarea rows="2" name="komentar" cols="20"></textarea></p>
  <p><input type="submit" value="Submit" name="Submit"><input type="reset" value="Reset" name="Reset"></p>
</form>

</body>

</html>
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10913170
I uploaded your code and created the database per your example. I made no modifications to your code and the form inserted data into the database with no errors.

Did you save the file as .asp vice .htm?

Make sure you link the fields to the form -- right click on the form and click on form properties

Click on Options, Saved Fields and make sure your form fields are linked to the database fields.

RCMB
0
 

Expert Comment

by:arekmalang
ID: 10915341
Yes, I did the Options thing and I checked, all the fields are linked to the database. I saved the file as .asp as well. It seems that when I hit the submit button, data is not transferred to the database at all. Is there anything that I need to tweak in Access? Can I set up a trace file to make sure that the data is actually inserted into the database? I don't think it's the connection problem since I am able to retrieve the data and view them on the page.


Thanks,
Supri
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11112084
I think you need to give write/update permissions to the database.

Use Windows Explorer to reach the database and right click on it and select properties. Under the securities tab, add the following users (IUSR_YourServername and IWAM_YourServername) and give permissions to read, write and execute.

Make sure you replace Yourservername with the name of your server.

:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11112090
Btw, the original poster of this question is sjc2309!

sjc2309, did any of this help you?
:) SD
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question