ACC2000: Unexpected Behavior with Case-Sensitive Objects

• Microsoft Access 2000

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

1. In the Database window, click Stored Procedures under Objects, and then click New.



2. In Design view, type the following into the new stored procedure:




   Create Procedure DelTable

   AS
   DROP TABLE CATEGORIES

   Return 

3. Press F5 to refresh the Database window.

Browsing Tables That Have Same Name/Different Case

1. In the Database window, click Stored Procedures under Objects, and then click New.



2. In Design view, type the following into the new stored procedure:




   Create Procedure ShowTable

   AS
   SELECT * FROM Categories

   Return 

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

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



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



3. 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).



4. Complete the setup with all other defaults.



5. On the same machine, install Access 2000.



6. Make sure MSDE or SQL Server is running.



7. Open Access and from the Microsoft Access dialog box list, select Northwind SQL Project File and click OK. The Access project opens.



8. In the Install Database dialog box, click Yes. Access proceeds to install the SQL Server database NorthwindCS to your server.



9. Once the database is created, click OK in the Installation Successful dialog box and OK on the startup screen.



10. In the Database window, click Stored Procedures under Objects, and then click New.



11. In the new stored procedure, type the following:




   Create Procedure makeTestTables

   As

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

   RETURN 

12. Save and run the stored procedure.



13. Press F5 to refresh the Database window.



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



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



16. Close the TEST table.



17. Select the table whose name is in all capital letters, "TEST."



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

Additional query words: pra upper lower title proper case

Keywords : kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug