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:
   
   

       [ODBC]
       DRIVER=Microsoft Access Driver (*.mdb)
       UID=admin
       ReadOnly=0
       UserCommitSync=Yes
       Threads=3
       SafeTransactions=0
       PageTimeout=5
       MaxScanRows=8
       MaxBufferSize=512
       ImplicitCommitSync=Yes
       FIL=MS Access
       DriverId=25
       

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.