asked on

Linked SQL table shows #Deleted in fields.

I have an Access 2007 front end and a SQL Server 2008 backend. When I try to view tblHistory within Access it shows #Deleted in all the fields. I tried deleting the link and recreating it but it's the same. I found an EE comment that suggested changing data types so I changed 4 fields from nvarchar to varchar. Then I deleted the link again and relinked it. Still not working. I think it may be my ChgTime field. In Access the datatype was Date/Time (before conversion). SQL Server shows it as just time. Now when I look at the data types for the newly linked table in Access design view it shows time as a text field. Do I need to change the data type to something else in SQL? If the data type is ok then any ideas on why the #deleted message?
I'm attaching a jpg of what I currently have in SQL Server as well as sample data.

HistoryDataTypes.JPG

MohammedU

Check the following..
http://office.microsoft.com/en-us/access-help/i-see-deleted-displayed-in-a-control-HA001186566.aspx

BobRosas

ASKER

Thank you for your quick response. I apologize for my delay in responding. I just wanted to add that the data is there and just fine if I view it from SQL server. If I create a qry in Access and add distinct then I can view the data but I can't change it and I need to be able to. None of the other links in your post descrbed circumstances like mine. Any other thoughts?

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BobRosas

ASKER

Thank you so much for the code. I think I'm almost there. I can now see the data but it's still read only. I think it's because I didn't know how to change your code and make the first 2 fields (chgdate and chgtime) a composite key. Can you help me with that? I'd be glad to increase points.
Thanks

BobRosas

ASKER

I tried just altering the table I have with...

alter table EE_tmp_tst_history add primary key (chgdate, chgtime)

but I get the error...
Msg 8111, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table 'EE_tmp_tst_history'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Mark Wills

OK, you will need to actually create the table properly.

The code above was really more of a quick test to make sure it was only the datatypes causing the issues...

To create as a primary key, use SSMS to go into the table (right click and design) and change the properties of those two columns to be NOT NULL.

Mark Wills

You might also want to double check the permissions on those tables, make sure they are read write (and no deny read) etc for the user logging into sql server.

BobRosas

ASKER

Thanks for all your help. I will try your suggestions and post related questions if needed. I raised your points, the code you gave me works great!

BobRosas

ASKER

Thanks again!