ACC2000: SQL Server System Administrator Cannot See System Tables (209179)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209179
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When a SQL Server System Administrator tries to import or link SQL Server tables to a Microsoft Access database, the administrator can see most of the SQL Server tables, but not the system tables.

CAUSE

The System Objects check box in Microsoft Access is not selected.

RESOLUTION

Before you try to import or create links to the SQL Server system objects, follow these steps:
  1. Start Access 2000, and then open the database where you intend to import or link the SQL Server tables.
  2. On the Tools menu, click Options.
  3. Click the View tab, click to select the System objects box, and then click OK.
  4. Begin to import or link the SQL Server tables, and note that the Import Objects or Link Tables dialog box now displays all the system tables within the SQL Server database.

MORE INFORMATION

In order for the SQL Server System Administrator (or any user who has logged in as sa) to see the SQL Server system tables from within Microsoft Access, the System Objects option must be selected in Access. The System Objects option is not enforced by default because most users do not need to see Access or SQL Server system tables.

NOTE:
The system tables for Microsoft Access begin with the prefix MSys. The system tables for SQL Server begin with the prefix dbo.sys. In either case, these system tables are undocumented and are subject to change in future versions of the Microsoft Access and SQL Server. Modifications to these system tables are not supported.

Steps to Reproduce the Behavior

  1. Start Microsoft Access 2000, and then open the database where you intend to import or link SQL Server tables.
  2. On the Tools menu, click Options.
  3. Click the View tab, click to clear the System objects check box, and then click OK.
  4. On the File menu, point to Get External Data, and then click Import or Link Tables.
  5. In the Files of type box, click ODBC Databases().
  6. Click the Machine Data Source tab, select the appropriate SQL Server data source name, and then click OK.
  7. Type the sa Login ID and password (if needed), select the appropriate SQL Server database (such as Pubs), and then click OK.

    NOTE: With over 20 system tables, a few of these tables may still appear to be visible even though the System Objects option is not selected. For example, dbo.sysalternates, dbo.sysconstraints, and dbo.syssegments may appear in the Import/Link dialog box. SQL Server actually stores these particular objects as views, not as system tables.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbprb kbusage KB209179
©2004 Microsoft Corporation. All rights reserved.