Specify what database you use (e.g : Ms Access).
Make a file DSN from ODBC32 in Control Panel to get the connection string
(this step is not necessary if you know the connection string to use for your
database).
Double Click on ODBC32 icon.
On ODBC Data Source Administrator dialog, choose File DSN and click Add
From Create New Data Source dialog, choose which type of database you use
and click Next
Type the name of your new File DSN e.g. "test" (click browse to specify
another folder where your File DSN file reside). Click Next and then Finish.
For Ms Access and Ms Excell type, you must tell the dialog where your
database is. It will ask you for a database name. In this cases, select any
database name on your computer.
Open the DSN file using any text editor. Usually it resides on "C:\Program
Files\Common Files\ODBC\" folder.
Use that string on your ODBC connection in global.asa with changes on the
"DefaultDir" (or the "DBQ Value" on Ms Access file) string value.
The changes on DefaultDir (or DBQValue) will be generated on the fly and
then stored on a Session variable.
' Automatic DataConnection
ScriptName = Request.ServerVariables("SCRIPT_NAME")
tmpScriptName = Mid(ScriptName,2)
intPos = Instr(tmpScriptName,"/")
VirtualDir = Left(tmpScriptName, intPos - 1)
AbsolutePath = Server.MapPath ("/" & VirtualDir)
DBQValue = "DBQ=" & AbsolutePath & "\YourDatabaseName.mdb"
' the string below is a duplicate string from the DSN file.
Session("DataConn_ConnectionString") = _
"DRIVER=Microsoft Access Driver (*.mdb);" & _
"UID=admin;" & _
"UserCommitSync=Yes;" & _
"Threads=3;" & _
"SafeTransactions=0;" & _
"PageTimeout=5;" & _
"MaxScanRows=8;" & _
"MaxBufferSize=512;" & _
"ImplicitCommitSync=Yes;" & _
"FIL=MS Access;" & _
"DriverId=25;" & _
"DefaultDir=;" & _
' you can change this DefaultDir
' DBQValue is generated on the fly
DBQValue
' End of Automatic DataConnection
Your database must be stored within the virtual directory If you specify
another directory within your virtual directory to store your database (e.g.
/VirtualDir/DataBase/MyData.mdb), you must change DBQValue variable with :
DBQValue = "DBQ=" & AbsolutePath & "\DataBase\YourDatabaseName.mdb"
To Use the connectionn string that we produce, from any asp file, simply use
this code :
<%
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open Session("DataConn_ConnectionString")
%>
Note :
Your virtual directory must reside on a directory without spaces. I have
trouble to connect my database when I put my virtual directory on such
directory.
You can also use a shorter connection string without add another string
value such as UID, PWD etc. depend on the ODBC Driver you use. Here's the
long connection string that I used:
' Automatic DataConnection
ScriptName = Request.ServerVariables("SCRIPT_NAME")
tmpScriptName = Mid(ScriptName,2)
intPos = Instr(tmpScriptName,"/")
VirtualDir = Left(tmpScriptName, intPos - 1)
AbsolutePath = Server.MapPath ("/" & VirtualDir)
DBQValue = "DBQ=" & AbsolutePath & "\YourDatabaseName.mdb"
' the string below is a duplicate string from the DSN file.
' This value is generated on the fly
Session("DataConn_ConnectionString") = _
"DRIVER=Microsoft Access Driver (*.mdb);" & _DBQValue
' End of Automatic DataConnection
