how to connect ms access with asp vbscript

katarey 27 Junior Poster · 2007-05-09T12:20:54+00:00

Hi there,

Well I am not a good tutorial writer but I am trying to explain how to make the connection with database (Ms Access) using asp,

Assumptions:
Running IIS 6
First here the directory plans:

Folders:
Websiteroot
Includes
Database

Websiteroot: - this folder will contain the full website
Includes: - this folder will contain the file where we will write the connection script
Database: - *here our database will be,

Files:
Websiteroot/default.asp
Websiteroot/includes/connectionFile.asp
Websiteroot/Database/dbFile.mdb

default.asp: Home Page of our website (here we’ll only show some records from database).
connectionFile.asp: here we’ll put connection script.
dbFile.mdb: Database File (Ms Access)

connectionFile.asp:

<%
'Declare Variables
Dim ConnString, conn, DatabaseLocation

'Assigning the database location into a variable, 
'Using ASP MapPath Method, The MapPath method maps a specified path to a physical path.
DatabaseLocation = Server.MapPath("/Database/dbFile.mdb")

'Creating Sub, Where we'll need to open the DataBase we'll use this sub
'Creating this Sub is Not Necessary, You can use directly code this inside of this Function/Sub 
Sub OpenDataBase()
'ADO Connection Object
'The ADO Connection Object is used to create an open connection to a data source.
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open DatabaseLocation
End Sub
'Creating Sub, To Close Database
'Creating this Sub is Not Necessary, You can use directly code this inside of this Function/Sub 
Sub CloseDataBase()
	conn.close 
End Sub
%>

default.asp:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Websiteroot/includes/connectionFile.asp " -->
<%
	'Open Database 
		OpenDataBase()
	'Create Recordset
	'ADO Recordset Object
	'The ADO Recordset object is used to hold a set of records from a database table.
	'A Recordset object consist of records and columns (fields).
		set rs = Server.CreateObject("ADODB.Recordset")
		sql = "Select * from users"
		rs.Open sql, conn
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>DataBase Connection Tutorial</title>
</head>
<body>
<!-- Form Not Submited Yet-->
  <table width="50%" border="1" cellspacing="0" cellpadding="0">
    <tr>
      <td>User Id</td>
      <td>User Name</td>
    </tr>
    <%	
		c=0 
			do while not rs.eof
		c=c+1 
	%>
    <tr>
      <td><%=rs("uid")%></td>
      <td><%=rs("username")%></td>
    </tr>
    <%
		rs.movenext
		loop
	%>
  </table>
  <%
  	Set rs = nothing
	CloseDataBase()
  %>
</body>
</html>

I hope this will Helpful For you,

this thread also useful for you

http://www.daniweb.com/techtalkforums/thread69174.html

Best regards,
Rahul Dev

I have Attached all Files used,

manleys_web 0 Newbie Poster · 2007-05-09T14:46:16+00:00

try the asp and ado pages on www.w3schools.com too ;)

CEK 0 Newbie Poster · 2010-08-24T17:34:10+00:00

simple example of database and asp connection