Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem doesn't make sense 'cannot find the input table or query....

Status
Not open for further replies.
dharkangel

dharkangel

MIS
Sep 25, 2002
159
US
Hi,

I have a simple ASP page running on an Windows XP professional box, with IIS 5.1, with MS Access 2003. I keep getting this error:

Microsoft JET Database Engine (0x80040E37)
The Microsoft Jet database engine cannot find the input table or query 'Barcelona Internal Actions: All Issues'. Make sure it exists and that its name is spelled correctly.
/metrics/metrics.asp, line 15

This doesn't make any sense because as you'll see from my code there is no reference to 'Barcelona Internal Actions: All Issues'. The table 'BarcelonaInternalActions' is a linked table that used to be called 'Barcelona Internal Actions: All Issues'.

Below is my code:

<%
'Create database connection the object
set actionsDB = Server.CreateObject("ADODB.Connection")
'Open the connection

actionsDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/Inetpub/

set actionSet = Server.CreateObject("ADODB.RecordSet")
mySQL = "Select * from BarcelonaInternalActions"

with actionSet
.ActiveConnection = actionsDB
.Source = mySQL
.Open \\this is where the error is happening
End with



%>



<html>
<head><title>test</title></head>
<body>
this is a test new2
</body>
</html>


<%
actionSet.Close
set actionSet = Nothing
'Close the connection
actionsDB.Close
'Destroy the connection
set actionsDB = Nothing
%>
 
Sort by date Sort by votes
Why are you opening "c:/Inetpub/ rather than the mdb the table is linked from? If you must reference the linked table, try delete the link and then re-link.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Upvote 0 Downvote
Yes, I have to link the tables because the data in the linked tables is coming from MS Sharepoint, which I have no access to the database, not even view access. I will try your suggestion...Is this a known problem with using linked tables?
 
Upvote 0 Downvote
I imported the tables and the problem went away, the only consequence, I need this data in real time. Is there any way to at least perform an automatic import of a table? I could maybe schedule the import to happen at a certain day of the week so I can perform the reports I need to against the data.
 
Upvote 0 Downvote
I didn't expect you to import the table. I thought you would just delete and relink. Make sure you don't rename the linked table and then use the newly relinked table name in your asp page. You might also trouble shoot this with a query in Access like:

SELECT msysObjects.Connect, msysObjects.Database, msysObjects.ForeignName, msysObjects.Name, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "msys*") AND ((msysObjects.Type) In (1,6,8)));


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Upvote 0 Downvote
Your problem is probably happening because a linked table has a hard-coded reference to the path and table name for the source database. If you relocate the database or rename the table then the link reference needs to be updated to the correct one. Here's a little routine that you can call to link or relink tables.
Code: 
Sub RefreshLinks(DestTable As String, SourceTable As String, SourceDB As String)
   
   Dim cat As New ADOX.Catalog
   Dim tbl As ADOX.Table
   
   cat.ActiveConnection = CurrentProject.CurrentConnection
   
   On Error Resume Next
   Set tbl = cat.tables(DestTableName)
   
   If Err.Number = 0 Then
      [COLOR=black cyan]' TABLE EXISTS ... Refresh the link.[/color]
      If tbl.Type = "LINK" Then
         tbl.Properties("Jet OLEDB:Create Link") = False
         tbl.Properties("Jet OLEDB:Link Datasource") = SourceDB
         tbl.Properties("Jet OLEDB:Remote Table Name") = SourceTable
         tbl.Properties("Jet OLEDB:Create Link") = True
      End If
   Else
      [COLOR=black cyan]' TABLE DOES NOT EXIST ... Create a new link.[/color]
      Set tbl = New ADOX.Table
      tbl.Name = DestTable
      Set tbl.ParentCatalog = cat
      tbl.Properties("Jet OLEDB:Create Link") = True
      tbl.Properties("Jet OLEDB:Link Datasource") = SourceDB
      tbl.Properties("Jet OLEDB:Remote Table Name") = SourceTable 
      cat.Tables.Append tbl
   End If
  
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

raywood1
  • Locked
  • Question
Reducing a Massive Linked Table
Replies
4
Views
442
dhookom
dhookom
JayRo71
  • Locked
  • Question
Saved Imoorts problem with file name
Replies
10
Views
485
Andrzejek
Andrzejek
v5652
  • Locked
  • Question
Trouble opening database Make sure the drive is available
Replies
2
Views
401
v5652
v5652
03Explorer
  • Locked
  • Question
Refreshing a linked table (housed on MS SQL Server) now breaks
Replies
3
Views
346
dhookom
dhookom
JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
536
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top