ACC2: ODBC Specification Error Attaching to SQL Server View

• Microsoft Access version 2.0

SYMPTOMS

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

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.

MORE INFORMATION

Steps to Reproduce Problem

1. 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
   

2. Start Microsoft Access.

3. Open the sample database NWIND.MDB.

4. 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.
   

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.

REFERENCES