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!!!

[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


"Grahammer" <postmaster@127.0.0.1> wrote in message
news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
: 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


"Grahammer" <postmaster@127.0.0.1> wrote in message
news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
: 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!


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uGYaCpxZDHA.2072@TK2MSFTNGP10.phx.gbl...

> 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.

> "Grahammer" <postmaster@127.0.0.1> wrote in message
> news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
> : 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]

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!


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uGYaCpxZDHA.2072@TK2MSFTNGP10.phx.gbl...

> 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.

> "Grahammer" <postmaster@127.0.0.1> wrote in message
> news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
> : 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:
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;282010&[/url]

Cheers
Ken

"Grahammer" <webmaster@127.0.0.1> wrote in message
news:e%23mB4cyZDHA.2024@TK2MSFTNGP12.phx.gbl...
: 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!
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:uGYaCpxZDHA.2072@TK2MSFTNGP10.phx.gbl...
: > 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.
:
: > "Grahammer" <postmaster@127.0.0.1> wrote in message
: > news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
: > : 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:
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;282010&[/url]

Cheers
Ken

"Grahammer" <webmaster@127.0.0.1> wrote in message
news:e%23mB4cyZDHA.2024@TK2MSFTNGP12.phx.gbl...
: 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!
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:uGYaCpxZDHA.2072@TK2MSFTNGP10.phx.gbl...
: > 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.
:
: > "Grahammer" <postmaster@127.0.0.1> wrote in message
: > news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
: > : 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!


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uVpXJo4ZDHA.2136@TK2MSFTNGP10.phx.gbl...

> 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:
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;282010&[/url]
>
> Cheers
> Ken
>
> "Grahammer" <webmaster@127.0.0.1> wrote in message
> news:e%23mB4cyZDHA.2024@TK2MSFTNGP12.phx.gbl...
> : 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!
> :
> :
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:uGYaCpxZDHA.2072@TK2MSFTNGP10.phx.gbl...
> : > 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.

> :
> : > "Grahammer" <postmaster@127.0.0.1> wrote in message
> : > news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
> : > : 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!


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uVpXJo4ZDHA.2136@TK2MSFTNGP10.phx.gbl...

> 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:
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;282010&[/url]
>
> Cheers
> Ken
>
> "Grahammer" <webmaster@127.0.0.1> wrote in message
> news:e%23mB4cyZDHA.2024@TK2MSFTNGP12.phx.gbl...
> : 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!
> :
> :
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:uGYaCpxZDHA.2072@TK2MSFTNGP10.phx.gbl...
> : > 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.

> :
> : > "Grahammer" <postmaster@127.0.0.1> wrote in message
> : > news:efAeAPxZDHA.2284@TK2MSFTNGP12.phx.gbl...
> : > : 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
> :
> :
>
>

[Bob Barrows]

Grahammer wrote:

> 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:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp[/url]
(see the section that talks about MSDASQL )

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

Bob Barrows

[Bob Barrows]

Grahammer wrote:

> 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:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp[/url]
(see the section that talks about MSDASQL )

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

Bob Barrows

[Grahammer]

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:Oukr8E9ZDHA.2072@TK2MSFTNGP10.phx.gbl...

> Grahammer wrote:

> > 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.

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

> it:
>

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

> (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!

[Grahammer]

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:Oukr8E9ZDHA.2072@TK2MSFTNGP10.phx.gbl...

> Grahammer wrote:

> > 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.

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

> it:
>

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

> (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]

> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp[/url]

> (see the section that talks about MSDASQL )

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

[Aaron Bertrand - MVP]

> [url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated.asp[/url]

> (see the section that talks about MSDASQL )

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

[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:
[url]http://www.aspfaq.com/2214[/url]

[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:
[url]http://www.aspfaq.com/2214[/url]

[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.

[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.