Home
Storing data in a ms access data base and showing and searching from it is often used in web programming. There are many components to access the database and multiple ways to the same thing. Here we are focusing on Ms Ado component to test some regular activities.

Ms access, ado data retrieval and addition.

In this example we are connecting to the ms access database named db1 and using sql we are quering the table named mytable. Then we are displaying all the rows from the tabale.

First a Adodb connection object is created and its connecting to the database. The a recordset is populated with the Sql query to get all the rows from the table named mytable.

Then a while loop is looping through all the (until eof/End) rows of that table and writing that to the browser.

There are three sql statements written here, you can comment out the first one (with ') and try with the 2nd and third mySQL statements.

<%
Set oRs = Server.CreateObject("ADODB.Recordset")
Set db = Server.CreateObject("ADODB.connection")

ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;DBQ=" & server.MapPath(".") & "\db1.mdb"

db.Open ConStr

mySQL = "Select * from mytable;"
' mySQL = "Select * from mytable where name='Jhon';"
' mySQL = "Select * from mytable where address like '%South%';"

oRs.Open mySQL , db, 1,2

while not ors.eof
response.write ors("id") & "<br>"
response.write ors("name") & "<br>"
response.write ors("email") & "<br>"
response.write ors("address") & "<br>"
response.write ors("tel") & "<br>"
response.write "<hr>"
ors.movenext
wend

ors.close
db.close
Set db = nothing
Set oRs = Nothing
%>

In this example, we are adding a new row of data to the table and then closing the record set. Then we are reopening the table and showing all the records, including the newly added row of data.

<%
'Adding data to a table.

Set oRs = Server.CreateObject("ADODB.Recordset")
Set db = Server.CreateObject("ADODB.connection")

ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;DBQ=" & server.MapPath(".") & "\db1.mdb"

db.Open ConStr

mySQL = "Select * from mytable;"
oRs.Open mySQL , db, 1,2

ors.addnew
ors("name") ="Ben"
ors("email") ="ben@mail.com"
ors("address") ="32 park street west."
ors("tel") ="8987234234"
ors.update
ors.close

oRs.Open mySQL , db, 1,2

while not ors.eof
response.write ors("id") & "<br>"
response.write ors("name") & "<br>"
response.write ors("email") & "<br>"
response.write ors("address") & "<br>"
response.write ors("tel") & "<br>"
response.write "<hr>"
ors.movenext
wend

ors.close
db.close
Set db = nothing
Set oRs = Nothing
%>

Here we are finding all rows of data where any part of the name is like 'ben', then we are deleting the first row from there. Alternately you can delete any particular row number by searching the id field.

<%
'deleting data from table.

Set oRs = Server.CreateObject("ADODB.Recordset")
Set db = Server.CreateObject("ADODB.connection")

ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;DBQ=" & server.MapPath(".") & "\db1.mdb"

db.Open ConStr

mySQL = "Select * from mytable where name like '%ben%' ;"

oRs.Open mySQL , db, 1,2

response.write "Deleting data id:" & ors("id") & "<hr>"
oRs.delete
oRs.update

oRs.moveFirst

while not ors.eof
response.write ors("id") & "<br>"
response.write ors("name") & "<br>"
response.write ors("email") & "<br>"
response.write ors("address") & "<br>"
response.write ors("tel") & "<br>"
response.write "<hr>"
ors.movenext
wend

ors.close
db.close
Set db = nothing
Set oRs = Nothing
%>

 

[Download Sample Code]