Error: Data source name not found and no default driver specified

Have anyone experienced this error message or know why it appears?

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/fps/formpubsForm.asp, line 455

I created an ASP to access a database on my local web server using Visual InterDev.  My global.asa file is able to connect to the database but everytime I try to view the records from IE I get the error message above.  The line number that the error message is referring to is the "formpubsFileDSN.Open " command line shown below:

Set formpubsFileDSN = Server.CreateObject("ADODB.Connection")
formpubsFileDSN.ConnectionTimeout = Session("formpubsFileDSN_ConnectionTimeout")
formpubsFileDSN.CommandTimeout = Session("formpubsFileDSN_CommandTimeout")
formpubsFileDSN.Open Session("formpubsFileDSN_ConnectionString"), Session("formpubsFileDSN_RuntimeUserName"), Session("formpubsFileDSN_RuntimePassword")
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "SELECT `EDC_Code`, `EDC_Name`, `EDC_Modified`, `EDC_Comment` FROM `EDC_Forms`"
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = formpubsFileDSN
rs.Open cmdTemp, , 0, 1

I have installed and reinstalled Visual Studio, Front Page Extension, Active Server Page and IIS 4.0 but there was no luck.  Any assistant is most appreciated.

Who is Participating?
2718Connect With a Mentor Commented:
Hi Rick.

Hmmm ...
either you don't understand what relative path names are OR I don't understand what it is you are trying to accomplish.

Relative pathnames (really we should be talking about relative URLs versus absolute URLs in your case because you talk about remote servers) - anyways, relative URLs only work when it is understood by the parties involved (in this case the IIS / ASP engine) what the base URL is.

"....However, I'd like to use relate pathname to the database file on  the remote server and have it look something like the following:   "DBQ=..\data\database\formpubs.mdb;DefaultDir=..\data\database;Driver={Microsoft Access Driver (*.mdb)}" ...."

This is not going to work because the remote server does not know  what the missing info is it could use to assemble the complete, absolute URL.

Relative URLs work with web pages and such related stuff because SOMEHOW YOU GOT to a given page and from there the web server is presented with a relative URL. It uses that web document's currrent position as a base URL to assemble the corresponding absolute URL. These conditions do not hold in your current situation with ASP so that's why it is not working.

"I figure that by using ralative pathnames, the database is more portable and I don't have to know the direct pathnames to the database directories on the remote servers."

I can see what you want to do. (I think, fingers crossed.  ** grin **)
A good way to do it would be to ask your web server administrator to map the directory containing your mdb to the web server's virtual directory mapping schema.
Or ask him/her if there are any directories already mapped that you could use in your URL description to avoid having to type the  whole thing in.

But if your database moves to a different server there's no way one generic URL will work for you. If your *.mdb goes to a new server you are going to have to update your ASP pages with the new, complete URL with the new location.

It comes down to relative pathnames for web documents not working / or behaving exactly like relative URLs for databases accessed by ASP pages. The specific problem being that the ASP engine / IIS has no idea what the base URL should be. And a partial solution being to map a directory along the path to your *.mdb to the IIS virtual directory structure (an option available only with the administrator's help.)

I hope this made some sense. And I hope it was relevant to your question.

rick101396Author Commented:
Edited text of question
I am not sure about your particular set-up for your ASP pages so I hope my comments are relevant.

"Data source name not found and no default driver specified"

this sounds like you are trying to open a connection to the database but IIS does not know / cannot find the data source name to use to open the database with.

This line of code here
formpubsFileDSN.Open Session("formpubsFileDSN_ConnectionString")
means use the given Connection Object (in your case formpubsFileDSN) and open a connection to the database indicated by the data source name which follows.
You are using a Session variable. Why you want to do that I don't know. A different Session object is kept for every user that visits your ASP pages - so assuming it's always the same database you want to access you are going to need to set that session variable to be equal to the name of the database as you set it up using the ODBC Data Source Administrator.

(I hope that I'm not telling you stuff you already know but
 this is the first thing I thought of after reading your problem  

I suggest you modify the aforementioned line of code by removing the reference to the Session Object variable (it is just one more layer of complexity to muck stuff up.) After you get the ASP page working to your satisfaction you can come back and re-insert the reference to the Session Object (assuming that's how you want to do it.)

Anyways, I suggest you do something like this

    formpubsFileDSN.Open "fluffy"

(this will work assuming you named your database 'fluffy'
 in the ODBC Administrator.)

If you have already taken care to specify the name of your database by setting a value for ("formpubsFileDSN_ConnectionString")
then Oops, sorry. I guess I did not understand your question.

By the way, all my comments reference the IIS ASP ADO
Connection Object Methods and Properties,
connection.Open ConnectionString, User, Password
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Visual Interdev produces almost unreadable "wizard code".

If you post your global.asa, i can try and make better readble (and debuggable) code.

Anyway, it seems that or that your global.asa is not ok, or (most likely) you don't have the ODBC connection created. At least that is what the error message says.

What is the value of
- Session("formpubsFileDSN_ConnectionString")
- Session("formpubsFileDSN_RuntimeUserName")
- Session("formpubsFileDSN_RuntimePassword")

You can display them by

Response.write Session("formpubsFileDSN_ConnectionString") & "<br>"

rick101396Author Commented:

Thanks for your help.  I was able to resolve the problem by using Visual InterDev 1.0's "Data Form Wizard" to create an connection as an example.  You are right, the session variables are not needed.   However, I have one quick question to ask about the setting the Connection_String by using relative pathnames rather than direct pathnames.

Since I created the connection on my local server, I could set the ConnectionString as:

"DBQ=C:\InetPub\wwwroot\usbccae2\data\database\formpubs.mdb;DefaultDir=C:\InetPub\wwwroot\usbccae2\data\database;Driver={Microsoft Access Driver (*.mdb)}"

However, I'd like to use relate pathname to the database file on the remote server and have it look something like the following:

"DBQ=..\data\database\formpubs.mdb;DefaultDir=..\data\database;Driver={Microsoft Access Driver (*.mdb)}"

But I get the following message appears when I load the ASP with the ralative pathnames:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)' isn't a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

I figure that by using ralative pathnames, the database is more portable and I don't have to know the direct pathnames to the database directories on the remote servers.

Please suggest the proper method to use for connecting the the database using relative pathnames...even if you don't know,  submit your reply as a "proposed answer" and I will allocate the points to you for helping me with my previous question.
The easiest thing is to make an ODBC connection to the database and use that to connect to the database. Then the only thing you need to do when moving the site to another server, is to create an ODBC connection on the new server.

To create a connection to an ODBC Access database, you only need 2 lines:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=YourODBCName"

i think you the access database needs to be located on the web server. Its a bug..
rick101396Author Commented:

I am hoping to create a DSNless connnection.  The reason is because I can only access the virtual directory of my web site on the server and also I don't want to have to ask the systems admin to add create the ODBC connection for me each time.  What I wanted to do was to create an UNC pathname as a connection to my database.  That way I could transport the database anywhere and I would only need to make some minimal changes.  

I hope I didn't go off track from what you were saying.  thanks for the ideas.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.