Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to successfully connect to an Access database

ASP 101

How to successfully connect to an Access database

by  DougP  Posted    (Edited  )
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")
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top