Access Table name (Linked vs. local)

[themikehyde]

Hi,
I have an Access 2000 database that contains both local (tblTableName1) and linked (SERVER1_TableName2) tables. Using .ASP I can access the local tables fine, but when I try to access the linked tables, it displays the following:

The Microsoft Jet database engine cannot find the input table or query 'TableName2'. Make sure it exists and that its name is spelled correctly.


I have tried using both SERVER1_TableName2.fieldname and TableName2.fieldname, and same result.

Thanks,
Mike

 

[Sheco]

Hmm...

Are linked tables part of the Access database or are they a feature of the Access application?

I never thought of that... might be a question for the Access gurus: forum700

Why don't you just connect directly to the linked table from your ASP ?

 

[themikehyde]

Sheco,
They are part of the Access DB. I have an Access DB that has linked tables to an IBM Server, and an Oracle DB. Locally in Access the query runs fine. I need to run this from the Web page. I didn't really want to have a SQL that queries two different ODBC connections if I could help it.

Thanks,
Mike

 

[Sheco]

Well I assumed you tried changing the name to something short and simple... like "foo

 

[themikehyde]

Sheco,
Yes I have. I have tried accessing BLLIB400_foo & foo. Always the same result, cannot find table.
Mike

 

[Sheco]

I duplicated your problem.

The issue that I see is that the linked tables need a password to connect.

hmmm..

 

[Sheco]

Well the problem is not just passwords.

I added a third linked table, this one to SQL Server. I configured the DSN to use a specific username and password so that Access never prompts for these things.

Same result.

 

[Sheco]

Converted it to a .VBS and it worked no problem.

 

[Sheco]

Turned off anon access in IIS Administrator, accessed the page as domain admin... didn't help.

The error I get looks like this:
Microsoft JET Database Engine error '80004005'

ODBC--connection to 'MyMSDE' failed.

/testaccess.asp, line 19


Is that what you see?

 

[themikehyde]

Sheco,
Sorry, I'm a little confused. What did you convert to a .vbs?

 

[Sheco]

I converted my ASP page to a VBS and it works as a plain VBScript file.

I thought the difference might be the access level so that is why i tried turning off anon in IIS.

 

[Sheco]

Here is my ASP version, it only works for the local access table, not for any of the linked tables.

<%
Dim ocn, ors, fld
Set ocn = Server.CreateObject("ADODB.Connection")
ocn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\[URL unfurl="true"]wwwroot\test.mdb;"[/URL] 
ocn.Open 

Response.Write "<BR><BR>SELECT * FROM AccessTbl WHERE 1 = 2<BR>"
Set ors = ocn.Execute("SELECT * FROM AccessTbl WHERE 1 = 2")
if ors.state <> 1 then
	Response.Write "ADO error!"
	Response.End 
end if
For each fld in ors.fields
  Response.Write fld.name & "<BR>"
Next
ors.close

Response.Write "<BR><BR>SELECT * FROM SQLSVRTbl WHERE 1 = 2<BR>"
Set ors = ocn.Execute("SELECT * FROM SQLSVRTbl WHERE 1 = 2")
if ors.state <> 1 then
	Response.Write "ADO error!"
	Response.End 
end if
For each fld in ors.fields
  Response.Write fld.name & "<BR>"
Next
ors.close

Response.Write "<BR><BR>SELECT * FROM DB2Tbl WHERE 1 = 2<BR>"
Set ors = ocn.Execute("SELECT * FROM DB2Tbl WHERE 1 = 2")
if ors.state <> 1 then
	Response.Write "ADO error!"
	Response.End 
end if
For each fld in ors.fields
  Response.Write fld.name & "<BR>"
Next
ors.close

Response.Write "<BR><BR>SELECT * FROM OracleTbl WHERE 1 = 2<BR>"
Set ors = ocn.Execute("SELECT * FROM OracleTbl WHERE 1 = 2")
if ors.state <> 1 then
	Response.Write "ADO error!"
	Response.End 
end if
For each fld in ors.fields
  Response.Write fld.name & "<BR>"
Next
ors.close



set ors = nothing
ocn.Close 
set ocn = nothing
%>

Here is my .VBS. It works for all tables, including the linked one. It does show an ODBC popup password box for the Oracle table but it works.

call foo

Sub foo()
Dim ocn, ors, fld
Set ocn = CreateObject("ADODB.Connection")
ocn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\[URL unfurl="true"]wwwroot\test.mdb;"[/URL] 
ocn.Open 

MsgBox "SELECT * FROM AccessTbl WHERE 1 = 2"
Set ors = ocn.Execute("SELECT * FROM AccessTbl WHERE 1 = 2")
if ors.state <> 1 then
	MsgBox "ADO error!"
	exit sub
end if
For each fld in ors.fields
  MsgBox fld.name 
Next
ors.close

MsgBox "SELECT * FROM SQLSVRTbl WHERE 1 = 2"
Set ors = ocn.Execute("SELECT * FROM SQLSVRTbl WHERE 1 = 2")
if ors.state <> 1 then
	MsgBox "ADO error!"
	exit sub
end if
For each fld in ors.fields
  MsgBox fld.name 
Next
ors.close

MsgBox "SELECT * FROM DB2Tbl WHERE 1 = 2"
Set ors = ocn.Execute("SELECT * FROM DB2Tbl WHERE 1 = 2")
if ors.state <> 1 then
	MsgBox "ADO error!"
	exit sub
end if
For each fld in ors.fields
  MsgBox fld.name 
Next
ors.close

MsgBox "SELECT * FROM OracleTbl WHERE 1 = 2"
Set ors = ocn.Execute("SELECT * FROM OracleTbl WHERE 1 = 2")
if ors.state <> 1 then
	MsgBox "ADO error!"
	exit sub
end if
For each fld in ors.fields
  MsgBox fld.name
Next
ors.close



set ors = nothing
ocn.Close 
set ocn = nothing
End Sub

I don't know what the problem is yet but I'm pretty sure it isnt the code because, as you can see, there is hardly any differece between the ASP version and the VBS version... only differences are:
Server.CreateObject changed to CreateObject
Response.Write changed to MsgBox
Response.End changed to Exit Sub
<BR> elements were removed

 

[Sheco]

I modified my code to make sure that I was getting the table names correct. I was.

But you might try this as a test since it is complaining about table names for you...

Dim ocn, ors, fld
Set ocn = Server.CreateObject("ADODB.Connection")
ocn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\[URL unfurl="true"]wwwroot\test.mdb;"[/URL] 
ocn.Open 

Response.Write "Set ors = ocn.OpenSchema(adSchemaTables)"
Set ors = ocn.OpenSchema(20)
if ors.state <> 1 then
	Response.Write "ADO error!"
	Response.End 
end if
Do While Not ors.Eof
	Response.Write ors("TABLE_NAME") & "<BR>"
  ors.MoveNext
Loop

 

[themikehyde]

The table "foo" shows up.

 

[Sheco]

I saw this post you made in another thread... was it supposed to be in this one?

Sheco, No it says the table does not exits.

Error Type:
Microsoft JET Database Engine (0x80040E37)
The Microsoft Jet database engine cannot find the input table or query 'blploct'. Make sure it exists and that its name is spelled correctly.

 

[themikehyde]

Sheco,

When I add this:

strSQL = "select * from foo"
set rsData = ocn.execute(strSQL)

Do While not rsData.EOF
rsData.movenext
Loop

I get:
Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80004005)
ODBC--connection to 'bllib400' failed.
/twacs/oracle.asp, line 20

 

[Sheco]

If so does "blploct" show or only "foo" ?

 

[Sheco]

Ah, ok that is the same error that I get.

 

[Sheco]

I punched the error code into google and got this:

http://www.sqlmonster.com/Uwe/Forum...ect-ODBC-SQLSrvr-linked-table-from-within-COM

The answer is a little cryptic...

 

[Sheco]

I tried using a plain ODBC driver instead of the OLEDB version.

The connection string I used was:
ocn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:C:\Inetpub\

http://wwwroot\test.mdb;"

The error message was at least as nasty:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x590 Thread 0xa54 DBC 0x11ad024 Jet'.