Making the Switch From ODBC to OLE DB

ODBC is great and all but for speed and reliability you should switch to OLE DB to power your database driven apps. I will show you how to do it two ways: in the global.asa and in any ASP page.

• IN THE GLOBAL.ASA FILE:

  As you probably already know, I usually use the global.asa to declare my database connection string. Because of this, the change from ODBC to OLE DB was easy! - I only had to change one line in one file to implement the new connection method across the entire site. Here's what you do:

  1. Find the old connection string (in the Application_OnStart Sub):

     GLOBAL.ASA

     SUB Application_OnStart
        ' this is the old ODBC string
     	Application("dbConn") = _
     	"DBQ=" & Server.Mappath("/aspEmporium/myData.mdb") & ";" & _
     	"DRIVER={Microsoft Access Driver (*.mdb)};"
     End SUB
     

  2. Replace old ODBC connection string with new OLE DB string:

     GLOBAL.ASA

     SUB Application_OnStart
        ' this is the new OLE DB string
     	Application("dbConn") = _
     	"Provider=Microsoft.Jet.OLEDB.4.0; " & _
     	"Data Source=" & Server.Mappath("/aspEmporium/myData.mdb") & ";"
     End SUB
     

  3. That's it! Now would probably be a good time to restart IIS so that the global.asa will reflect the new values. And then test every app that hits the database for any reason.

• IN INDIVIDUAL ASP FILES:

  If you don't declare the connection string in the global.asa, you probably have sore fingers from typing it so many times in your pages. (Just kidding) What I meant to say is, here's how to do it in any ASP page:

  ANYPAGE.ASP

  <%
  Set Conn = Server.CreateObject(ADODB.Connection)
   ' old ODBC connection string:
   ' (commented out)
   ' Conn.Open "DBQ=" & Server.Mappath("/aspEmporium/myData.mdb") & ";" & _
   '           "DRIVER={Microsoft Access Driver (*.mdb)};"
  
   ' new OLE DB connection string:
  Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=" & Server.Mappath("/aspEmporium/myData.mdb") & ";"
  
   ' below here it's business as usual...
  %>
  

  You did it! Now test it and make sure it works. In most instances, you will not need to change any other part of the script's original code.

I had no problems with the switch to OLE DB for the ASP Emporium site except for with the shopping cart example. This was narrowed down to the SQL statement that displays the cart's content. It was eventually determined that the database field being queried was corrupted. The following field in the shoppingCart table was deleted: session
A new field was added called itemGrabber and the source was updated to reflect the new field value. After that, the example worked correctly.

Please report any unusual occurances, bugs or problems as you get them to the feedback area.

Other Errors that I've had making the switch:

The ASP Emporium web site uses 4 databases, one of which is public and contains all of the examples. I thought I could get away with just changing the application("dbConn") examples database string in the global.asa to reflect OLE DB while leaving all the other database strings in the old ODBC format (DBQ= , etc...).

This results in an error, however you won't know until someone accesses one of the pages that uses a different database conn string than the one declared in the global.asa. The system then discards the ole db conn string declared in the global.asa and uses the new conn string. After this, all pages using the default conn string (declared in the global.asa) will return errors.

My quick fix:

switch all your different database conn strings to the new OLE DB format and everything will work like it used too (at least it did for me!). I guess the lesson is: use a uniform connection method and don't mix and match ODBC and OLEDB conn strings. Just remember to test everything before you release it.