Switching from ODBC to OLE DB

ODBC is the comonly used way to power your database driver apps, OLE DB will improve not only speed but will also be more reliable.

What i will show you in this tutorial is two ways to switch between ODBC to OLE DB.

Also read this: OLE DB white paper

THE GLOBAL.ASA FILE:
If you use the GLOBAL.ASA file to declare your database connection string, then you only have to change one line in one file to implement the new connection across your entire site
This is what you should 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("/aspDirectory/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("/aspDirectory/myData.mdb") & ";"
End SUB
3. Ok, now your done, you might want to restart your IIS or other ASP server so it can reload the GLOBAL.ASA file

WHEN USING ULTRADEV 4:
When you use UltraDev 4 you can need to define a CUSTOM STRING:
This is an example of what should be used in the CONNECTION STRING:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\somepath\dbname.mdb") & ";User Id=admin;Password=pass;

Note that if you are using ASP JScript, you should change the & signs to + signs, like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.Mappath("\somepath\dbname.mdb") + ";User Id=admin;Password=pass;

Ok this should do it, if you would encounter any problems, please post them and ill help you fix them. I used both ways and worked fine for me.

If you don't know the drive path:

If you have a problem with the path, you can try out what i wrote at the bottom of the tutorial or you can use this string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\dbname.mdb;User Id=admin;Password=pass;

If you don't know the drive path to the database after uploading, you can download a server behavior from http://www.basic-ultradev.com/extensions/getdatabasepath.mxp and put it on a blank page and fill in your database name. Upload it to the server and browse the page. The connection string for DSNless connection will be in the browser window and you can cut and paste into UltraDev. (Extension created by Tom Muck)

Comments

Format of the initialization script does not conform to the OLE DB specification

October 5, 2001 by Piero Mattirolo

Hello, I have used DSNless connections before, but always within handcoded sections. When using UD however, I have always been forced either to make a local DSNless connection (i.e. needing modifications when uploading to the remote server) or to use ODBC.

All my attempts at using the Server.Mappath method within the UD connections window have been so far fruitless and frustrating. Can this be done at all? I am not quite sure about the use of quotation marks(") in this window, and I am pretty sure that this is the cause of my problems, but I think I have tried all combinations.

This is more or less the actual string I have been using, causing the above error

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\mdb-database\Tabellini.mdb")

This connection works:

Provider=Microsoft.Jet.OLEDB.4.0;Data source="C":\Inetpub\wwwroot\StudioTabellini\mdb-database\Tabellini.mdb

I suppose the solution is very simple but I don't seem to be able to find it :-)

RE: Format of the initialization script does not conform to the OLE DB specification

October 5, 2001 by Dave Joosten

Hello Piero,

Did you try using the extension i link in the tuturial to get the Mappath ? Now what i described works fine, and i have helped a lot of people with this so far. If you could give me some detailed step-by-step information on what your try to do i would be able to give you some specific information on where the problem is. And yes, as you quote the solution is very simple, but one typo in the string and it won't work, so let's get you connected soon!

Dave Joosten

RE: RE: Format of the initialization script does not conform to the OLE DB specification

October 6, 2001 by Piero Mattirolo

Thanks a lot, Dave, for your helpfulness, as well as for your good tutorials.

My problem is not really that I don't know the physical path to the database on the server, but rather that I don't know how to use the Mappath method within the UD interface. I have been using a mappath file that I keep at hand when I am using a new remote server. In fact, I have modelled my subfolder structure on some of my sites to be able to make changes easily and to test them locally. However I did this either by using the mappath method within a handcoded connection, which prevented me from using UD to full extent (eg. recordsets with a red ! exclamation mark, etc.), or using the physical path, which caused some errors when by mistake I would load the local connection file to the server (there will always be difference, eg the server unit being E:/ while the local is C:/) .

My question really remains:

does the connections window accept a string using the Mappath method in the string itself and what is the correct syntax?

Thanks once more for your kind help

RE: RE: RE: Format of the initialization script does not conform to the OLE DB specification

October 6, 2001 by Piero Mattirolo

Please disregard my previous message. The answer is:

Yes, it can be done, the proper syntax is though:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("..\somefoldername\somedatabasename.mdb")

(I was putting an extra quotation mark). As to the use of the backslashes and the dots, this depends of course on the folder location. However your tutorial should mention the quotation marks at the beginning of the string !

See all 7 Comments

You must me logged in to write a comment.