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

 

[themikehyde]

I am getting a little confused on this one. It will let me show the tables, but not access them? I am gathering there is now orkaround for this. Should I try using .vbs?

 

[Sheco]

I made a proper DSN using the ODBC version instead of JET...

Connection string was:
"DSN=DeleteFoo;DBQ=C:\Inetpub\

http://wwwroot\test.mdb;DriverId=25;FIL=MS

Access;"

Error same as the orignal one:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'MyMSDE' failed.



All I can say at this point is maybe check one of the MS Access forums on this site and see if any of the Access gurus know the answer.

 

[Sheco]

It seems like its gotta be some kind of permissions thing.

We see it works as VBS but not as ASP. That link I found on google says it works as a VB EXE but not as a VB ActiveX DLL.

There is something definately funky going on but I can't put my finger on it. Maybe it will dawn on me during lunch.

 

[bslintx]

sheco,

i had the same problem on my intranet..and was in a prior post......works locally but once you go over the intranet it didn't work...i checked the path under the link table proerties and the path is correct but the problem was 2 connections are involved...1 to the linked table and 1 to the local server. steven had some insight on this but not to fix the link problem. if you fix this one then a star from me...because for the lfe of me think it's weird how it works locally but not across a network...either way there are 2 dfferent connections so it makes no since?? so anxiously awaiting for the fix!!! wlll check up on ths one.

thanks
Brian

 

[steven290]

is this the issue with use the ip of the machine ex.

machine with asp file - 192.168.0.5
my machine - 192.168.0.8

machine with asp files virtual directory - test

on my machine type

http://192.168.0.5/test/theaspfile.asp

 

[steven290]

as i told sheco i dod this all time in my company while testing for browser/platform compatibilty

 

[bslintx]

steven...ths is the one we went offlne with...i went the other route as we discussed and didn't link the tables and worked fine; however, i am way curious as to why the local machine database CAN an WILL LINK to an external server source db table. You can open up the local machine and see the lnked table...open it up and see the data being linked. But under linked manager properties it has the address to the networked server...makes since but when trying to open locally with a local connection it won't work because the connection is local yet the linked table has a path that's different then the one you are connected to, hence why cannot find query/table...there are literally 2 connections. why access allows for a linked table accross the network but cannot connect through a local connection is over my head...i'm sure it'a s imple fix...but too damn simple perhaps that it's overlooked! Hope to get the answer

 

[themikehyde]

Thanks guys, I am glad its not just me. I am using this on our Intranet also. We have an IBM iSeries that contains all of our billing and customer data. We also have a Windows 2K server with an Oracle DB. I have an Access database that can run a local query joining tables from both systems, works great. For usability, I am trying to run these queries from the Intranet, rather than the users having to open the db and run the queries. In the interim, I have a scheduled job, opening Access and running a query to make the table, giving it a tblTableName (as opposed to SERVERDB_tblTableName) and then pulling from the local table. The downside is having duplicates of the mainframe tables in Access, and havingto make sure they are updated before users run the Intranet Reports. Its not perfect, but its the only thing I could come up with. I figured this was easier than opening 2 datasets and tryingto join them in .asp.

Please let me know if you ever come up with somthing.

Thanks,
Mike