Error 80004005 when creating recordset object in ASP file, but works fine in Global.ASA

[Grahammer]

For some reason I am getting an error when trying to open a recordset on an
Access database on my Win2K3 machine from my INDEX.ASP page, but the same
code accesses the database fine when coming from the GLOBAL.ASA page. I'm
using a DSN to connect to the database.

The error I'm getting is:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
/index.asp, line 14

My code looks like:
dim objConn
set objConn= server.CreateObject("ADODB.Connection")
objConn.Open "DNS=SparesDB.dsn"
dim objRS
set objRS = server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM tblUsers WHERE UserName='" &
request.form("txtuser") & "' AND Password='" & request.form("txtPass") &
"';", objConn, ,3
if objRS.recordcount=1 then
...some code

The error occurs in 'set objRS = server.CreateObject("ADODB.Recordset")'

The DSN has no username/password. I have both disabling and enabling
anonymous access to the web site. Disabling access forces me to enter a
valid domain username/password combination when GLOBAL.ASA is executed, but
I still get the same error in my INDEX.ASP page.

My files are located as follows:
- D:\Internet\mypage.com\web holds my web pages (ASP, HTML, CSS, images,
etc.)
- D:\Internet\mypage.com\db holds my MS Access database (I will eventually
migrate the database to a proper MS SQL server)
- My DSN is named "SparesDB.dsn", has no password and points to my Access
file.

Since my DB folder is at the same level as my WEB folder, I shouldn't need
to specify a username or password... At least this is what I found online:
"With DSN and User ID/Password (NOTE: a user/pass is NOT needed if you place
your database in the /database directory on the same level as the public
/html directory so this is rarely used)" Is it necessary to actually name
the folders "html" and "database" ???

Any assistance is appreciated!!!

 

[Ken Schaefer]

The problem is this line:

objConn.Open "DNS=SparesDB.dsn"

Instead, use:

objConn.Open "DSN=SparesDB"

(note DSN is misspelt, and you don't need the extension if you have a system
DSN, since the information is stored in the registry).

That said you should probably use the Jet OLEDB Provider instead.

Cheers
Ken


: For some reason I am getting an error when trying to open a recordset on
an
: Access database on my Win2K3 machine from my INDEX.ASP page, but the same
: code accesses the database fine when coming from the GLOBAL.ASA page. I'm
: using a DSN to connect to the database.
:
: The error I'm getting is:
: Microsoft OLE DB Provider for ODBC Drivers error '80004005'
: [Microsoft][ODBC Driver Manager] Data source name not found and no
: default driver specified
: /index.asp, line 14
:
: My code looks like:
: dim objConn
: set objConn= server.CreateObject("ADODB.Connection")
: objConn.Open "DNS=SparesDB.dsn"
: dim objRS
: set objRS = server.CreateObject("ADODB.Recordset")
: objRS.Open "SELECT * FROM tblUsers WHERE UserName='" &
: request.form("txtuser") & "' AND Password='" & request.form("txtPass") &
: "';", objConn, ,3
: if objRS.recordcount=1 then
: ...some code
:
: The error occurs in 'set objRS = server.CreateObject("ADODB.Recordset")'
:
: The DSN has no username/password. I have both disabling and enabling
: anonymous access to the web site. Disabling access forces me to enter a
: valid domain username/password combination when GLOBAL.ASA is executed,
but
: I still get the same error in my INDEX.ASP page.
:
: My files are located as follows:
: - D:\Internet\mypage.com\web holds my web pages (ASP, HTML, CSS, images,
: etc.)
: - D:\Internet\mypage.com\db holds my MS Access database (I will
eventually
: migrate the database to a proper MS SQL server)
: - My DSN is named "SparesDB.dsn", has no password and points to my Access
: file.
:
: Since my DB folder is at the same level as my WEB folder, I shouldn't need
: to specify a username or password... At least this is what I found online:
: "With DSN and User ID/Password (NOTE: a user/pass is NOT needed if you
place
: your database in the /database directory on the same level as the public
: /html directory so this is rarely used)" Is it necessary to actually name
: the folders "html" and "database" ???
:
: Any assistance is appreciated!!!
:
:

 

[Grahammer]

Do I feel like an idiot!

I stared at that thing for an HOUR!

I appreciate it muchly!

BTW... I'm quite new to this. What is involved in using the Jet OLEDB
provider?

Thanks!

The problem is this line:

objConn.Open "DNS=SparesDB.dsn"

Instead, use:

objConn.Open "DSN=SparesDB"

(note DSN is misspelt, and you don't need the extension if you have a system
DSN, since the information is stored in the registry).

That said you should probably use the Jet OLEDB Provider instead.

: For some reason I am getting an error when trying to open a recordset on
an
: Access database on my Win2K3 machine from my INDEX.ASP page, but the same
: code accesses the database fine when coming from the GLOBAL.ASA page. I'm
: using a DSN to connect to the database.
:
: The error I'm getting is:
: Microsoft OLE DB Provider for ODBC Drivers error '80004005'
: [Microsoft][ODBC Driver Manager] Data source name not found and no
: default driver specified
: /index.asp, line 14

 

[Ken Schaefer]

strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\database\myDatabase.mdb;"

If you don't know the physical path to your database, you can use the
Server.Mappath() function to return the physical path.

The latest Jet Provider is available here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;282010&

Cheers
Ken

: Do I feel like an idiot!
:
: I stared at that thing for an HOUR!
:
: I appreciate it muchly!
:
: BTW... I'm quite new to this. What is involved in using the Jet OLEDB
: provider?
:
: Thanks!
:
:
: : > The problem is this line:
: >
: > objConn.Open "DNS=SparesDB.dsn"
: >
: > Instead, use:
: >
: > objConn.Open "DSN=SparesDB"
: >
: > (note DSN is misspelt, and you don't need the extension if you have
: a system
: > DSN, since the information is stored in the registry).
: >
: > That said you should probably use the Jet OLEDB Provider instead.
:
: > : > : For some reason I am getting an error when trying to open a
: recordset on
: > an
: > : Access database on my Win2K3 machine from my INDEX.ASP page, but
: the same
: > : code accesses the database fine when coming from the GLOBAL.ASA
: page. I'm
: > : using a DSN to connect to the database.
: > :
: > : The error I'm getting is:
: > : Microsoft OLE DB Provider for ODBC Drivers error '80004005'
: > : [Microsoft][ODBC Driver Manager] Data source name not found and
: no
: > : default driver specified
: > : /index.asp, line 14
:
:

 

[Grahammer]

Thanks Ken!

I'll look into it.

At the moment, portability is more important that performance.

This system MAY move to a MSSQL server, MySQL or possibly Oracle. If I
read correctly the ODBC connection provided by the DSN provides a
common interface to these databases.

I will do some more digging though.

Thanks!

 

[Bob Barrows]

Thanks Ken!

I'll look into it.

At the moment, portability is more important that performance.

This system MAY move to a MSSQL server, MySQL or possibly Oracle. If I
read correctly the ODBC connection provided by the DSN provides a
common interface to these databases.

I will do some more digging though.

Thanks!

That reasoning makes no sense.

If the database server name does not change when the app is ported, the
connection string does not have to change. If the database server name can
be different, the DSN would have to change as well when the app is ported..
There really is no advantage to using a DSN. In fact, MS recommends against
it:
http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp
(see the section that talks about MSDASQL )

ADO communicates with ODBC (DSN) via the MSDASQL provider.

Bob Barrows

 

[Grahammer]

That reasoning makes no sense.

It does to me... I don't know what I'm doing, yet!

If the database server name does not change when the app is ported, the
connection string does not have to change. If the database server name can
be different, the DSN would have to change as well when the app is

ported..

I was just looking at "www.connectionstrings.com and see that when
using OLEDB:

For a MSSQL server - "Provider=sqloledb;Data Source=Aron1;Initial
Catalog=pubs;User Id=sa;Password=asdasd;"

For an Access database - "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\somepath\mydb.mdb;User Id=admin;Password=;"

but when using a DSN I can use the following for either kind of
database: "DSN=myDsn;Uid=username;Pwd=;"

So what this means is I just have to alter the DSN to point to a new
kind of database once I move my tables from Access to MSSQL. Seems
simpler to me. (Also, doesn't the DSN connection do some SQL
conversion when there are differences in how a specific database type
works?)

There really is no advantage to using a DSN. In fact, MS recommends against
http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp
(see the section that talks about MSDASQL )

ADO communicates with ODBC (DSN) via the MSDASQL provider.

This is a good reason to switch though. I want to use the current
technology. I'll be looking at it!

 

[Aaron Bertrand - MVP]

http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp

(see the section that talks about MSDASQL )

Thanks, added this link to www.aspfaq.com/2126

 

[Aaron Bertrand - MVP]

So what this means is I just have to alter the DSN to point to a new

kind of database once I move my tables from Access to MSSQL.

Big deal.

How often are you planning to migrate? Once?

By using a DSN, you ease administrative hassles when you move to a new
machine (never mind migrating database platforms) -- someone with admin
privileges on the web server will have to add the DSN if you switch servers,
and many hosts charge a fee for that.

By using the native OLE-DB provider, you ensure slightly better performance
through the life of the app, and are not using deprecated,
soon-to-be-unsupported technology.

And if you think changing the DSN is going to be the biggest hassle when you
move from Access to SQL Server, you might want to go over this article:
http://www.aspfaq.com/2214

 

[Aaron Bertrand - MVP]

By using a DSN, you ease administrative hassles

Sorry, this should say add, not ease.

Also, a recommendation: store your connection string in an #include file
that gets referenced in all data access pages, or in an application variable
from global.asa. This way, when you migrate to a real database, "all that
hard work" of changing the connection string is only done once, instead of
multiple times (or with a search and replace tool, if all the files have an
identical connection string).

Or, maybe, you could just use MSDE now, instead of mucking with Crap-cess at
all. The transition from MSDE to SQL Server will be essentially seamless.

 

[Grahammer]

Sorry, this should say add, not ease.

Also, a recommendation: store your connection string in an #include file
that gets referenced in all data access pages, or in an application variable
from global.asa.

Definately will be doing this.

This way, when you migrate to a real database, "all that
hard work" of changing the connection string is only done once, instead of
multiple times (or with a search and replace tool, if all the files have an
identical connection string).

Or, maybe, you could just use MSDE now, instead of mucking with Crap-cess at
all. The transition from MSDE to SQL Server will be essentially

seamless.

I'd love to, but I keep getting told, "That's good... Can you do this
as well?" At that point I have to start altering the tables and such
and find Access is much easier to manage at that point. I'm probably
overlooking some MSSQL tool that lets me work with tables just as
easily, but I haven't found it.

At this point I'm more interested in getting some kind of system
running to present.

I appreciate all the help!

 

[Aaron Bertrand - MVP]

I'd love to, but I keep getting told, "That's good... Can you do this

as well?" At that point I have to start altering the tables and such
and find Access is much easier to manage at that point. I'm probably
overlooking some MSSQL tool that lets me work with tables just as
easily, but I haven't found it.

No offence, but that seems like a pretty lame excuse for deciding on a
database platform, IMHO.

I can't see that Enterprise Manager is any more difficult than the Access
GUI (except there are way more options). However, I typically recommend
against using EM in most cases, opting for Query Analyzer (see
http://www.aspfaq.com/2455). However, this requires some knowledge of
constructing both DML and DDL statements. Also, there are many tools out
there you can use to manage MSDE / SQL Server, and many are "dumbed down" to
a simple point and click interface, several web-based... see
http://www.aspfaq.com/2442 for a partial list. Some of these tools might
even be usable with Access.

 

[Chris Hohmann]

Big deal.

How often are you planning to migrate? Once?

By using a DSN, you ease administrative hassles when you move to a new
machine (never mind migrating database platforms) -- someone with admin
privileges on the web server will have to add the DSN if you switch servers,
and many hosts charge a fee for that.

By using the native OLE-DB provider, you ensure slightly better performance
through the life of the app, and are not using deprecated,
soon-to-be-unsupported technology.

And if you think changing the DSN is going to be the biggest hassle when you
move from Access to SQL Server, you might want to go over this article:
http://www.aspfaq.com/2214

I wanted to make a few clarifications:
1. DSN-Less connections are supported for ODBC as well as OLEDB. So I
think we can all agree that from an administrative standpoint, DSN-Less
connections are the way to go.
2. The original poster (OP) does make a valid point about the
portability benefits of ODBC over using the native OLEDB provider for
SQL-Server. ODBC should (theoretically) blackbox all the implementation
differences among the underlying databases, such as those described in
the aforementioned aspfaq article, at the expense of performance and
stability. That being said, I would like to propose that instead of
relying on ODBC as a universal translator that you uses parameterized
queries/stored procedures to create a interface layer between your
database and your external apps. This way you can reap the benefits of
using OLEDB while retaining a measure of the portability offered by
ODBC. To my mind, a nicely implemented stored procedure interface layer
is what distinguishes a database application from just another bunch of
tables.

I've been seeing recurring examples of this "happy median" or "sweet
spot" principle. I get the feeling that there's some sort of homeostasis
thing going on. Anyway, that's my $0.02.

HTH
-Chris