A collation conflict error occurs when you open an Access Project table or view (318989)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q318989
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you open a table or view in a Microsoft Access project (.adp) file, the following error occurs:
ADO Error: Cannot resolve collation conflict for column 2 in SELECT statement

CAUSE

You receive this error message if the both of the following are true:
  • There is or was an installation of Microsoft Visual Studio. NET on the client computer.
  • The collation of Microsoft SQL Server 2000 and the database are different.
The Visual Database Tools are used by Access projects when displaying data from SQL Server tables and views. When you install Visual Studio .NET, the Visual Database Tools shared components are updated to newer versions. The newer version of the tools does not work when the collation of the user database is different from the collation from the SQL Server Master database.

RESOLUTION

You can use any of the following three methods to resolve this problem:
  • Method 1: Re-create the User Database by Using the Default Collation of SQL Server
  • Method 2: Rebuild the Master Database or Reinstall SQL Server by Using the Same Collation as the User Database
  • Method 3: Reinstall the Version of the Visual Database Tools that Were Included with Office XP

Method 1: Re-create the User Database by Using the Default Collation of SQL Server

You can only specify the collation of a database when it is created. Because Access always creates new databases by using the default collation of SQL Server, the easiest way to solve the problem is to have Access create a new database on the server and then to import the objects from your current database into the new database. To do so, follow these steps:
  1. Start Microsoft Access, and then click New on the File menu.
  2. In the New File task pane, click Project (New Data).
  3. To give your project a new name and to save it to your folder of choice, click Create in the File New Database dialog box.
  4. Use the Microsoft SQL Server Database wizard to specify the server settings, and then use the wizard to create and connect to your new database.
  5. On the File menu, point to Get External Data, and then click Import.
  6. In the Import wizard, select the project you were previously using to connect to SQL Server, and then click Import.
  7. Click Select All on each tab to select all of the objects.
  8. To import the selected items into the new file, click OK. You may get collation errors during the import process.

    After the import is complete, you can open tables without error. You may also have to re-create relationships, other SQL Server objects, and properties that were not imported.

Method 2: Rebuild the Master Database or Reinstall SQL Server by Using the Same Collation as the User Database

SQL Server 2000 comes with a utility named rebuildm.exe that you can use to create a new Master database with a different collation. You can also specify a custom collation option when you install SQL Server. However, the rebuildm.exe utility does not come with SQL Server 2000 Desktop Engine. Also, there is also no graphical utility that you can use to select a collation option when you install the Desktop Engine. You must modify the Setup.ini file to specify a custom collation for the Desktop Engine. To learn how to do this, click the article number below to view the article in the Microsoft Knowledge Base:

301413 ACC2002: "Configuring SQL Server 2000 Desktop Engine" White Paper Available in Download Center

Method 3: Reinstall the Version of the Visual Database Tools that Were Included with Office XP

  1. Remove Visual Studio. NET.
  2. Find and rename the Visual Database Tools folder. By default, this folder is installed to the following location:

    C:\Program Files\Common Files\Microsoft Shared\Visual Database Tools

  3. Start Access.
  4. To reinstall the files in the renamed Visual Database Tools folder, click Detect and Repair on the Help menu.NOTE: Although you can fix the problem in Access by following steps 2 through 4, this is not a good idea because you may experience additional problems with Server Explorer in Visual Studio. NET.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

To determine the collation settings for all databases on the server, you can run the sp_helpdb system stored procedure in the master database. The status field in the recordset that is returned by sp_helpdb displays various properties for each database, including its collation setting.

The error does not occur with functions or stored procedures. Forms and reports may continue to work as expected.

Steps to Reproduce the Behavior

  1. On a clean Windows 2000 computer, install Microsoft Access 2002 or later, SQL Server 2000, and Visual Studio. NET.
  2. Open the sample database NorthwindCS.adp in Access, and then create the NorthwindCS database on the local SQL Server when you are prompted. Verify that you can view the tables without an error message.
  3. Link to or create a new SQL Server database with a different collation than the server default.
  4. Add a new table.
  5. Change the connection of the NorthwindCS.adp sample file to point to the newly added database from step 3.
  6. Try to open a table. Note that you receive the error message that is mentioned in the "Symptoms" section of this article.

REFERENCES

To find information about SQL Server collation and about how to rebuild the Master database, see the following page on the MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_collation_192m.asp?frame=true

For more information about SQL Server, see SQL Server Books Online, which is available at the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b

Modification Type:MajorLast Reviewed:5/5/2006
Keywords:kbADP kberrmsg kbbug kbpending KB318989
©2004 Microsoft Corporation. All rights reserved.