?
Solved

How to "use" an access database from a Web Page?

Posted on 2002-07-26
24
Medium Priority
?
242 Views
Last Modified: 2006-11-17
I don't know if my problem is related to ASP, HTML or some else kind of "web language". I will try to explain my problem, and you experts i hope will teach me the right way.

I've a Access Database with 3 tables:
1) tPerson is a table containing usual personal data (name, address, etc), with a ID number as key.

2) tQuestions is a BIIIIGGGG table fulfilled of "Medical" and "Nutritional" questions: some are in "yes/no" format ("Do you smoke?", "Have you the Haltzeimer?"), some are in "options" format ("How many cigarettes you smoke per day? <2 / 3 to 8 / 8 to 12 / >12"), and some are in "text" format ("Describe <<somewhat>>").

3) tAnswers is a "clone" of the tQuestions table, containing a reference ID to the person in tPerson, and the answers that person give.

My customer now want to "switch" from the usual MSAccess forms (that requires MSAccess installed) to a "HTML" interface. He don't needs to put the DB on the WEB, he only wants to have not MSAcces installed on every PC of every Doctor whitch use that DB.

I'm totally "OUT" about this kind of stuff...i can only build simple HTML pages...and the HTML pages exported fby MSAccess are pretty unreadable....so this is my question:

How can i build a "Simply maintenace" web page that can browse the tAnswers table, depending on the Person selected, and a Web page that can write/edit new data?
0
Comment
Question by:parduz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +2
24 Comments
 

Author Comment

by:parduz
ID: 7181246
Oh, if this means something, the DB is not shared between the Doctors; instead, each Doctor own this proper DB...
0
 
LVL 1

Expert Comment

by:stino
ID: 7181278
MS Access 2000 does have an "Export to HTML" option that has rudimentary features that may allow you to publish your data to a web page.  But! - to do anything serious, you'll need a solid technology like ASP.  If you're a software developer and you are familiar with VB, then ASP offers the best and most versatile solution.  But in short, look at that export option (not sure how it is actually referred to).  If that doesn't work, start reading up on ASP - you really should get into this technology!
0
 
LVL 1

Expert Comment

by:stino
ID: 7181289
MS Access 2000 does have an "Export to HTML" option that has rudimentary features that may allow you to publish your data to a web page.  But! - to do anything serious, you'll need a solid technology like ASP.  If you're a software developer and you are familiar with VB, then ASP offers the best and most versatile solution.  But in short, look at that export option (not sure how it is actually referred to).  If that doesn't work, start reading up on ASP - you really should get into this technology!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:parduz
ID: 7181709
Yes, i know that Access exports to HTML...the problem with this is the page inside code: pretty nothing that i can read. Or, at least, i see no way to edit that page.

I'm a VB programmer. If you like to put here a little ASP - HTML example (an option button, a combobox and a textbox, each reading/writing a field in a table), i'm sure i will start to understand somewhat! :)
0
 
LVL 1

Accepted Solution

by:
tonejunkie earned 1200 total points
ID: 7182150
'This is a very simple example of how to read and display ColumnName1, ColumnName2 from the tAsnwers table.
'Make sure you set up a DSN to point to the .mdb file (or link directly to the access file.  And make sure you give IUSR_*** permission to read/write to the .mdb file.

<%
  'Start by opening a connection to the database
  set db = Server.CreateObject("ADODB.Connection")
  set rs = Server.CreateObject("ADODB.Recordset")
  db.Open "DB_DSN_NAME"
 
  'Define and execute your select/insert/update statement
  query = "Select * from tAnswers where ID=" & id
  rs.Open query, db
 
  'Check if there are any results
  if not rs.eof then
    rs.movefirst
    'Loop through the results, print them out
    do while not rs.eof
      response.write rs("ColumnName1") & ", " & rs("ColumnName2") & "<br>"
      rs.movenext
    loop
  else
    'If no results, print out an error message.
    response.write "No records found."
  end if
  rs.close
  db.close
  set rs = nothing
  set db = nothing
%>
-------------------------------------------------
'Now let's say you want to insert a record into the DB.
'You'd submit information from page1.asp to page2.asp like this:

'source code for page1.asp:
<html>
<body>
 <form name="answersForm" method=POST action="page2.asp">
  <input type=hidden name="ID" value="123">
  <input type=text name="ColumnName1">
  <input type=text name="ColumnName2">
  <input type=submit value="Submit">
 </form>
</body>
</html>


'source code for page2.asp:
<%
strID = request.form("ID")
strColumnName1 = Trim(request.form("ColumnName1"))
strColumnName2 = Trim(request.form("ColumnName2"))

set db = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
db.Open "DB_DSN_NAME"
 
query = "Insert into tAnswers(ID, ColumnName1, ColumnName2) values(" & strID & ", '" & ColumnName1 & "', '" & ColumnName2 & "')"
rs.Open query, db

set rs = nothing
set db = nothing
%>
<html>
<body>
 Record inserted.
</body>
</html>

'So what that will do is when the user submits the form on page1.asp, on page2.asp it'll pull out the ID, ColumnName1, and ColumnName2 variables.  Insert them into the statement to be executed.  And then insert into the DB.
----------------------------------------
For more information:
SQL tutorial - http://www.sqlcourse.com/
ASP reference guide - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/iisref/html/psdk/asp/iiwaref.asp
More ASP examples - http://www.webmasterbase.com/article/343
0
 
LVL 1

Expert Comment

by:tonejunkie
ID: 7182163
Oops...  that insert string should actually be:
query = "Insert into tAnswers(ID, ColumnName1, ColumnName2) values(" & strID & ", '" & strColumnName1 & "', '" & strColumnName2 & "')"
0
 

Expert Comment

by:seabubblefish
ID: 7182174
In my opinion, I think you are a VB programmer and just know a little bit about html. The main differences for VB form and Html form are data awareness. In VB form, we can put text box, combo box, check box or etc. And the data automatically appears in the controls. If we change the data in the controls, the data changed will be reflected to the Access database.
However for Html form, it is quite stupid compared with VB form. It is because if we want to display the data in the controls. We need to handle by ourselves. And also if we change the data in the controls, the data will not be reflected to the Access database. We need to submit the form to the server and in the server, we need to use SQL to write the data back to the Access database.
0
 
LVL 1

Expert Comment

by:tonejunkie
ID: 7182187
Which is what I tried to show in my examples.  :)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7182207
Parduz,

If you are out of your depth on this, perhaps you should consider farming this work out to a consultant. People like me and others on this site (just look at the profiles) do this sort of work on a consulting basis and can turn this around for you pretty quickly.

On the other hand, if you want to do this yourself, I recommend using ASP. To get started, take a look at www.asp101.com, http://www.w3schools.com/asp/, and www.4guysfromrolla.com.

Fritz the Blank
0
 
LVL 1

Expert Comment

by:stino
ID: 7182209
tonejunkie's example seems to be right on the money insofar as a working example to accomplish what you want to do.  Have we answered your question?  
0
 

Author Comment

by:parduz
ID: 7182700
Well, thanks to all. I start right now to test the tonejunkie's code.
in one hour or two i will give some notice...

Thanks!
0
 

Author Comment

by:parduz
ID: 7182711
Fritz, can you anyway mail me at parduz@libero.it , and better explain the terms of your consulting service?
Thanks!
0
 
LVL 1

Expert Comment

by:stino
ID: 7182870
Hey, if fritz gets the job can I at least get the points?  Or at least share it with tonejunkie ! :-)
0
 

Author Comment

by:parduz
ID: 7184040
I was just courious: if i decide to delegate that job, pts assignement will be unaffected.

About the code submitted:
it works ( apart the connection string that i've modified looking on a web sample).

What i can't figure out is:
1) How to browse the tAnswer table record by record?
2) How to show a field value in a text control or a radio button?
3) I've discovered how to make asp working in my PC: using the personal web server. This is a lot of things to install: what are the only needed files to use ASP and a Access DB? I really need all the thing PWS installs?

IMO, these questions are related to this question, but if you think that i must open new EE questions, tell me: i don't want to abuse of your kindness.
:)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7184132
You must install PWS if you are not running IIS on a server. Otherwise, any attempt to view your pages will result in a prompt asking you to download the files rather than view them in a browser.

When you say record by record, do mean as results in a table, or all of the information for one particulare ID # (member, patient, and etc.)?

Fritz the Blank

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7184208
In response to the email you sent me, here is some sample code that will update a record via a checkbox and a radial control:

<%On Error Resume Next%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%

bolTestCheck =Request.Form("Checkbox")
if bolTestCheck ="on" then
     bolTestCheck = "Yes"
else
     bolTestCheck = "No"
end if
strTestRadial = Request.Form("RadioControl")
intID = Request.Form("PatientID")
strDataPath = "c:\yourpath\yourAccessDB.mdb"


if not IsObject("ojbConnection") then
     strDataPath = server.MapPath("AccessDB.mdb")
     set objConnection=Server.CreateObject("ADODB.Connection")
     strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                    + " Data Source= " & strDataPath & ";"_
                    + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
     objConnection.ConnectionTimeout = 15
     objConnection.CommandTimeout =  10
     if objConnection.state = 0 then
          objConnection.Open strConnectString
     end if
end if

strSQL="SELECT * FROM tbl_PatientQuestions WHERE ID= " & intID

if not isObject("objRS") then
     set objRS=Server.CreateObject("ADODB.RecordSet")
end if

if objRS.state <> 0 then
     objRS.close
end if

objRS.Open strSQL,objConnection
     objRS("RadioControl") = strTestRadial
     objRS("Checkbox") = bolTestCheck
objRS.Update


'VBScript errors
If Err.Number > 0 Then
    Response.write("<p><hr>The following VB Errors Occured<p>")
     Response.write("Error Number: " & Err.number & "<br>")
     Response.write("Error Source: " & Err.source & "<br>")
     Response.write("Error Description: " & Err.description & "<br>")
     Response.End
End If


'Connection errors
dim iCounter
if objConnection.Errors.count > 0 then
    Response.write("<p><hr>The following Connection Errors Occured<p>")
    for iCounter = 0 To objConnection.Errors.Count - 1
          Response.write("Error Number: " & objConnection.errors(iCounter).number & "<br>")
          Response.write("Error Description: " & objConnection.errors(iCounter).description & "<br>")
          Response.write("SQL State: " & objConnection.errors(iCounter).SQLState & "<br>")
          Response.write("Native Error: " & objConnection.errors(iCounter).NativeError & "<br>")
    next
    Response.End
end if

if IsObject("objRS") then
     if objRS.state <> 0 then
          objRS.close
     end if
     set objRS = Nothing
end if

if IsObject("objConnection") then
     if objConnection.state <> 0 then
           objConnection.close
     end if
     set objConnection = Nothing
end if

%>

</HEAD>
<BODY>

Your Record has been successfully updated!
</BODY>
</HTML>
0
 

Expert Comment

by:seabubblefish
ID: 7187500
For installing web server,
if you are using Win98 or NT4, you can download Option Pack 4 and just follows the Setup steps. It is not difficult.
For Win2000, the web server components are in the Win2000 installion CD. If there is not installed in PC, you can run the setup program and add the web server components.
0
 

Author Comment

by:parduz
ID: 7197910
Well, i'm in trouble.
Tonejunkie is the first who write here some "working" code, Fritz, with his code, teach me how the code must be "formerly" written, and in general all ppl help me understand something. So who gain the points?
I'm assigning the pts to Tonejunkie 'cause it was the first to submit code, and assigning to each other my eternal gratitude :).

Anyway, i will post a new question after postin this comment, so if you already like to answer me...

Thanx to all.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7198120
Fair enough, but please don't email me at my business address asking for free code again.

Fritz the Blank
0
 
LVL 1

Expert Comment

by:tonejunkie
ID: 7198197
Thanks.  And good luck with the project.
0
 

Author Comment

by:parduz
ID: 7202030
Fritz, is your comment serious? If you think i've offended you tell me, i will try to repair, or at least to explain... anyway, i'm sorry.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7202043
No, no offense taken. It is just that you posted a different question in regards to radio buttons and etc. to my business address and took the time to answer it for you.

Usually when events like that happen and you have a hard time figuring out how to award points, you can either request a point split at http://www.experts-exchange.com/commspt/ or post a second question title Points for so and so with extra points for the person in question.

I firmly believe that tonejunkie deserves the points for the code that s/he gave you. I just feel that my going the extra distance for answering a request that came through my business address deserved recognition as well.

Fritz the Blank

0
 

Author Comment

by:parduz
ID: 7202055
heh, you're right.
Due to my pts shortage, tomorrow i will ask at a my collague to make a question for you.
Sorry again, i don't want to seem ungrateful...
...i'm only running to build that hell of DB app, so my mind is not really clear.
(but for sure, next time someone ask me a job like this, i will send to you and charge it 3 times!!!)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7202069
Oh, boy--do I know that feeling.

Fritz the Blank
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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