Tables missing when attempting to link SQL 2000 tables

  • Thread starter Thread starter Will Feaser
  • Start date Start date
W

Will Feaser

I am using an ODBC datasource to connect Access 2000 to a
SQL Server 2000 database. When the "Link Tables" box
appears, it displays only one table. The database
contains 19087 tables (it is a PeopleSoft database), and I
have dbo rights on the database. Thinking that I was
timing out, I increased the ODBC timeout to 600 seconds,
but that did not help. The "Link Tables" box appears
after about 30 seconds. Any idea why I can not see all of
the tables?

Using an identical ODBC datasource, I can connect to
another database on the same server and see all of the
tables in that database.

The problem is occurring on several of our "developer"
PCs. The PCs are running Windows 2000 or Windows XP.
Conversely, there are several PCs on which this works.
 
Would it not be better to use the SQL Server driver rather than ODBC? Access
should have a specific driver for SQL Server. This may give you better
connectivity and ODBC.
 
Hi,
You could be using Windows NT authentication with your
ODBC connection (DSN). If a user on a certain machine has
no rights to the database or limited rights, then they
won't see some or all of the tables.
Hope it helps.

Khalid
 
How is the DSN configured?
SQL Server or windows authentication?

Are you using Named Pipes or TCP/IP?

Did you use the dbo credentials in the DSN?

I have never connected to a database with 19,000 tables.
Not sure if there is some upper limit.
But only getting 1 table sounds like a credentials problem.
 
I found the problem. The list box that displays the
objects that can be linked has a limit of 64,000
characters. In my case, the database contains 19,000
tables and 15,000 views, so I quickly exceeded that
limit. I resolved the problem by creating a role in SQL
Server that has access to a limited set of tables \
views. I assigned a user id to the role and then used the
user id in my DSN. If you are using Oracle, you might be
able to correct the problem by removing the Synonym option
in you ODBC registry entry (see MS Knowledge Base article).
 
Back
Top