Home code Convert ODBC to OLE connection string

Convert ODBC to OLE connection string

1457
0

This need came about when a database of mine stopped working with a Godaddy hosting account (kept getting a 500 error).  The page is ASP and the database is Access 2000 with a dsn-less connection string.  After their support messed with it, the page came back up, but now it had this intermittent connection error:

Provider error ‘80004005’

Unspecified error
/addins/includes/dsn.asp, line 7

The weird thing is the page would load once and then if I refreshed it the page would have that error above.  I have no way of knowing what was done on the Hosting end of things, but researching the error lead me to believe that I now had a problem with Connection Pooling which has something do do with bad connections not being dropped when they should be.  I recall having this issue once before on another page and I don’t remember how I solved it (maybe renaming all files and variables?).
Anyway, the easy fix for this issue was to update my ODBC connection string with an OLE connection string, which many agree are better than ODBC anyway.  Here is my dsn.asp file which I include on each page where I need a database connection.  I commented out the old lines and bolded the new OLEdb line.

<%
  Dim Conn

  Set Conn=Server.CreateObject(“ADODB.Connection”)
  Conn.open “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & Server.MapPath(“\”) & “\access_db\database.mdb;”

  ‘connectionString=”DBQ=” & Server.MapPath(“\”) & “\access_db\database.mdb”
  ‘Conn.open “DRIVER={Microsoft Access Driver (*.mdb)}; ” & connectionString
%>

Your connection string may have an actual path instead of the Server.MapPath function, but believe me it’s better to have that MapPath function when your site is cohosted on a server you don’t control.  That way if they move your site to another server you don’t have to worry about updating the path to the database.

For more on Connection Pooling with ASP click here

LEAVE A REPLY

Please enter your comment!
Please enter your name here