Tables retrieved by MS Access Link Tables using Oracle database

[aquablade]

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?

[MMcCarthy]

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?

[aquablade]

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]

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

[aquablade]

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]

I'll put a call out to the other experts to see if anyone has any suggestions.

[NeoPa]

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]

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]

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]

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 programmatically? It's a long shot but maybe one worth chancing.

[nico5038]

I experienced this problem when linking/accessing a table with >250 fields.
Access has a max of 250 fields (sometimes with long fieldnames even less) and the solution I used was to create views with 100 fields and linked to these instead.

Nic;o)

[college researcher]

I’m a college institutional researcher and have this problem with Access ODBC links to PeopleSoft Finance, and now our Student/HR 8.9 “upgrade” – both on SQL Server. I’ve read that the problem is that Access has a limit of 32,768 objects (tables, views, etc.). PeopleSoft's table count, unfortunately, exceeds that limit.

I found the potential solutions below in Microsoft.com/Office/Community Access discussions. My DBA had already proposed #1 (the last sentence portion), but as a researcher I need to see all tables, sample/test, & select those I need for a specific research project, so I’m hoping #2 or #3 might work. I’ve had similar problems using Access Project (.adp), and pass-through queries don't provide the functionality I need. So please share if you find these/others to work.

POST #1: get external data odbc limitation? “ . . . . I'd start by replacing the employee who designed a relational database with 50,000 tables with a trained DBA. . . . . In the short term, create a role that has permissions limited to only the tables needed, create a new user and assign it to that role, then connect to SQL Server with that User ID.”

POST #2: Problem with link dialog list >= 35000 entries. “ . . . . I found a workaround for my Oracle / ODBC Problem by altering the following value in the registry: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\ODBC] "AttachableObjects"="'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'" In my case, excluding 'SYNONYM' from the attachable objects reduced the list in the link dialog to < 32768.”

POST #3: Set up an ODBC connection Programmatically? “ . . . . It's also possible, though, to go DSN-less. Doug Steele has an example at http://www.accessmvp.com/djsteele/DSNLessLinks.html . . . . . Did you look at Carl's site - http://www.carlprothman.net/Default.aspx?tabid=90 - to get the correct DSNless connection string for Oracle?”

[MMcCarthy]

I'd start by replacing the employee who designed a relational database with 50,000 tables with a trained DBA

I agree whole heartedly with this statement. I'm not familiar with Peoplesoft but I can't think of any database system that should legitimately exceed 32,768 objects.

By any chance do a number of these objects have the same schema. Are they essentially the same tables but with different data. If this is the case I would design some VBA to retrieve the data in each of these tables and append them to one new table in Access. Although the data wouldn't be live you could trigger a refresh of the data as required.

[slicendicecube]

I had the same problem linking to Oracle tables in PeopleSoft Student Finanacials. Removing 'SYNONYM' from the registery worked for me.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\ODBC] "AttachableObjects"="'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'"

[slicendicecube]

DB=Oracle;PeopleSoft

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\ODBC]
AttachableObjects
Value: 'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'

Removing 'SYNONYM' previously allowed me to see all of the objects with Access 2003. With my recent upgrade to 2007 it no longer works.

I tried removing everything except 'TABLE' in the registry in hopes of reducing the number of objects to no avail. It doesn't make sense that there is a limit in the number of objects shown in the dialog box. I can understand a limit to the number of objects with Access. I guess I'm going to have to hang up Access.

[ammahen]

Hi.. Aquablade,

Did you got a solution for your problem?,, If you got it means please post it bacause ...I too have a same problem like you my Oracle DB is 10.2.0.4 my MS Access version 2007.

[accessfail]

There are 2 solutions one is SP2 for office, if you install that it should solve your issue, the other is basically almost the same as the 2003 fix except you need to edit this path
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\ Access Connectivity Engine\Engines\ODBC]

apparently ACCESS 2007 no longer uses the JET engine.

Good Luck!

[NeoPa]

Not the Jet engine as such, but the ACE is supposed to be the new version of the Jet Engine. It is simply not using the standard (basic) that comes with Windows any more.

That said, thanks for posting. That's helpful information to be sure.

Welcome to Bytes!