ACC2: ODBC Specification Error Attaching to SQL Server View

• Microsoft Access 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

Microsoft SQL Server "Language Reference," version 4.2, pages 97-109

Keywords : kberrmsg kbusage OdbcSqlms
Version : 2.0
Platform : WINDOWS
Issue type : kbbug