How to set up a file Data Source Name -- Sample Code

 Here are the sample code blocks and directions needed to set up a "File DSN" to make your web portable:

  1. Create your Microsoft Access database and import it into your web.
  2. Create a simple text file (using Notepad) named "somefile.dsn" -- for example, you might name it "msaccess.dsn" -- that will contain the ODBC setup parameters.  This file would also need to be imported into your web directory.  Here are the lines that would appear in such a file:

        DRIVER=Microsoft Access Driver (*.mdb)
        FIL=MS Access
  3. Create a simple text file (you can use the FrontPage source editor or Notepad) named "somefile.asp" -- for example, you might call it "dsn.asp" -- that will contain the VBScript commands needed to create a string variable that will contain the connection string. This file will be included at the beginning of every .ASP file you create that needs to manipulate the database file. The sample shown below creates a string variable named "sDSN" that you can use with the ".Open" function of an "ADODB.Connection" object.  Here are the lines that would appear in such an .asp file:
        ' set up variables
        Dim sDB, sPath, sDSNDir, sDSNFil, sDefDir, sDSN, sScriptDir
        ' The variable sDB contains the database path.
        ' It is physical with respect to your
        ' main project or web directory.  In
        ' other words, it assumes that you
        ' have the subdirectory "fpdb" beneath
        ' your web directory and that your Access
        ' database file (named mydatabase.mdb) was 
        ' imported into that directory.
        sDB = "fpdb\mydatabase.mdb" 
        ' Retrieve the script directory of this currently executing file
        sScriptDir = Request.ServerVariables("SCRIPT_NAME")
        sScriptDir = StrReverse(sScriptDir)
        sScriptDir = Mid(sScriptDir, InStr(1, sScriptDir, "/"))
        sScriptDir = StrReverse(sScriptDir)
        ' Set the virtual Directory
        sPath = Server.MapPath(sScriptDir) & "\"
        ' This is the DSN file Name for Access database ODBC general specs
        sDSNFil = "msaccess.dsn"
        ' Build the resulting DSN string SDSN which can then be used in the .Open method
        sDSN = "filedsn=" & sPath & sDSNFil & ";DefaultDir=" & sPath & ";DBQ=" & sPath & sDB & ";"
  4. At the beginning of every ASP file that will manipulate the database, you need to include the file created in step 3 (above) using the #include directive.  For example, if you had a file called "storedata.asp" that was going to open the database, insert some data, and close the database, the first few lines at the very top of the "storedata.asp" script file would read as follows:
        <%@ LANGUAGE="VBSCRIPT" %>
        <!-- #include file="DSN.asp" -->

    These lines, included at the beginning of the script file, would create the variable "sDSN" which could then be used as follows:
        set myConn=Server.CreateObject("ADODB.Connection")
        myConn.Open sDSN
    This approach makes your entire application portable from one web server to another.