ACC95: Error Using Identity Column with Data Outline Control

• Microsoft Access 7.0
• Microsoft SQL Server 6.0, 6.5

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use the Data Outline Control 1.1 to view data from a Microsoft SQL Server database, you receive the following error:

   One of the specified RecordSources is invalid.

CAUSE

The RecordSource property for one or more levels of the Data Outline Control contains an Identity column from the SQL Server table. An Identity column is similar to an AutoNumber field in Microsoft Access.

RESOLUTION

There are two methods to work around this problem. You can change the RecordSource property so it does not include the Identity column, or you can base the RecordSource on a SQL pass-through query.

Method 1: Change the RecordSource

Change the RecordSource property in the Data Outline Control to eliminate the Identity column; you must change the RecordSource property for any level in the Data Outline control that contains an Identity column.

For example, if you only need to see the job_desc, min_lvl, and max_lvl fields from the Jobs table in the Pubs sample database, set the RecordSource property to:

   SELECT job_desc, min_lvl, max_lvl FROM dbo_jobs

Method 2: Use a SQL Pass-Through Query

Change the RecordSource property so it uses a SQL pass-through query. This allows you to use the Identity column in your Data Outline Control. Be sure to include all the fields you need for your Data Outline Control level in the SQL pass-through query.

For more information about creating and using SQL pass-through queries, search the Help Index for "pass-through queries."

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

NOTE: The error also occurs with the Data Outline Control 1.2 for Microsoft Access 97. That version of the control is provided by Microsoft as-is, and is not supported by Microsoft Technical Support. However, the resolution in this article also works with the Data Outline Control 1.2. For more information about obtaining the Data Outline Control 1.2, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q162359
   TITLE     : ACC97: Access 97 Data Outline ActiveX Control Available on
               MSL

Steps to Reproduce Problem

NOTE: These steps assume you have access to the Pubs database included with Microsoft SQL Server.

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. On the File menu, point to Get External Data, and then click Link Tables.

3. In the Link dialog box, select ODBC Databases() in "Files of type."

4. In "Select Data Source", select an SQL Server data source, and then click OK.

5. Type your Login ID and Password in "SQL Server Login", and then click Options.

6. Select (or type) Pubs in the Database box, and then click OK.

7. Select the dbo.jobs table in Link Tables, and then click OK.

8. Create a new, unbound form in Design view.

9. On the Insert menu, click Custom Control.

10. In "Insert OLE Custom Controls", select Data Outline Control 1.1, and then click OK.

11. On the Edit menu, point to Data Outline Control 1.1 Object, and then click Properties.

12. Click the Level One tab, and then type "SELECT * FROM dbo_jobs" (without the quotation marks) in the RecordSource property.

13. Type "job_desc;min_lvl;max_lvl" (without the quotation marks) in the DisplayFields property, and then click OK.

14. Switch the form to Form view, and note that you receive the error message.