Microsoft KB Archive/131586

= ACC2: ODBC Specification Error Attaching to SQL Server View =

Article ID: 131586

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q131586



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



SYMPTOMS
When you attach to a view in a Microsoft SQL Server database created from a table that resides in a different Microsoft SQL Server database and contains a field with a user-defined data type, you receive the following error message:

ODBC Specification Conformance Error [-7731]. This error should be reported to the ODBC driver vendor.



RESOLUTION
To avoid this error message, use one of the following methods:

  Recreate the view and use the Microsoft SQL Server CONVERT function to change the user-defined data type to any system data type in the view's database.

For example, you can use the following syntax to create a view based on the Microsoft SQL Server sample database named PUBS and convert the au_id field to a VARCHAR data type:

     create view test as      select 'au_id'=convert(varchar(11), au_id), from pubs..authors  Add the user-defined data type to the database that contains the view by using the sp_addtype system stored procedure in Microsoft SQL Server.

For example, if you want to attach to a view in the MASTER database based on the Authors table from the PUBS sample database that contains a user-defined data type called ID, you can run the sp_addtype system stored procedure to add the ID data type to the MASTER database.



Steps to Reproduce Problem
  In the Microsoft SQL Server sample database PUBS, create a view in the MASTER database using the following syntax:

     create view test as      select * from pubs..authors  Start Microsoft Access.</li> Open the sample database NWIND.MDB.</li> On the File menu, click Attach. Because the Authors table in the PUBS database contains a user-defined data type called ID, you receive the following error message:

ODBC Specification Conformance Error [-7731]. This error should be reported to the ODBC driver vendor.

</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

<div class="references_section">