Knowledge Base  

Collation Conflict Error When You Open an Access Project Table or ViewPSS ID Number:  318989 

Article Last Modified on  7 / 21 / 2003 


 --------------------------------------------------------------------------------
 
The information in this article applies to:


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 Visual Studio. NET on the client computer.
The collation of 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 Microsoft 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: 

Start Microsoft Access, and then click New on the File menu.
In the New File task pane, click Project (New Data).
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.
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.
On the File menu, point to Get External Data, and then click Import.
In the Import wizard, select the project you were previously using to connect to 
SQL Server, and then click Import.

Click Select All on each tab to select all of the objects.
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
Remove Visual Studio. NET.
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 

Start Access  2002 .
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
On a clean Windows  2000  computer, install Microsoft Access  2002 , SQL Server 
 2000 , and Visual Studio. NET.
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.
Link to or create a new SQL Server database with a different collation than the 
server default.
Add a new table.
Change the connection of the NorthwindCS.adp sample file to point to the newly 
added database from step  3 .
Try to open a table. Note that you receive the error message that is mentioned in 
the  "Symptoms"  section of this article.