#Deleted appears in column in MDB, but data is good

77 views
Skip to first unread message

Charax

unread,
Jun 24, 2006, 4:57:40 PM6/24/06
to
I'm running WinXP Pro, SQL Server 2000 back end, MS Office Access 2003 front
end
- Access is ver. 11.6566.6568 SP2
- Component checker shows MDAC 2.8 SP1 on Windows XP SP2
- Control Panel, installed programs shows MDAC KB870669 is installed
- ODBC Data Source Administrator shows Microsoft Access Driver (.mdb) is
ver. 4.00.6304.00; however, in the dialog box MS Access > About > System
Information > Office 2003 Applications > Microsoft Access Office 2003 > Jet
Core Components, the Path for both Jet 4.0 and ODBC items is "Not Available"

In an SQL Server database upsized from Access:
When the table is viewed in an Access MDB with ODBC links to the table, some
records show #Deleted in every column except the timestamp which is blank.
When the same table is viewed using an Access ADP front end, those same
records correctly show the data. The table has a primary key composed of
three fields with this structure:
PK dwSellwood smallint 2
PK intSellwood smallint 2
PK txtSellwood nvarchar 255
fVerified bit 1
ObvType ntext 16 (allow nulls)
upsize_ts timestamp 8

There is a check constraint to prevent an empty field on the txtSellwood
field, written as ([txtSellwood] <> '')

Text txtSellwood and ObvType is mixed Greek and English using Unicode
characters.

Can anyone explain the #Delete problem and how to correct it so the Access
MDB front end can operate properly?

Thanks,

Chris Hopkins


Stefan Hoffmann

unread,
Jun 26, 2006, 4:18:43 AM6/26/06
to
hi,

Charax wrote:
> The table has a primary key
> composed of three fields with this structure:
> PK dwSellwood smallint 2
> PK intSellwood smallint 2
> PK txtSellwood nvarchar 255

Has your linked table (in design view) this PK?


mfG
--> stefan <--

Charax

unread,
Jun 26, 2006, 7:19:28 AM6/26/06
to
Dear Stefan,

"Stefan Hoffmann" <stefan....@explido.de> wrote in message
news:eCeggjP...@TK2MSFTNGP03.phx.gbl...

Yes, in MS Access Project design view, the primary key is composed of three
fields: dwSellwood, intSellwood, and txtSellwood. (They also show as a
three-field PK in design view of the Access MDB to which the table is
linked.)

Any ideas?

Cheers,

Chris Hopkins

Charax

unread,
Jun 29, 2006, 2:45:03 PM6/29/06
to
I've been experimenting and my problem is now identified but not solved:

If the PK of an SQL Server 2000 table is an nvarchar field and there is a
Greek Unicode character PK column, then ODBC mangles the record so that
#Deleted appears in every column of a record of a table linked to an MDB.

This seems to be a big problem for the ODBC driver. Can it support Unicode
in a PK?

To confirm this, I created a simple test table in SQL Server 2000 where the
PK is an nvarchar field. I loaded simulated data into 8 records where the PK
field of 2 records contains Greek Unicode. Sure enough, when the SQL Server
is linked through ODBC to an Access 2003 MDB, the two records which contain
Greek show #Deleted in every column. The MDB database is defined as an
Access 2000 format.

Ouch! Am I missing a setting or some critical bit of information?

Thanks,

Charax

"Charax" <chop...@ameritech.net> wrote in message
news:eSWzVD9l...@TK2MSFTNGP05.phx.gbl...

Stefan Hoffmann

unread,
Jun 30, 2006, 4:12:19 AM6/30/06
to
hi,

Charax wrote:
> If the PK of an SQL Server 2000 table is an nvarchar field and there is
> a Greek Unicode character PK column, then ODBC mangles the record so
> that #Deleted appears in every column of a record of a table linked to
> an MDB.

On page 4 in the ODBC setup, there is a translation option for charsets,
which is by default active.


mfG
--> stefan <--

Charax

unread,
Jun 30, 2006, 9:06:04 AM6/30/06
to

"Stefan Hoffmann" <stefan....@explido.de> wrote in message
news:%23ZN7lyB...@TK2MSFTNGP02.phx.gbl...
> hi,

>
> On page 4 in the ODBC setup, there is a translation option for charsets,
> which is by default active.
>
>
> mfG
> --> stefan <--

Thanks for your note, Stefan. Sorry to be so dense, but what ODBC setup are
you talking about? How do I get to check/change the ODBC translation option
settings?

Cheers,

Charax

Stefan Hoffmann

unread,
Jul 1, 2006, 7:05:46 AM7/1/06
to
hi,

Charax wrote:
>> On page 4 in the ODBC setup, there is a translation option for
>> charsets, which is by default active.

> Thanks for your note, Stefan. Sorry to be so dense, but what ODBC setup
> are you talking about? How do I get to check/change the ODBC translation
> option settings?

Go to the ODBC control panel and check your configuration. In the german
localized version on page 4 there is a check box for this option.


mfG
--> stefan <--

Charax

unread,
Jul 1, 2006, 3:39:38 PM7/1/06
to
"Stefan Hoffmann" <stefan....@explido.de> wrote in message
news:O$2oK4PnG...@TK2MSFTNGP03.phx.gbl...
> hi,

>
> Go to the ODBC control panel and check your configuration. In the german
> localized version on page 4 there is a check box for this option.
>
>
> mfG
> --> stefan <--

Thanks, I checked this and the translation box is properly checked. That
evidently is not the cause of the problem. Remember, the Greek appears fine
as long as it is not in a PK.

More testing: I created an SQL Server table with a multi-field unique index
where one of the fields contains a Greek word. The Greek appears fine in SQL
Server and when the table is ODBC linked to an MDB.

It is only when the Greek appears in the primary key that the record is
trashed by ODBC, showing #Deleted in every column in an MDB (but OK in ADP
or SQL Server). Looks like a bug to me.

Anyone else have a suggestion?

Cheers,

Charax

Stefan Hoffmann

unread,
Jul 3, 2006, 8:11:28 AM7/3/06
to
hi,

Charax wrote:
> "Stefan Hoffmann" <stefan....@explido.de> wrote in message

> It is only when the Greek appears in the primary key that the record is
> trashed by ODBC, showing #Deleted in every column in an MDB (but OK in
> ADP or SQL Server). Looks like a bug to me.
>
> Anyone else have a suggestion?

Use a INT IDENTITY() PK.


mfG
--> stefan <--

Charax

unread,
Aug 14, 2006, 9:18:44 AM8/14/06
to
"Stefan Hoffmann" <stefan....@explido.de> wrote in message
news:eeWrMmpn...@TK2MSFTNGP03.phx.gbl...
> hi,

>
>> Anyone else have a suggestion?
> Use a INT IDENTITY() PK.
>
> mfG
> --> stefan <--
>

Cryptic though it was, Stefan's suggestion was the correct one -- that is,
redesign my database to avoid using an nvarchar datatype primary key if it
will contain Unicode characters outside the ASCII range.

BUG: In an SQL Server 2000 table connected to an Access 2003 MDB through
ODBC, there can be an erroneous redundant translation between Unicode and
ASCII characters, and the translation only happens on primary key. The
result is that every column in the record will show #Deleted. This issue has
been reported internally at Microsoft as a bug in the ODBC driver and is
awaiting action.

Note that the bug also exists in the ODBC driver in the latest Microsoft SQL
Server Native Client (for Microsoft SQL Server 7.0, 2000 or 2005), Feature
Pack for Microsoft SQL Server 2005 - November 2005, version 9.00.1399:
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en

WORKAROUND: However, while waiting for an updated ODBC driver, there is a
workaround if you cannot redesign your database to avoid the nvarchar PK:
1. On Win XP workstation, go to Control Panel, open Regional and Language
Options.
2. On Advanced tab, select Greek in Language for non-Unicode programs.
3. Click Apply button. You may need the setup CD-ROM for Windows XP.
4. Restart the computer. Then all records will correctly show the data.

I expect languages other than Greek will work, but since my problem is
caused by mixed Greek and English in the nvarchar field, I've only tried
Greek. I've seen no other problems when using this workaround, but I have no
non-Unicode applications.

Hope this is helpful to others.

Cheers,

Charax

Tony Toews

unread,
Aug 14, 2006, 10:04:18 PM8/14/06
to
"Charax" <chop...@ameritech.net> wrote:

>Hope this is helpful to others.

Indeed it is. I've posted a copy to my fellow Access MVPs so we'll
remember this for the future.

Thanks very much for posting your results and work around.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Tony Toews

unread,
Aug 15, 2006, 4:47:37 PM8/15/06
to
"Charax" <chop...@ameritech.net> wrote:

>Cryptic though it was, Stefan's suggestion was the correct one -- that is,
>redesign my database to avoid using an nvarchar datatype primary key if it
>will contain Unicode characters outside the ASCII range.

Some MVPs have suggested that a timestamp field might be the answer.
Did you try such first?

TimeStamp fields are highly recommended when using Access against SQL
Server database.

Charax

unread,
Aug 15, 2006, 6:15:16 PM8/15/06
to
----- Original Message -----
From: "Tony Toews" <tto...@telusplanet.net>
Newsgroups: microsoft.public.access.odbcclientsvr
Sent: Tuesday, August 15, 2006 4:47 PM
Subject: Re: #Deleted appears in column in MDB, but data is good

> Some MVPs have suggested that a timestamp field might be the answer.
> Did you try such first?
>
> TimeStamp fields are highly recommended when using Access against SQL
> Server database.
>
> Tony

Yes, Tony. Timestamp fields don't help. BTW, this was not resolved by me,
but was received from Microsoft PSS. They kindly refunded my money when they
saw where the problem was.

Cheers,

Charax

Tony Toews

unread,
Aug 15, 2006, 6:28:16 PM8/15/06
to
"Charax" <chop...@ameritech.net> wrote:

>> Some MVPs have suggested that a timestamp field might be the answer.
>> Did you try such first?
>>
>> TimeStamp fields are highly recommended when using Access against SQL
>> Server database.
>>

>Yes, Tony. Timestamp fields don't help.

Thanks for the clarification

> BTW, this was not resolved by me,
>but was received from Microsoft PSS. They kindly refunded my money when they
>saw where the problem was.

Very nice indeed.

Reply all
Reply to author
Forward
0 new messages