I have an MS Access database which is already linked with many tables in Oracle. I want to add few more tables to that from different server of Oracle.
The problem is if I connect to the server through MS Access and try to import using linked table manager I could not find my schema or user in that list of tables.
This Link tables dialog even from Get External data->Link Tables gives me only system tables like (CTXSYS,DMSYS,XDB etc....).
Why can I not find any of my tables? Do I not have access to this?
How do I achieve this? Why are my table schema's not listed even if I connect to the right server?
I don't recall how the Linked Table Manager worked in Access 2000, so I may be off base here. However, in Access 2003, the Linked Table Manager allows me to change the connections for existing table links, but doesn't provide an option to create new links. (at least not an option I can find)
IOW, if the same source table existed on a different server, I could use the Linked Table Manager to change an existing link for that table to point to the new server. But, with no existing link to the source table, I can't use the Linked Table Manager to create one.
For that purpose, I go to Access' main menu and choose File -> Get External Data -> Link Tables, choose "ODBC Databases()" from the "Files of Type" box on the Link dialog, then choose the DSN for the server which houses the tables I want linked. IIRC, you should have a similar option with Access 2000.
You could also use VBA code to create your links. See TransferDatabase Method.