Access linked tables and asp pages

[DenverMarc]

Hello,

I've inherited the maintenance of our intranet sites, which use several Access databases and .asp pages. Many of the databases use the same tables, and currently keep separate copies of the tables, instead of linking them to one database.

When I attempted to do this, I got an error:

Microsoft JET Database Engine error '80004005'
The Microsoft Jet database engine cannot open the file
'\\binbosiis002\orgs\dm2\data\product.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

"product.mdb" is the main database that stores the tables. I don't think this is a permissions problem, as I've checked Microsoft help files and permissions seem to be there.

I'm really new to the whole asp thing, but does this have something to do with "include" files. The web page where I get this error has an include file to the database that has the queries I need and the tables are linked to the product.mdb table.

Can you tell me what I need to do to make this work?

Thanks,

Marc

 

[Sheco]

Check to make sure that your IUSER_MachineName default anonymous web user account has permissions on your .MDB file.

You might also have a problem using ASP to get at tables that are linked inside of Access.

 

[DenverMarc]

Thanks Sheco,

Yea, the permissions seem okay.

I was afraid you'd say it probably can't be done.

Thanks,

Marc

 

[Sheco]

I'm not saying that it can't be done.

I'm saying that I've had problems getting it to work for me.

I tested this out just a few days ago and sorta gave up in frustration.

I took a VBS script that worked just fine against linked tables in an .mdb and converted it to ASP... I got that same 80004005 error.

I tried it linking to tables on SQL Server, Oracle, and DB2 ... all gave the same result.

That tells me the problem was most likely not in the ODBC database drivers but rather something else about the difference between the IIS environment and the local windows scripting host.

Permissions comes to mind, but I didn't know why what or how. I tried turning off anonymous access ... connected to the web server as domain administrator account ... same error!

It *could* be the way that IIS tries to use transactions and connection pooling for database but, rather than investigate that option, I just created an ADO connection directly to the linked table.

Another test, which I didn't do, might be to link tables from 1 Oracle server to another and see if you can follow that sort of link... to determine if it is just an Access thing or what... but I didnt do that test.

If you figure it out I'd love to hear the answer.

 

[DenverMarc]

I'm pretty new to this stuff, so excuse me if anything sounds ignorant.

It seems as though the asp page is finding the database that contains the queries but not the one that contains the tables used in the queries. Is there some change/addition to the "include" files that could be made to connect to both databases?

Thanks again for your time and effort.

 

[steven290]

post your code

 

[DenverMarc]

I'm not sure how much you need to see.

The asp page where I'm getting the error:
<!--#include virtual="/orgs/dm2/navbar.asp"-->
<!--#include virtual="/orgs/dm2/includes/competitor.asp"-->

<tr>
<td width="100%" valign="top"><font face="verdana, sans-serif" size="2">Click on product
name to view competitive report.</font></td>

<td width="60%"><strong><small><font face="Verdana">Product Name</font></small></strong></td>

</tr>
<% sql="SELECT ProductID,ProductName,ProductOwner FROM qrySortProduct ;" %>
<% Set RSInfo=Conn.Execute(sql)%>

<% Do While Not RSInfo.eof%>
<tr>
<td width="60%"><small><font face="Verdana"><a
href="info.asp?id=<%= RSInfo("ProductID")%>"><%= RSInfo("ProductName")%></a> </font></small></td>
<td width="40%" align="left"><p align="left"><small><font face="Verdana"><%= RSInfo("ProductOwner")%></font></small></td>
</tr>
<% RSInfo.MoveNext%>
<% Loop %>
<%
RSInfo.close
Set RSInfo=nothing
Conn.close
Set Conn=nothing
%>


The includes file:

<%
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & Server.Mappath ("/orgs/dm2/data/competitor.mdb")
Conn.Open
%>

Thanks,

Marc

 

[PaulBricker]

I connect to a few linked tables and never have problems, but I don't use Server.Mappath. I use a full path to accomplish it. Also, my two db's are in the same folder on my server.

Dim objConn, dbPath, strProvider
  dbPath = "PATH TO .mdb"
  Set objConn = Server.CreateObject("ADODB.Connection")
  strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
  objConn.Open strProvider
 sql="SELECT ProductID,ProductName,ProductOwner FROM qrySortProduct"   
Set rsInfo = Server.CreateObject("ADODB.Recordset")
rsInfo.Open sql, strProvider

I've mixed a little of yours with a little of mine.

Paul

 

[DenverMarc]

Thanks Paul,

Sorry to need all the hand-holding here, but now I have a different error message. I changed the include file to read:

Dim objConn, dbPath, strProvider
dbPath = "PATH TO .mdb"
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath ("/orgs/dm2/data/competitor.mdb")
objConn.Open strProvider

Which seems great, that it's now finding the table. But now I get the error message:

Microsoft VBScript runtime error '800a000d'

Type mismatch

/orgs/dm2/includes/competitor.asp, line 5

Does this have something to do with the ProductID being an integer?

Thanks,

Marc

 

[steven290]

this is wrong

Dim objConn, dbPath, strProvider
dbPath = [red]"PATH TO .mdb"[/red]
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & [red]dbpath ("/orgs/dm2/data/competitor.mdb")[/red]
objConn.Open strProvider

if you your gonna use server.mappath then you will need to use a relative path

this is assume you place the mdb file in the same directory as your asp file

dbPath = "competitor.mdb"
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath
objConn.Open strProvider

if you not gonna use server.mappath you will need to use an absolute path

dbPath = "c:\inetpub\[URL unfurl="true"]wwwroot\orgs\dm2\data\competitor.mdb"[/URL]
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath
objConn.Open strProvider

 

[DenverMarc]

I don't think I have the training or knowledge to figure this out. Everything resides on one of our companies networks, not on my C drive. So how would I make an absolute path for that? And it seems like the virtual path is not working either. All the files are on the same network, but in different folders.

Thanks for all the suggestions.

Marc

 

[PaulBricker]

Do you know the path to your databases? If you do, then just post it here and we can work it into the code. If you don't know the path, then ask your server person for it. Either way, we should be able to work it out.


Paul

 

[DenverMarc]

\\binbosiis002\orgs\dm2\data\

Thanks,

Marc

 

[steven290]

is the database on the same server as your asp page.

It looks like the file is on another machine.

 

[DenverMarc]

Yes, they are on the same server. The asp page is in:

\\binbosiis002\orgs\dm2\competitors\product

 

[PaulBricker]

dbPath = "\\binbosiis002\orgs\dm2\data\competitor.mdb"
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath
objConn.Open strProvider
sql="SELECT ProductID,ProductName,ProductOwner FROM qrySortProduct"   
Set rsInfo = Server.CreateObject("ADODB.Recordset")
rsInfo.Open sql, strProvider

Try this and see if it helps.

Paul

 

[steven290]

Play with this

dbPath = server.mappath([red]"../../data/competitor.mdb"[/red])
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath
objConn.Open strProvider
sql="SELECT ProductID,ProductName,ProductOwner FROM qrySortProduct"   
Set rsInfo = Server.CreateObject("ADODB.Recordset")
rsInfo.Open sql, strProvider

or this
../data/competitor.mdb

or just move the mdb file into the same directory as your asp
and do this

dbPath = server.mappath("competitor.mdb")

 

[DenverMarc]

Paul and Steve,

Thanks for the continuing suggestions. Both resulted in almost the same error message. When I used:
dbPath = "\\binbosiis002\orgs\dm2\data\competitor.mdb"

I get the error message:

Microsoft JET Database Engine error '80004005'
The Microsoft Jet database engine cannot open the file '\\binbosiis002\orgs\dm2\data\competitor.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

/orgs/dm2/includes/competitor.asp, line 6


When I used:
dbPath = server.mappath("../../data/competitor.mdb")

I get the heading of the page but then still the message:

Microsoft JET Database Engine error '80004005'
The Microsoft Jet database engine cannot open the file '\\binbosiis002\orgs\dm2\data\product.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

/orgs/dm2/competitors/product/index.asp, line 63

It identifies the database where the TABLES are stored (product.mdb)- not where the queries are (competitor.mdb)

When I had looked up that error before Microsoft says it's a permissions problem. When I checked the permissions on the server there are 3 "names"
Administrators (BINBOSIIS002\Administrators)
Everyone
SYSTEM

They all have the boxes under "allow" checked so I didn't think this was a problem. Am I missing something?

Thanks again,

Marc

 

[steven290]

you need to add (as sheco said) IUSER_MachineName where Machinename is the name of your cpu, give it full permissions on the folder holding the ddatabase

 

[DenverMarc]

I have to have permission on my cpu? Does that mean everyone who wanted to view this web page would have to be set up individually with permissions?

Marc