ACC2002: #Name Appears in Linked Table When Decimal Field Has Identity Set to Yes (282328)

SYMPTOMS

When you view a linked SQL Server table that contains at least one field with a data type of Decimal and if that Decimal field has its Identity property set to Yes, the data in the table appears as #Name.

RESOLUTION

Create a Data Source Name (DSN) that does not use the values of the regional settings. In the DSN wizard, this option is:

Use regional settings when outputting currency, numbers, dates, and times.

MORE INFORMATION

Steps to Reproduce the Behavior

Create the following table in the SQL Server sample database Pubs and name it tblTest.

Column Name Data Type Length Allow Nulls Identity Primary Key
colDecimal decimal 9 <unchecked> Yes Yes
colDescrip varchar 50 <checked> No No
Add the following data to the tblTest table:

colDecimal colDescrip
1 Descrip1
2 Descrip2
3 Descrip3
On the computer on which Access 2002 is running, open Control Panel, open the Regional Settings tool, and then on the Currency tab, assign a comma as the decimal separator.
Create a Data Source Name (DSN) on the computer on which Access 2002 is running that points to the Pubs database on SQL Server. When you create the DSN, ensure that the Use regional settings when outputting currency, numbers, dates, and times check box is selected.
Open any database, and then link the tblTest table by using the DSN that you created earlier.
Open the linked table. Note that the data appears as #Name.