Many people in my office use Oracle tables by linking them in Access (either 2010 or 2016) databases.
They always complain about the fact that, when Access is about to present them the list of tables they have access to in a specific Oracle instance for selection, they have to wait 10 to 15 minutes for the selection window to pop-up.
The reason seems to be the huge number of accessible tables, in turn related to the huge number of accessible schemas, on that particular instance, so that the resulting list contains tens of thousands of table names.
There was a similar problem with some DB2 databases, but was solved by adding a 'SCHEMALIST' option in the ODBC configuration (db2cli.ini) for those databases, to limit the number of schemas visible to the connection.
I had a look at Oracle ODBC documentation, but couldn't find a similar option, so I'm stuck.
Does somebody have a trick to share about how to filter schemas/tables visible to an ODBC connection to Oracle or otherwise speed-up linking those tables?
Best Answer
What if you create a special Oracle user that Access would use to connect? That user would only have the ability to access the tables that are relevant, plus it would have a synonym for every table it had access. Then when you connect from Access to Oracle, Access would only populate the table list with the tables that you want to see. I'm going to guess that you have at least tens of thousands of tables in your schema if not hundreds of thousands of tables. Are you running Peoplesoft?
You might want to find other tools to work with besides Access. There must be other BI tools that you can work with. Possibly even a few open source tools.