June 21, 2004 at 12:45 pm
When I open an OBDC linked SQL Server 2000 table I can only see "#Deleted" in every row and every field. Does anyone have a fix for this?
Thanks
June 21, 2004 at 1:55 pm
Knowledge Base article 128809
http://support.microsoft.com/default.aspx?scid=kb;en-us;128809&Product=acc97
This is because of index(es), or lack of, on the SQL table. If you can't alter the SQL database to put an index on the table then has worked for me in the past is to make a copy of the data into a local table. It depends if you are trying to do data manipulation or just report off of it.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
June 21, 2004 at 1:59 pm
The tables I am trying to view each have a clustered index as well as several non-clustered indexes so I do not think that this is my specific issue.
I am able to view the data in the table if I use a SQL Pass-Through query, so I am taking that route.
Thanks for replying.
Thanks
June 22, 2004 at 4:03 am
MSAccess can't handle a SQL (or any other type of 'linked' table) if it has no way to identify any row with a unique id, that is: you MUST have a PRIMARY KEY, or a TIMESTAMP field on your tables if you want to access them from MSAccess
HTH
Luigi
June 22, 2004 at 5:54 am
What you need to do is create a pseudo index. Create a new query in Access, go to the SQL View. Then enter
Create unique index PK_TableName on TableName (FieldName) with primary;
Run it
Russel Loski, MCSE Business Intelligence, Data Platform
June 22, 2004 at 6:56 am
Drop and Re-Link the table. You will be prompted to use a key column. Select the clustered index as your primary key. Even if it envolves multiple columns.
June 22, 2004 at 9:03 am
Jeff is quite right. I have this all the time. Another reason is when the structure of the table changes and the linked tables are not updated. Need to run the update link tables from the tools/database/linked table manager.
June 22, 2004 at 10:00 am
My tables all have primary keys, as well as other indexes.
I think the issue is the the Primary Key is a data type of Bigint in which case Access does not know what to do. I have tables with a primary key of data type smallint and Access displays those rows correctly.
I created SQL Pass-Through queries to sit on top of the table and it will work great. Then I'll never need to worry about the table structure changing and causing issues (i.e. select * from myTable)
Thanks for replying
June 22, 2004 at 10:53 am
Yes, I've also had this issue when I've used a datatype that Access didn't recognize. One other thing to look out for, make sure you're running the latest version of MDAC on the client machines.
June 22, 2004 at 11:05 am
Hai,
Please check that The columns Datatype in tje the table you are trying to view in Access may be Bigint. I had similar problem prev. After changing it to int datatype it is displaying records normally. This may be problem with DataTypes which are not recognized by the Access.
Murthy
June 22, 2004 at 11:05 am
Hai,
Please check that The columns Datatype in tje the table you are trying to view in Access may be Bigint. I had similar problem prev. After changing it to int datatype it is displaying records normally. This may be problem with DataTypes which are not recognized by the Access.
Murthy
June 24, 2004 at 2:48 am
also install :
1- MDAC 2.7 SP1 on server and clent machines
2- SQL Server SP3a on server machine
3- Jet SP8 on client machines
I hope this help u.
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply