Solved

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

Posted on 2002-07-26
24
230 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
  • 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
 

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 300 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now