Image of Navigational Map linked to Home / Contents / Search DSN-less Connections

by Rupert Walsh - GUI Computing
Image of Line Break

With Active Server websites you can easily connect to ODBC databases. Traditionally this involves creating a DSN (Data Source Name) on the server machine for your database.

Unfortunately, you can't do this remotely with the extensions available to Frontpage or Visual Interdev in the way that you can set user permissions. You may need to rely on your service provider or network administrator to set these up for you. It also means recreating the DSN when you move your website to a new server (say from development to production).

If you're using a file-type database such as MS Access, another possibility is to set up a "DSN-less" connection. It is possible to get all the information neccesary to define the ODBC connection string when a user session starts. To do this we're taking advantage of the Server.MapPath function and the DBQ and DefaultDir parameters in the connection string. You may recognise these parameters if you've ever examined the contents of file-type DSNs available in recent releases of ODBC. The DBQ (no, I don't know what the Q stands for) is the physical path to the database file, and the DefaultDir is, well, the default directory, and is where Access will put its locking file (.ldb).

Naturally you can load this string into a session variable during the Session_OnStart event, just as you would if it referenced a normal DSN.

I also find it useful to put my database in a subdirectory of the web, but it could reside anywhere. If it's not in a subdirectory, you'll need to hardcode the physical path, somewhat decreasing the portability of your site.

Here's an example using an Access database (MyDB.mdb):

Sub Session_OnStart
   dim sDBPath, sBasePath, nPos

   'Get the virtual path to this web
   sBasePath = Request.ServerVariables("PATH_INFO")
   
   'Remove the file name part
   nPos = instr(2, sBasePath, "/")
   sBasePath = Left(sBasePath, nPos)
   
   'Convert to a physical path
   sBasePath = Server.MapPath(sBasePath)
   
   'Append the subdirectory name
   sDBPath = sBasePath & "Database"

   'Define the Data Source Connection String
   Session("ConnString") = "DBQ=" & sDBPath & "\MyDB.mdb;" & _
      "DefaultDir=" & sDBPath & ";" & _
      "Driver={Microsoft Access Driver (*.mdb)};" & _
      "DriverId=25;" & _
      "FIL=MS Access;" & _
      "MaxBufferSize=512;" & _
      "PageTimeout=5;" & _
      "UID=;"
End Sub

Visual Interdev's built-in database viewer won't support this DSN-less connection, however you can also create a DSN on your development machine and include code for both connection types in global.asa if you want to use it. Personally, I find the interface somewhat clunky anyway, and prefer to open the database using Access. Of course, if you do this you'll need to be careful to use only the DSN-less connection string throughout your project, or you'll lose the portability advantage and (sevice) provider independence.



Written by: Rupert Walsh
April '98

Image of Arrow linked to Previous Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]