Entry

How can I connect to an Access database using ASP?

Jun 7th, 2003 16:37
Sven Glazenburg, unknown unknown, http://www.able-consulting.com/ADO_Conn.htm

A whole bunch of useful connection strings can be found here:
(http://www.able-consulting.com/ADO_Conn.htm . These also include a 
variety of ways to connect to an Access database (using DNS or DNS-
less).
Using ASP (you'll need IIS or PWS running), it's no problem.
Create a global.inc file containing something like this
******** Code Start ***********
<%@ Language="JScript"%>
<%
//DSN-less Connection string to the database
var sdbConnString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA 
SOURCE=c:\\inetpub\\wwwroot\\database\\MyDatabase.mdb";
//Response.Write(Session("data_conn"));
if (Session("data_conn") != null)
   cnn = Session("data_conn")
else
   {
    cnn = Server.CreateObject("ADODB.Connection");
    cnn.Mode = 3;
    cnn.Open(sdbConnString);
    Session("data_conn") = cnn
   }
   %>
******** Code End ***********
Put the line
<!--#include file="include/global.inc"-->
as the first line of all your asp files.
You can then run SQL commands, or create recordsets i.e.
******** Code Start ***********
RS = Server.CreateObject("ADODB.Recordset");
SQLstmt = "SELECT FieldName FROM Table";
RS.Open(SQLstmt, cnn, 3);
if (!RS.BOF && !RS.EOF) {
  while(!RS.EOF) 
    Response.Write(RS("FieldName") + "<br>");
    RS.MoveNext();
    }
  }
******** Code End ***********
In order to write to the database, use the execute method.
This example will add a new row to Table, setting values for
Field1 & Field2
******** Code Start ***********
SQLstmt = "INSERT INTO Table (Field1, Field2) VALUES (";
SQLstmt += yourVariable1 + ", ";  // Numerical value
SQLstmt += "'" + yourVariable2 + "');";  // String variable (in single 
quotes)
result = cnn.execute(SQLstmt);
******** Code End ***********