I have an Oracle 10g R2 database where my ERP transactions reside. I'm using MS Access to grant access to my power users where I use MS Access' Link Tables to provide direct access.
I noticed that not all tables in all schema in the database is retrieved by MS Access even if SYSTEM is used to login. It looks like there's an upper limit to the number of tables that's retrieved by MS Access. We need to access all tables in te database. This was not the case in previous versions of MS Access. I cannot be certain which MS Access versions.
In the connection attempts I did, I used Microsoft ODBC for Oracle (2.575.1117.00) and Oracle driver (10.02.00.01) delivered by the Oracle 10g R2 client. Both had the same outcome which is what I described above.
Has anyone encountered this problem before?
17  20786  MMcCarthy 14,534
Recognized Expert Moderator MVP
I have an Oracle 10g R2 database where my ERP transactions reside. I'm using MS Access to grant access to my power users where I use MS Access' Link Tables to provide direct access.
I noticed that not all tables in all schema in the database is retrieved by MS Access even if SYSTEM is used to login. It looks like there's an upper limit to the number of tables that's retrieved by MS Access. We need to access all tables in te database. This was not the case in previous versions of MS Access. I cannot be certain which MS Access versions.
In the connection attempts I did, I used Microsoft ODBC for Oracle (2.575.1117.00) and Oracle driver (10.02.00.01) delivered by the Oracle 10g R2 client. Both had the same outcome which is what I described above.
Has anyone encountered this problem before?
This is a new one on me. Are you manually linking the tables using the Get External data option? Are all the tables visible in the selection list?
This is a new one on me. Are you manually linking the tables using the Get External data option? Are all the tables visible in the selection list?
I'm manually linking that tables in Oracle using MS Access' Link Table option. Power users normally use MS Access to update system configuration without the need to issue SQL statements.
When MS Access connects to Oracle, the list of tables retrieved is not completed only 1/3 of all tables perhaps. Previously, all tables were listed after MS Access makes successful connection to Oracle.
MMcCarthy 14,534
Recognized Expert Moderator MVP
I'm manually linking that tables in Oracle using MS Access' Link Table option. Power users normally use MS Access to update system configuration without the need to issue SQL statements.
When MS Access connects to Oracle, the list of tables retrieved is not completed only 1/3 of all tables perhaps. Previously, all tables were listed after MS Access makes successful connection to Oracle.
I'd guess you're problem is with Oracle then and the permissions on the tables. Access wouldn't limit the list even if it fell over when you try to add too many tables. Compare the permissions on the tables in the list against those not appearing in the list. See if any other security permissions have been added to Oracle which might be causing this.
If that doesn't help try recreating the DSN and see if that helps
I'd guess you're problem is with Oracle then and the permissions on the tables. Access wouldn't limit the list even if it fell over when you try to add too many tables. Compare the permissions on the tables in the list against those not appearing in the list. See if any other security permissions have been added to Oracle which might be causing this.
If that doesn't help try recreating the DSN and see if that helps
Currently, PUBLIC has full access to all tables in the database and therefore I don't think it has something to do with permissions. No changes were done in the database that could potentially affect tables retrieved my MS Access' Link Tables.
Oracle client and Oracle database server versions are the same so that eliminates the possibility of incompatibility .
DSN has been recreated a number of times already but still the same result.
MMcCarthy 14,534
Recognized Expert Moderator MVP
I'll put a call out to the other experts to see if anyone has any suggestions.
NeoPa 32,568
Recognized Expert Moderator MVP
Everything I would have thought of has been said already by Mary I'm afraid.
I use ODBC a fair bit but not connecting to an Oracle database. I've never noticed any limit on the number of tables that are able to be seen in the available tables list within 'Linked Tables'.
Sorry I can't be more (any :() help.
pks00 280
Recognized Expert Contributor
Ive never come across this problem and the database I worked with had loads of tables.
Who is the owner of the schema, do u see it in that format ? owner.tablename
Also it might be worth trying the microsoft driver instead of the Oracle one - thats the ODBC driver Im talking about.
See if that makes any difference.
Create a different DSN, one using Microsoft driver but link to the same TNS Names entry
Denburt 1,356
Recognized Expert Top Contributor
Consider trying the MS version of the ODBC driver I have had issues using both Oracle and MS versions of the Oracle ODBC driver. I think that lately most of my apps are using the MS driver and I am having a lot of success,
 ADezii 8,834
Recognized Expert Expert
I have an Oracle 10g R2 database where my ERP transactions reside. I'm using MS Access to grant access to my power users where I use MS Access' Link Tables to provide direct access.
I noticed that not all tables in all schema in the database is retrieved by MS Access even if SYSTEM is used to login. It looks like there's an upper limit to the number of tables that's retrieved by MS Access. We need to access all tables in te database. This was not the case in previous versions of MS Access. I cannot be certain which MS Access versions.
In the connection attempts I did, I used Microsoft ODBC for Oracle (2.575.1117.00) and Oracle driver (10.02.00.01) delivered by the Oracle 10g R2 client. Both had the same outcome which is what I described above.
Has anyone encountered this problem before?
Never experienced this type of problem but it could be a restriction on the Linked Table Manager, something similiar to the Switchboard Manager where you can only place a maximum of 8 items on a Switchboard but by modifying the Switchboard Items Table directly, you can add as many items as you want. Have you tried to Link the Oracle Tables programmaticall y? It's a long shot but maybe one worth chancing.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Phil S |
last post by:
I used the Migration Workbench for Oracle 9.2 Client to migrate the
tables in an Access 2K back-end database to Oracle. (The Migration
Workbench is intended to automate the process of migrating tables from
Access to Oracle.) Following the migration, when I try to link the
Oracle table to the Access front-end using Oracle ODBC Driver, I have
to either select one or more fields in the Oracle table as key fields
or not have any key fields. ...
|
by: G rumpy O ld D uffer |
last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.
I've been given 30+ (and counting) separate 'Weekly' Databases which
all have a Table, in exactly the same Field format, in each of these
'Weekly' DataBases.
I want to set-up a new 'Master' Database, so that I can write one
Query to search all the 'Weekly' Databases and produce one new Table
in the 'Master' Database...
|
by: dave71 |
last post by:
Hi
Could someone please advise me how to remove the schema name from
linked tables within Access. For example when I connect to a Oracle
database via Microsoft ODCB for Oracle the list of tables shown are of
the nature username.tablename I would like this only to display the
table name I have this arrangement on another PC here but I am not
sure how it was configured.
Thanks
|
by: anand |
last post by:
Hi,
I have an Access 2000 database, which contains some native tables,
and some linked tables which belong to an ORACLE database, through
ODBC.
Using VB.NET, I am trying to fetch some data by joining two tables,
one table native to the mdb file, and another belonging to ORACLE.
I am using a connection string to connect to mdb file:
|
by: Alfonso Esteban Gonzalez Sencion |
last post by:
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.
When I consult the linked table in access, the total number of records
is OK but some records appear several times and some records do not
appear at all. It seems as if access or the ODBC drivers returns several
times the same record and skips some of the records, curiosly...
| | |
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For tables that involve a number
field as the primary key, the data is returned successfully. For tables
that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary
key, I have the correct number of rows returned, but the data displayed...
|
by: Randy Harris |
last post by:
I have been using "IN" to query tables in Oracle from MS Access very
successfully.
Select Field FROM MyTable IN [ODBC...etc
Works great if there is only one table involved. Anyone know how I can use
the same technique with multiple tables (and be certain that the join occurs
at the server).
--
|
by: RLN |
last post by:
I have a Microsoft Access2002 database that needs to connect to an
Oracle Database. I need to map 2 tables from the Oracle DB to retrieve
the proper data.
I read somewhere (quite a while back) that if you use ADO in Access, you
can connect without the need for ODBC, TNSNAMES.ORA and the Oracle
client installed on the user's desktop.
If I use the Microsoft driver for Oracle, is it possible to connect to
an Oracle DB using straight VBA
|
by: Jim Devenish |
last post by:
I have a split front end/back end system. However I create a number of
local tables to carry out certain operations. There is a tendency for
the front end to bloat so I have set 'compact on close'
I think that I have read in some threads (althoug I cannot find them
now) that others place such tables in a local, linked database. I
could do this but I am interested to know what would be the advantages.
And disadvantages, if any.
Any...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
