Can't see all tables in a linked ODBC database Access 2007

  • Thread starter Thread starter brownshel
  • Start date Start date
B

brownshel

We recently updated to Office 2007. I have existing databases which seemed
to move from 2003 to 2007 just fine, but now I cannot see the complete list
of tables when I want to attach another from a linked ODBC database. Can
anyone tell me how to stop the list from limiting? (There are hundreds of
tables in the ODBC database in question; the ones I want are toward the end
of the alphabetical list.) Can anyone help? Thank you!
 
Just figured it out. Access 2007 looks at a different file in our REGEDIT
than older versions used. If you have the same problem, try this.

Go to START: RUN>REGEDIT and navigate to
HKEY_LOCAL_MACHINE>SOFTWARE>Microsoft>Office>12.0>ODBC
Right-click on "attachableobjects" and remove 'ALIAS' and 'SYNONYMS' from
the string. Shut down and restart.
 
Holy mackerel! You're advising people to cut off their noses to spite their
faces! Please don't give technical advice unless you know what you're
talking about.

First, Access 2007 doesn't "look at a different file in your regedit than
older versions used." Regedit is the Windows Registry editor, and there's
only ONE Windows Registry per operating system. Access 2007 uses THE
Windows Registry on whatever computer it's booted on. There aren't other
choices here, even if you have multiple versions of Office installed. All
versions of Office (and Access) use the same Windows Registry on the
operating system they're booted on.

Second, a synonym is used as an alternative name of an object a user has
permissions to use. For example, in Oracle you can replace two-part names
with synonyms:

CREATE TABLE SchemaName.ObnoxiouslyLongNamedTable (
data VARCHAR2(20));

CREATE OR REPLACE SYNONYM SimpleName FOR
SchemaName.ObnoxiouslyLongNamedTable;

Therefore,

SELECT data FROM SimpleName;

is equivalent to

SELECT data FROM SchemaName.ObnoxiouslyLongNamedTable;

In SQL Server 2005 and later, you can use synonyms to replace two-part,
three-part, or four-part names. Here's an example of a four-part name (a
fully-qualified name of server, database, schema, and object) being replaced
with a shorter synonym:

CREATE SYNONYM ProdCat FOR Orion.AdventureWorks.Production.ProductCategory;

Why is this important in Access? The ODBC linked table list only allows up
to 64K characters, regardless of how many tables, views and synonyms you
have permission to read in the database. That's it. You have no way to
increase this. By getting rid of the listed synonyms in the Registry key,
you kept the all the SchemaName.ObnoxiouslyLongNamedTables and got rid of
the SimpleNames that were far, far fewer characters. And you're advising
others to do it too!

64K characters should be enough to list thousands of tables, views and
synonyms. If you only have hundreds of linkable objects, say 500 tables,
views and synonyms, and you still can't see the end of the list, it means
you've got an average of more than 131 characters per object name, and you
REALLY, REALLY need to use the shorter synonyms instead.

And unless you're an Oracle or SQL Server DBA doing database administrative
work on the database server from Access, you don't need to link to the
system tables, do you? You kept those but you dumped the synonyms? Sheesh.
 
Back
Top