INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ASP 101

How to successfully connect to an Access database by DougP
Posted: 8 Aug 01

3 things first

1st. This works on a NT server. If your site is hosted on an Appache server it won't work.
2nd. Using Front Page make sure that the "Insert" menu "Database" option is NOT grayed out. If it is it won't work either.
3rd you need a global.asa file which has the settings in it.
I created a dummy form and let Front Page create this for me.  Then I opened it and examined the contents and came up with the minimal VBA code needed to make it work as shown below.

In the global.asa file
---------------------------
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
    '==FrontPage Generated - startspan==
    Dim FrontPage_UrlVars(2)
    '--Project Data Connection
        Application("hospice_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/hospice.mdb"
        FrontPage_UrlVars(0) = "hospice_ConnectionString"
        Application("hospice_ConnectionTimeout") = 15
        Application("hospice_CommandTimeout") = 30
        Application("hospice_CursorLocation") = 3
        Application("hospice_RuntimeUserName") = ""
        Application("hospice_RuntimePassword") = ""
    '--Project Data Connection
        Application("tellusaboutyou_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/tellusaboutyou.mdb"
        FrontPage_UrlVars(1) = "tellusaboutyou_ConnectionString"
        Application("tellusaboutyou_ConnectionTimeout") = 15
        Application("tellusaboutyou_CommandTimeout") = 30
        Application("tellusaboutyou_CursorLocation") = 3
        Application("tellusaboutyou_RuntimeUserName") = ""
        Application("tellusaboutyou_RuntimePassword") = ""
    '--
    Application("FrontPage_UrlVars") = FrontPage_UrlVars
    '==FrontPage Generated - endspan==
End Sub
Sub Session_OnStart
    FrontPage_StartSession '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
    On Error Resume Next
    if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub
    ' discover the VRoot for the current page;
    ' walk back up VPath until we find global.asa
    Vroot = Request.ServerVariables("PATH_INFO")
    strG1 = "global.asa"
    strG2 = "Global.asa"
    iCount = 0
    do while Len(Vroot) > 1
        idx = InStrRev(Vroot, "/")
        if idx > 0 then
            Vroot = Left(Vroot,idx)
        else
            ' error; assume root web
            Vroot = "/"
        end if
        if FrontPage_FileExists(Server.MapPath(Vroot & strG1)) then exit do
        if FrontPage_FileExists(Server.MapPath(Vroot & strG2)) then exit do
        if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
        iCount = iCount + 1
        if iCount > 100 then
            ' error; assume root web
            Vroot = "/"
            exit do
        end if
    loop
    ' map all URL= attributes in _ConnectionString variables
    Application.Lock
    if Len(Application("FrontPage_VRoot")) = 0 then
        Application("FrontPage_VRoot") = Vroot
        UrlVarArray = Application("FrontPage_UrlVars")
        for i = 0 to UBound(UrlVarArray)
            if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
        next
    end if
    Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
    ' convert URL attribute in conn string to absolute file location
    strVal = Application(AppVarName)
    strKey = "URL="
    idxStart = InStr(strVal, strKey)
    If idxStart = 0 Then Exit Sub
    strBefore = Left(strVal, idxStart - 1)
    idxStart = idxStart + Len(strKey)
    idxEnd = InStr(idxStart, strVal, ";")
    If idxEnd = 0 Then
        strAfter = ""
        strURL = Mid(strVal, idxStart)
    Else
        strAfter = ";" & Mid(strVal, idxEnd + 1)
        strURL = Mid(strVal, idxStart, idxEnd - idxStart)
    End If
    strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") & strURL) & strAfter
    Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
    On Error Resume Next
    FrontPage_FileExists = False
    set fs = CreateObject("Scripting.FileSystemObject")
    Err.Clear
    set istream = fs.OpenTextFile(fspath)
    if Err.Number = 0 then
        FrontPage_FileExists = True
        istream.Close
    end if
    set istream = Nothing
    set fs = Nothing
End Function
</SCRIPT>

------------------------
this goes on your .ASP page
-----------------------------------
<html>
<%@ Language=VBScript %>
<head>
<title>New Page 1</title>
</head>

<body>

<%
    Set fp_conn =  Server.CreateObject("ADODB.Connection")
    Set fp_rs = Server.CreateObject("ADODB.Recordset")
    fp_conn.Open Application("tellusaboutyou_ConnectionString")
    fp_rs.Open "test", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
    fp_rs.AddNew
    fp_rs("name") = "big Frankie"
    fp_rs.update
    
    fp_rs.Close
    fp_conn.Close

%>

----------------------------

The information in the global.asa is to make a connection to a database (.MDB) on your WEB site.
You can open an many tables as you want using the same connection string.
Notice in my global.asa I have two databases
hospice.mdb
and
tellusaboutyou.mdb

The line below tells which table to open in your .ASP file

the table name is "TEST" in my example
fp_rs.Open "test", .....

-------------------------
this line tells which database to open

fp_conn.Open Application("tellusaboutyou_ConnectionString")

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close