Table linked to ODBC

[Allan]

Hi,

i have a table linked to ODBC. When i'm viewing the content of the table it
showed "#Deleted" for all the columns and row. What would seem to be the
problem. However, when i imported it to table, i can see all the data.

Which is better, linking or importing data from ODBC?

Thanks.

Allan

 

[Sylvain Lafontaine]

Possibly a missing primary key (or at least, any other unique index) on the
table or the incapacity of Access/JET to find the primary key or a unique
index. If the backend table is not a real table but a view, you might have
to locally define a primary key on the ODBC linked table; see:

http://support.microsoft.com/kb/q209123/

On SQL-Server, each primary key and index have a distinct name. For many
versions of Access, it's important that the name of the primary key or of a
unique index be the first in alphabetical order; before the name of any
non-unique index.

If you want to edit/update the data or to always have a current view of the
data in the linked table, it's obvious that's better to link the table
instead of importing it.

 

[Allan]

Hi Sylvain,

thanks for the reply. Is it not linked table inherits the properties of the
source table? This is true to my case, hence the properties of my linked
table cannot be modified. Though it has the primary key set to each linked
still it resulted to "#Deleted". I noticed that if primary keys are more than
two, it resulted to that error.

i checked the net and found specific answers for this. see this

http://office.microsoft.com/en-us/access/HA011865661033.aspx?pid=CL100570041033

this specifically solved my concern

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

i implemented the pass through queries for all the tables i want to have.
this is really better linking the table as this does not require
updating/refreshing the linked tables, hence i save some codes.


Allan

 

[Rick Brandt]

Hi,

i have a table linked to ODBC. When i'm viewing the content of the table
it showed "#Deleted" for all the columns and row. What would seem to be
the problem. However, when i imported it to table, i can see all the
data.

Which is better, linking or importing data from ODBC?

If you want to see updates in real time then linking is better. If the
amount of data is large than linking is better.

If the primary key of the table on the server uses a DataType that does
not map exactly to an Access DataType then the #DELETED issues can come
up. Your options are:

Change the PK type on the server.

Create a View on the server casting the PK field to another type and link
to the view instead.

Use a passthrough query instead of a table link.