FrontPage-designed web page can't get to newly added table

[RSW]

I have an .asp page that had the following query:

SQL="SELECT * FROM WorkOrder WHERE WorkOrder_Number='" &strWorkOrder_Number&"'"

and that worked fine. However, I wanted to also link to a different database on a different server. So I used Linked Table Manager in Access to create a link, generated the query, and it works fine in Access. The new query is:

SQL="SELECT WorkOrder.*, tblRequests.REQUEST_DT, tblRequests.DELIVERY_DT, tblRequests.DOCK_DT, tblRequests.REQUEST_STATUS, tblRequests_1.REQUEST_DT, tblRequests_1.DELIVERY_DT, tblRequests_1.DOCK_DT, tblRequests_1.REQUEST_STATUS FROM (WorkOrder LEFT JOIN tblRequests ON WorkOrder.ID1 = tblRequests.ID) LEFT JOIN tblRequests AS tblRequests_1 ON WorkOrder.ID2 = tblRequests_1.ID WHERE WorkOrder.WorkOrder_Number='" &strWorkOrder_Number&"'"

Unfortunately, I receive this error when loading the updated page:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '\\server\Backend.mdb'. It is already opened exclusively by another user, or you need permission to view its data. 

DisplayWO.asp, line 30

Through trial and error, I have determined that any time I try to get to tblRequests in the query, it breaks. But, again, the database query itself works fine.

Does anyone know why the .asp page can't handle the link? Is there a different way I should be doing this?

Thanks in advance!

 

[RSW]

I really hope there is some way to handle this. If there is any more information I can provide, just let me know.

 

[Simon_MT]

I would suggest using the explicit file references rather than \\.

Do you use a global.asa file?

Simon

 

[RSW]

I would suggest using the explicit file references rather than \\.

Do you use a global.asa file?

Simon

The \\ may be misleading--I shorted the link for brevity and confidentiality's sake, but the link does go all the way back to the server. It has to start with \\, right?

I did locate a global.asa file (which I had never seen before). The database I want to add doesn't seem to appear in it. Do you think adding it will have an effect?


Thanks,
Rob

 

[Simon_MT]

Try this:

<SCRIPT Language=VBScript RUNAT=SERVER>
Sub Application_OnStart

The file is held on an intranet: WebApplication is specific for each web site

    Application("my_conn") = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\inetpub\wwwroot\WebApplication\Data\Web.mdb"

End Sub

On each ASP page you need:

    Set conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionString = Application("my_conn")
    conn.open

The advantage of this approach is that the location of the database is declared once and referred to in each page.

Simon

 

[RSW]

Thanks Simon,

Unfortunately, that doesn't fix the problem (and truthfully the way it was set up before was not all that different from that). Remember, the page isn't having any trouble getting to that link itself. If all the data were at that link, it would work fine. It's the fact that that Access database has a link to another one on a different server that causes the problem.

 

[Simon_MT]

Is this an intranet? You need to explicitly access the database on the server or have the asp pages pages on the server with the database. You could possibly create an alternative connection but I would recommend having data and pages in the one place.

Simon