Filter table list in MS-Access Link Table?

views:

762

answers:

Filter table list in MS-Access Link Table?

WinXP Pro Oracle 10g Instant Client 10.2.0.1 MS Access 2003

When I link a table in MS Access, the pick list that appears shows me every table and view in the system I have access to. This list is quite large. Normally, I really only want to look at the tables that I own. Is there a way to filter the items displayed based on owner, or any other criteria?

Not really.

You could work around it by getting another account set up that just has access to your tables and link against them that way. When you are pleased as punch with the links you have created, you can always update the links (link to a new location, pointing at your account perhaps) in the Link Table Manager.

CodeSlave 2008-12-15 20:24:49

Workarounds really aren't worth the time for me. It's just more of a PITA to scroll through all those tables. I was hoping for a filter feature that I had maybe missed, not being all that familiar with Access.

DCookie 2008-12-16 17:34:40

This is one place where consistent naming conventions can make it a lot easier to navigate the list. This is one reason a lot of Access developers use the tbl prefix for tables and the qry prefix for queries, so the two types of data sources get sorted together in the listbox.

David-W-Fenton 2008-12-19 02:48:34

@David-W-Fenton: I'm talking about the list of tables in my ODBC source (in this case, Oracle). I get every table and view listed that I have access to, including SYS objects (which is a hefty list). It's a pain to scroll through, and it takes a bit of time to populate as well.

DCookie 2010-06-23 21:12:02

Yes, and my point is that if the Oracle tables and views were named in a way that segregated them in a sorted list, it would be easier to manage. I use "tbl" for tables and "vbl" for views (kind of silly, I guess).

David-W-Fenton 2010-06-23 22:48:00

You could create a local table of the objects (tables and views) you 'own' in the distant database. You could then write some VBA code where, by looping on this table and using the doCmd.TransferDatabase method, you could automatically generate your links.

Philippe Grondier 2008-12-15 21:01:53

Thanks for the reply. See my comment in accepted answer.

DCookie 2008-12-16 17:37:19

Yes, you can set a SCHEMALIST parameter within your ODBC connection (under the advanced settings tab) and specify the table schemas you want to include in your table link list. This will then bring back only the tables you listed in the SCHEMALIST.

STACY 2010-06-23 18:32:46

Okay, I'm not a regular user of Access. I assume you're talking about some Advanced Settings tab within Access? How do I get to it? I've poked around a bit and don't see it.

DCookie 2010-06-23 21:09:47

This is suggestion to do it yourself. There is no way to change the behavior of the Access UI in this regard, only to implement naming conventions on the server that make it easier to navigate, or to implement security in a way that the list is filtered.

David-W-Fenton 2010-06-23 22:49:25

ansaurus

tags:

views:

answers:

Filter table list in MS-Access Link Table?

related questions