Access connection mysteries

2 issues. The second one came up trying to solve the first.
First -
On my local machine XP Pro SP2 IIS6 I have 2 ASP pages connecting to an Access
2000 DB, using DSN-less ODBC (I know - but that's the second problem). The
structure looks like
root
subweb
db
roster
meeting

The first page, in the roster folder connects fine using:

if request.servervariables("Server_Name") = "broomhilda" then
DBPath = Server.MapPath("../db/rostern.mdb")
else
DBPath = Server.MapPath("/db/rostern.mdb")
end if
set conn=server.createobject("adodb.connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath
This works out to
DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=C:\Inetpub\wwwroot\subweb\db\rostern.mdb

The second page, in the meeting folder blows using:

if request.servervariables("Server_Name") = "broomhilda" then
DBPath = Server.MapPath("../db/meeting.mdb")
else
DBPath = Server.MapPath("/db/meeting.mdb")
end if
set conn=server.createobject("adodb.connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath <== Line21
This works out to
DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=C:\Inetpub\wwwroot\subweb\db\meeting.mdb

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry
key 'Temporary (volatile) Jet DSN for process 0x600 Thread 0x9e0 DBC 0x1094404 Jet'.
/subweb/meeting/reset_result.asp, line 21

I don't think this is the real error. Looking this one up on MS gives me some
ino on what a registry entry should look like. I don't have the sub-key, much
less the value (AND #1 works). I also did a compact/repair of the db to no
avail. It works fine on the ISP's server, but I prefer to develop/debug locally.

In trying to move from ODBC to OLEDDB, I used this on both pages, getting DBPath
the same as above.

cst = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbPath
set Conn=server.createobject("adodb.connection")
Conn.Open cst

Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/subweb/meeting/reset_result.asp, line 18, column 13
Conn.Open cst
------------^

Thanks, Mike

Re: Access connection mysteries

MikeR wrote:
> In trying to move from ODBC to OLEDDB, I used this on both pages,
> getting DBPath the same as above.
>
> cst = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbPath

Do a
Response.Write dbPath

If that looks OK then:

> set Conn=server.createobject("adodb.connection")
> Conn.Open cst
>
> Error Type:
> Microsoft VBScript compilation (0x800A0408)
> Invalid character
> /subweb/meeting/reset_result.asp, line 18, column 13
> Conn.Open cst
> ------------^
>
Response.Write cst

and show us the result if that doesn't clue you into the problem.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access connection mysteries

Bob Barrows [MVP] wrote:
> Response.Write cst
>
> and show us the result if that doesn't clue you into the problem.
Here's the result of the response.write of cst, which includes DBPath. Looks
correct to me... Line wrap may be wacky..

Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\Inetpub\wwwroot\subweb\db\meeting.mdb

Re: Access connection mysteries

MikeR wrote:
> Bob Barrows [MVP] wrote:
>> Response.Write cst
>>
>> and show us the result if that doesn't clue you into the problem.
> Here's the result of the response.write of cst, which includes
> DBPath. Looks correct to me... Line wrap may be wacky..
>
> Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=C:\Inetpub\wwwroot\subweb\db\meeting.mdb

Well, I certainly see no invalid characters there: in fact, given that
the Response.Write worked, there can't be any. I suspect you are blaming
the wrong line for generating the error.

Create a test page with only this code in it:

<%
option explicit
dim cst, conn
cst="Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & _
"C:\Inetpub\wwwroot\subweb\db\meeting.mdb"
response.write cst & "

"
set conn = createobject("adodb.connection")
conn.open cst
if conn.state=1 then
response.write "connection successfully opened"
conn.close
else
response.write "problem opening the connection"
end if
%>

If this code runs successfully, then start adding back in the logic to
select the correct database, testing your page with each additional
step, until you find the line that causes the problem.

If the above code does not run successfully, let us know what the error
is.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Access connection mysteries

Bob Barrows [MVP] wrote:
> Well, I certainly see no invalid characters there: in fact, given that
> the Response.Write worked, there can't be any. I suspect you are blaming
> the wrong line for generating the error.
Microsoft did the blaming, not me
I think it's the wrong error.
>
> Create a test page with only this code in it:
>
> <%
> option explicit
> dim cst, conn
> cst="Provider=Microsoft.Jet.OLEDB.4.0; " & _
> "Data Source=" & _
> "C:\Inetpub\wwwroot\subweb\db\meeting.mdb"
> response.write cst & "

"
> set conn = createobject("adodb.connection")
> conn.open cst
> if conn.state=1 then
> response.write "connection successfully opened"
> conn.close
> else
> response.write "problem opening the connection"
> end if
> %>

Error Type:
Microsoft JET Database Engine (0x80004005)
The Microsoft Jet database engine cannot open the file
'C:\Inetpub\wwwroot\subweb\db\meeting.mdb'. It is already opened exclusively by
another user, or you need permission to view its data.
/subweb/meeting/aa.asp, line 9

Re: Access connection mysteries

MikeR wrote:
> Bob Barrows [MVP] wrote:
>> Well, I certainly see no invalid characters there: in fact, given
>> that the Response.Write worked, there can't be any. I suspect you are
>> blaming the wrong line for generating the error.
> Microsoft did the blaming, not me
> I think it's the wrong error.
>>
>> Create a test page with only this code in it:
>>
>> <%
>> option explicit
>> dim cst, conn
>> cst="Provider=Microsoft.Jet.OLEDB.4.0; " & _
>> "Data Source=" & _
>> "C:\Inetpub\wwwroot\subweb\db\meeting.mdb"
>> response.write cst & "

"
>> set conn = createobject("adodb.connection")
>> conn.open cst
>> if conn.state=1 then
>> response.write "connection successfully opened"
>> conn.close
>> else
>> response.write "problem opening the connection"
>> end if
>> %>
>
> Error Type:
> Microsoft JET Database Engine (0x80004005)
> The Microsoft Jet database engine cannot open the file
> 'C:\Inetpub\wwwroot\subweb\db\meeting.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.
> /subweb/meeting/aa.asp, line 9

All users of the database file need to be able to create, modify and
delete a locking file that has a .ldb extension in the folder that
contains the database file. This means that users need Change/Modify
permissions for the folder, not just the .mdb file. Who the users are
depends on what type of authentication your website is using. If using
Anonymous, then the IUSR_machinename account requires permission for the
folder. Otherwise, all the accounts of the users themselves need the
permissions. See:
http://support.microsoft.com/default.aspx/kb/253604
http://support.microsoft.com/default.aspx/kb/166029
http://support.microsoft.com/kb/253580/EN-US/


Once the permissions are correct, you will be able to have the database
open in Access while running web pages that also use it.




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Access connection mysteries

Bob Barrows [MVP] wrote:
> All users of the database file need to be able to create, modify and
> delete a locking file that has a .ldb extension in the folder that
> contains the database file. This means that users need Change/Modify
> permissions for the folder, not just the .mdb file. Who the users are
> depends on what type of authentication your website is using. If using
> Anonymous, then the IUSR_machinename account requires permission for the
> folder. Otherwise, all the accounts of the users themselves need the
> permissions.
All the IIS permissions looked ok. The problem was adding IUSR_machinename in
Windows Explorer. Your test code (modified to match mine by adding the mappath
stuff) then fell into the "connection sucesssfully opened" branch.

My code went back to the VB error thing. It didn't even respond to the
response.write, response.end unless I commented out Conn.Open cst. I deleted the
variable cst and a couple of blank lines below it , readded it and BINGO. Must
have been an invisible character.

So, thanks to you, life is good again.