Microsoft KB Archive/223202

= ACC2000: Unexpected Behavior with Case-Sensitive Objects =

Article ID: 223202

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q223202



Advanced: Requires expert coding, interoperability, and multiuser skills.

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



SYMPTOMS
In a Microsoft Access project, if you are connected to either a Microsoft SQL Server or to a Microsoft Data Engine (MSDE) that has a Sort Order set to "Dictionary order, case-insensitive," but has Unicode Collation case sensitivity turned on, you can see unexpected behavior. This behavior can occur with this type of server if you have objects that have the same name but that are spelled with all or some letters in a different case. In this scenario, you can see the following two unexpected behaviors:

Wrong Object Is Deleted
If in the user interface of the Access project, you try to delete the second or greater object of the same name, you find that actually the first object with that name is deleted. For example, suppose that you have the following tables listed:   categories Categories CATEGORIES In an Access project file connected to this database, if you try to delete the table named "CATEGORIES," you find that you have actually deleted the table named "categories."

Table Appears to Contain No Data
If in the user interface, you try to open the second or greater table of the same name, you actually see the columns of the first table and they are empty. For example, suppose that you have the following tables listed:   categories Categories CATEGORIES In an Access project file connected to this database, if you try to open the table named "CATEGORIES," you see that columns from the table named "categories" are shown instead and those columns are empty.



CAUSE
The Access project uses the sort order to determine which object to delete or open. The default Sort Order of both Microsoft SQL Server and Microsoft Data Engine is "Dictionary order, case-insensitive," meaning they ignore case when acting upon objects. Also, the default setting for Unicode Collation is case-insensitive. However, if during the setup of Microsoft SQL Server or MSDE, the Unicode Collation case sensitivity is turned on, but the Sort Order is left at the default of "Dictionary order, case-insensitive," it allows objects with the same spelling but with different case to exist on the server.

Using a server set up in this way, if you delete or open an object in the user interface of the Access project, Access finds the first occurrence of the object name that matches the spelling but does not try to match the case because it is using a dictionary sort order that is case-insensitive. Therefore, the wrong object could be chosen.

NOTE: This behavior does not occur if the server was set up with case-sensitive sort order as well as case sensitive Unicode collation.



RESOLUTION
The following steps show you how to work around these two behaviors. The steps use tables named CATEGORIES and Categories. You should replace these table names with the names of your tables.

Deleting Objects That Have Same Name/Different Case
Delete an object by using the DROP statement in a stored procedure. To do so, follow these steps:  In the Database window, click Stored Procedures under Objects, and then click New.  In Design view, type the following into the new stored procedure:   Create Procedure DelTable

AS  DROP TABLE CATEGORIES

Return  Press F5 to refresh the Database window.

Browsing Tables That Have Same Name/Different Case
View the table by using a stored procedure. To do so, follow these steps:  In the Database window, click Stored Procedures under Objects, and then click New.</li>  In Design view, type the following into the new stored procedure: <pre class="fixed_text">  Create Procedure ShowTable

AS  SELECT * FROM Categories

Return </li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Behavior
 On a clean machine running either Microsoft Windows 95 or 98, or Windows NT 4.0 SP3, run the Setup program of either Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0.</li> If you are running SQL Server 7.0, in order to see the dialog box for the next step, make sure to select Custom Setup.</li> In the Character Set/Sort Order/Unicode Collation dialog box, set the Sort Order to Dictionary order, case-insensitive and under Unicode Collation, make sure the Case-insensitive option is not selected (not checked).</li> Complete the setup with all other defaults.</li> On the same machine, install Access 2000.</li> Make sure MSDE or SQL Server is running.</li> Open Access and from the Microsoft Access dialog box list, select Northwind SQL Project File and click OK. The Access project opens.</li> In the Install Database dialog box, click Yes. Access proceeds to install the SQL Server database NorthwindCS to your server.</li> Once the database is created, click OK in the Installation Successful dialog box and OK on the startup screen.</li> In the Database window, click Stored Procedures under Objects, and then click New.</li>  In the new stored procedure, type the following: <pre class="fixed_text">  Create Procedure makeTestTables

As

SELECT * INTO test FROM Categories SELECT * INTO TEST FROM Employees

RETURN </li> Save and run the stored procedure.</li> <li>Press F5 to refresh the Database window.</li> <li>Click OK on the confirmation dialog box and go to Tables. Note that you have two tables with the same name but spelled with different case letters, "TEST" and "test." "TEST" contains a copy of the Employees table and "test" contains a copy of the Categories table.</li> <li>Double-click to open the table named TEST. Note that you see columns from the table Categories instead of from Employees. The table also appears empty.</li> <li>Close the TEST table.</li> <li>Select the table whose name is in all capital letters, "TEST."</li> <li>Press the DELETE key on the keyboard. Click Yes to the "Do you want to..." message.

Note that the "test" table has disappeared and the table "TEST" remains.</li></ol>

Additional query words: pra upper lower title proper case

Keywords: kbbug kbclientserver kbpending KB223202

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.