#Deleted #Deleted using MSSQL

[Gary Walter]

I have created a linked table to MS SQL2000 via the ODBC driver, when
viewed in Access 2003 all the records returned show #Deleted in the
fields. When using MSQuery and viewing the data via the same odbc source I
get the correct data. Can anyone please point me in the right direction as
to solving this problem

Hi Mark,

http://support.microsoft.com/default.aspx?scid=kb;en-us;128809

quick summary:

**quote**

.. Avoid entering records that are exactly the same except for the unique
index.
.. Avoid an update that triggers updates of both the unique index and another
field.
.. Do not use a Float field as a unique index or as part of a unique index
because of the inherent rounding problems of this data type.
.. Do all the updates and inserts by using SQL pass-through queries so that
you know exactly what is sent to the ODBC data source.
.. Retrieve records with an SQL pass-through query. An SQL pass-through query
is not updateable, and therefore does not cause "#Delete" errors.
.. Avoid storing Null values within any field making up the unique index of
your linked ODBC table.

**unquote**

other advice:

1) add a field of type TimeStamp to each table

2) make sure each table has a primary key

3) if you "Refresh" links, your links will lose
"virtual primary key"....either delete links and
relink resetting them....
or use code provided by Joe Fallon
many times on these newsgroups to
refresh links

http://groups-beta.google.com/groups?q=Joe+Fallon+Refresh+Links&hl=en

good luck,

gary

 

[Mark Hollander]

Hi All,

I am new to MS Access and am battling with the following issue.

I have created a linked table to MS SQL2000 via the ODBC driver, when viewed
in Access 2003 all the records returned show #Deleted in the fields. When
using MSQuery and viewing the data via the same odbc source I get the
correct data. Can anyone please point me in the right direction as to
solving this problem

Thank you
Mark Hollander