Problem getting connction to Access DB using ASP

[Quintus Snapper]

I have a website running on a Win2000 Machine (IIS), and it's
programmed in ASP and uses an Access 2000 DB.

The site generally runs fine. However I used to have a problem that
occasionally the website could no longer get a connection to the DB. I
had noticed that this always happened after I had downloaded the DB
made some changes to the DB structure and uploaded it again. The ASP
code would always fail at connection BD connection line. The only
solution I found to resolve the problem would be to restart the
server! I tried switching to a DSN Less connection (used to use
ODBC... see my connection string below) but that also didn't prevent
it from happening.

After having given up on trying to find out how not prevent this from
happening after uploading, I ended up figured out how to make all
changes to the DB with SQL commands which although a bit more
cumbersome (for me anyway!), did allow me to make the changes and have
the added benefit of not having to take the site offline for a couple
of mins so I could live with that.

However... to my surprise I now have the same problem AND I have't
made any change the the DB myself! This is very annoying (especially
like now if it happens during the weekend and I have no service at the
hosting provider till mon!).

Has anyone else had this experience? What is the cause? Is there a way
to prevent this from happening? Or at least a way to set it right
again without having to restart the server? Since the problem appears
NOT to be with the Database (if I download it locally it works fine),
it would lead me to belive it has something to do with the Access
driver. I had tried to use the OLEDB driver, but several of my queries
did not work or gave different results to the Access Driver.

Hoping there is an "easy" fix!

Thanks for any help in advance,


Quintus

---
<%

'response.Redirect("../default_temp.asp")
'response.end

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
'ConnStr = "DSN=toucanart"

dbpath = "d:/http/toucanart.net/Databases/project21.mdb"

'ConnStr="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
'ConnStr=ConnStr & dbpath & ";"

'ConnStr=ConnStr & "Password=whatever;"


ConnStr2="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath

ConnStr="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath

conn.open ConnStr

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
%>

[Bob Barrows]

You need to tell us the error message. You should avoid using ODBC, as the
Jet OLEDB provider will have the most features and provide the most robust
connection. I know your experience argues against the latter, but without
the error message, it's pretty hard for us to troubleshoot ...

I do recall that compacting a database will sometimes cause permissions
problems (IUSR no longer has permissions for the compacted database), so
that's the first thing I would investigate. Instead of restarting the
server, explicitly grant Change permissions on the newly uploaded database
to the IUSR account.

Bob Barrows

Quintus Snapper wrote:

> I have a website running on a Win2000 Machine (IIS), and it's
> programmed in ASP and uses an Access 2000 DB.
>
> The site generally runs fine. However I used to have a problem that
> occasionally the website could no longer get a connection to the DB. I
> had noticed that this always happened after I had downloaded the DB
> made some changes to the DB structure and uploaded it again. The ASP
> code would always fail at connection BD connection line. The only
> solution I found to resolve the problem would be to restart the
> server! I tried switching to a DSN Less connection (used to use
> ODBC... see my connection string below) but that also didn't prevent
> it from happening.
>
> After having given up on trying to find out how not prevent this from
> happening after uploading, I ended up figured out how to make all
> changes to the DB with SQL commands which although a bit more
> cumbersome (for me anyway!), did allow me to make the changes and have
> the added benefit of not having to take the site offline for a couple
> of mins so I could live with that.
>
> However... to my surprise I now have the same problem AND I have't
> made any change the the DB myself! This is very annoying (especially
> like now if it happens during the weekend and I have no service at the
> hosting provider till mon!).
>
> Has anyone else had this experience? What is the cause? Is there a way
> to prevent this from happening? Or at least a way to set it right
> again without having to restart the server? Since the problem appears
> NOT to be with the Database (if I download it locally it works fine),
> it would lead me to belive it has something to do with the Access
> driver. I had tried to use the OLEDB driver, but several of my queries
> did not work or gave different results to the Access Driver.
>
> Hoping there is an "easy" fix!
>
> Thanks for any help in advance,
>
>
> Quintus
>
> ---
> <%
>
> 'response.Redirect("../default_temp.asp")
> 'response.end
>
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> 'ConnStr = "DSN=toucanart"
>
> dbpath = "d:/http/toucanart.net/Databases/project21.mdb"
>
> 'ConnStr="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
> 'ConnStr=ConnStr & dbpath & ";"
>
> 'ConnStr=ConnStr & "Password=whatever;"
>
>
> ConnStr2="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath
>
> ConnStr="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath
>
> conn.open ConnStr
>
> '---- CursorTypeEnum Values ----
> Const adOpenForwardOnly = 0
> Const adOpenKeyset = 1
> Const adOpenDynamic = 2
> Const adOpenStatic = 3
>
> '---- LockTypeEnum Values ----
> Const adLockReadOnly = 1
> Const adLockPessimistic = 2
> Const adLockOptimistic = 3
> Const adLockBatchOptimistic = 4
>
> '---- CursorLocationEnum Values ----
> Const adUseServer = 2
> Const adUseClient = 3
> %>

[Quintus Snapper]

My server does not give very detailed error message. Since it's hosted
in DE, I get some German error message, it always gives an error
message on the line htat contains "conn.open ConnStr".

Do you know what exactly the difference it between the Access Driver
and the Jet OLEDB one?
Obviously one thing it does interpret the SQL statements differently
as when I tried it, although most of the website worked, certain parts
were giving different results! If I knew a bit more about what was
different make it easier to update the site as now there are so many
queries all over the place will take ages to find and check all of
them.

BTW, I finally managed to get it working again without restarting the
server, but removing the line "ConnStr="DRIVER={Microsoft Access
Driver (*.mdb)};DBQ=" & dbpath", trying to access the site (naturally
didn't work) and then adding it again!

Quintus

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<O7PWs60XDHA.2516@TK2MSFTNGP09.phx.gbl>...

> You need to tell us the error message. You should avoid using ODBC, as the
> Jet OLEDB provider will have the most features and provide the most robust
> connection. I know your experience argues against the latter, but without
> the error message, it's pretty hard for us to troubleshoot ...
>
> I do recall that compacting a database will sometimes cause permissions
> problems (IUSR no longer has permissions for the compacted database), so
> that's the first thing I would investigate. Instead of restarting the
> server, explicitly grant Change permissions on the newly uploaded database
> to the IUSR account.
>
> Bob Barrows
>
> Quintus Snapper wrote:

> > I have a website running on a Win2000 Machine (IIS), and it's
> > programmed in ASP and uses an Access 2000 DB.
> >
> > The site generally runs fine. However I used to have a problem that
> > occasionally the website could no longer get a connection to the DB. I
> > had noticed that this always happened after I had downloaded the DB
> > made some changes to the DB structure and uploaded it again. The ASP
> > code would always fail at connection BD connection line. The only
> > solution I found to resolve the problem would be to restart the
> > server! I tried switching to a DSN Less connection (used to use
> > ODBC... see my connection string below) but that also didn't prevent
> > it from happening.
> >
> > After having given up on trying to find out how not prevent this from
> > happening after uploading, I ended up figured out how to make all
> > changes to the DB with SQL commands which although a bit more
> > cumbersome (for me anyway!), did allow me to make the changes and have
> > the added benefit of not having to take the site offline for a couple
> > of mins so I could live with that.
> >
> > However... to my surprise I now have the same problem AND I have't
> > made any change the the DB myself! This is very annoying (especially
> > like now if it happens during the weekend and I have no service at the
> > hosting provider till mon!).
> >
> > Has anyone else had this experience? What is the cause? Is there a way
> > to prevent this from happening? Or at least a way to set it right
> > again without having to restart the server? Since the problem appears
> > NOT to be with the Database (if I download it locally it works fine),
> > it would lead me to belive it has something to do with the Access
> > driver. I had tried to use the OLEDB driver, but several of my queries
> > did not work or gave different results to the Access Driver.
> >
> > Hoping there is an "easy" fix!
> >
> > Thanks for any help in advance,
> >
> >
> > Quintus
> >
> > ---
> > <%
> >
> > 'response.Redirect("../default_temp.asp")
> > 'response.end
> >
> > Dim conn
> > Set conn = Server.CreateObject("ADODB.Connection")
> > 'ConnStr = "DSN=toucanart"
> >
> > dbpath = "d:/http/toucanart.net/Databases/project21.mdb"
> >
> > 'ConnStr="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
> > 'ConnStr=ConnStr & dbpath & ";"
> >
> > 'ConnStr=ConnStr & "Password=whatever;"
> >
> >
> > ConnStr2="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath
> >
> > ConnStr="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath
> >
> > conn.open ConnStr
> >
> > '---- CursorTypeEnum Values ----
> > Const adOpenForwardOnly = 0
> > Const adOpenKeyset = 1
> > Const adOpenDynamic = 2
> > Const adOpenStatic = 3
> >
> > '---- LockTypeEnum Values ----
> > Const adLockReadOnly = 1
> > Const adLockPessimistic = 2
> > Const adLockOptimistic = 3
> > Const adLockBatchOptimistic = 4
> >
> > '---- CursorLocationEnum Values ----
> > Const adUseServer = 2
> > Const adUseClient = 3
> > %>

[Bob Barrows]

Quintus Snapper wrote:

> My server does not give very detailed error message. Since it's hosted
> in DE, I get some German error message, it always gives an error
> message on the line htat contains "conn.open ConnStr".

Do you get an error number?

>
> Do you know what exactly the difference it between the Access Driver
> and the Jet OLEDB one?

The difference is one uses the older ODBC technology, which is being phased
out, and the other uses the newer OLEDB technology.

> Obviously one thing it does interpret the SQL statements differently
> as when I tried it, although most of the website worked, certain parts
> were giving different results!

Example, please?

> If I knew a bit more about what was
> different make it easier to update the site as now there are so many
> queries all over the place will take ages to find and check all of
> them.
>
> BTW, I finally managed to get it working again without restarting the
> server, but removing the line "ConnStr="DRIVER={Microsoft Access
> Driver (*.mdb)};DBQ=" & dbpath", trying to access the site (naturally
> didn't work) and then adding it again!
>

I don't understand this sequence of events.

Bob

[Wes]

I have had a similar problem with my web server. The ASP works fine,
everything is great, and then once in a while, when I log into the server,
I'll have the MS script debugger waiting for me to skip the debugging.
There is no problem with the script, its flawless and works on other
machines connecting to DB's just fine without these issues.




"Quintus Snapper" <qsnapper@terra.es> wrote in message
news:29c2044a.0308100503.2719b86@posting.google.co m...

> I have a website running on a Win2000 Machine (IIS), and it's
> programmed in ASP and uses an Access 2000 DB.
>
> The site generally runs fine. However I used to have a problem that
> occasionally the website could no longer get a connection to the DB. I
> had noticed that this always happened after I had downloaded the DB
> made some changes to the DB structure and uploaded it again. The ASP
> code would always fail at connection BD connection line. The only
> solution I found to resolve the problem would be to restart the
> server! I tried switching to a DSN Less connection (used to use
> ODBC... see my connection string below) but that also didn't prevent
> it from happening.
>
> After having given up on trying to find out how not prevent this from
> happening after uploading, I ended up figured out how to make all
> changes to the DB with SQL commands which although a bit more
> cumbersome (for me anyway!), did allow me to make the changes and have
> the added benefit of not having to take the site offline for a couple
> of mins so I could live with that.
>
> However... to my surprise I now have the same problem AND I have't
> made any change the the DB myself! This is very annoying (especially
> like now if it happens during the weekend and I have no service at the
> hosting provider till mon!).
>
> Has anyone else had this experience? What is the cause? Is there a way
> to prevent this from happening? Or at least a way to set it right
> again without having to restart the server? Since the problem appears
> NOT to be with the Database (if I download it locally it works fine),
> it would lead me to belive it has something to do with the Access
> driver. I had tried to use the OLEDB driver, but several of my queries
> did not work or gave different results to the Access Driver.
>
> Hoping there is an "easy" fix!
>
> Thanks for any help in advance,
>
>
> Quintus
>
> ---
> <%
>
> 'response.Redirect("../default_temp.asp")
> 'response.end
>
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> 'ConnStr = "DSN=toucanart"
>
> dbpath = "d:/http/toucanart.net/Databases/project21.mdb"
>
> 'ConnStr="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
> 'ConnStr=ConnStr & dbpath & ";"
>
> 'ConnStr=ConnStr & "Password=whatever;"
>
>
> ConnStr2="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath
>
> ConnStr="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbpath
>
> conn.open ConnStr
>
> '---- CursorTypeEnum Values ----
> Const adOpenForwardOnly = 0
> Const adOpenKeyset = 1
> Const adOpenDynamic = 2
> Const adOpenStatic = 3
>
> '---- LockTypeEnum Values ----
> Const adLockReadOnly = 1
> Const adLockPessimistic = 2
> Const adLockOptimistic = 3
> Const adLockBatchOptimistic = 4
>
> '---- CursorLocationEnum Values ----
> Const adUseServer = 2
> Const adUseClient = 3
> %>