Microsoft Knowledge Base Email Alertz
All KB/RSS Links  |  Hot Kb!  |  New Kb!  |  Scott Cate  |  FAQ / KB  |  Web Hosting  |  Unsubscribe  

(209179) - 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. The System Objects check box in Microsoft Access...

Search KbAlertz

Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.
Click here to create a
FREE account
Already have an account?
[Click here to Login]











Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks
Article ID: 209179 - Last Review: January 26, 2005 - Revision: 2.1

ACC2000: SQL Server System Administrator Cannot See System Tables

View products that this article applies to.
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.
Back to the top

CAUSE

The System Objects check box in Microsoft Access is not selected.
Back to the top

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.
Back to the top

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.
Back to the top
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Back to the top
Keywords: 
kbprb kbusage KB209179
Back to the top
        

Community Feedback System

Very often, it takes hours to solve a problem. Very often, you've looked high and low, and have tried a lot of solutions. When you finally found it, chances are, it was because someone else helped you. Here's your chance to give back. Use our community feedback tool to let others know what worked for you and what didn't.

Please also understand that the community feedback system is not warranted to be correct, it's simply a system that we've built to let people try and help each other. If something in a feedback response doesn't make sense to you, or you're not comfortable making changes that the feedback talks about (like registry edits), please consult a professional.

Thank you for using kbAlertz.com Feedback System.

-- Scott Cate

Copyright © 2002-2007 KBALERTZ.COM.  All Rights Reserved. Terms / Privacy. Discount ASP.NET Hosting