Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

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
0
BobRosas
Asked:
BobRosas
  • 5
  • 3
1 Solution
 
BobRosasAuthor Commented:
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?
0
 
Mark WillsTopic AdvisorCommented:
Most likely the new datatypes in SQL Server. They are DATE and TIME whereas they used to be DATETIME.

You could check by creating a "dummy" file and try connecting to that. E.g. in SQL Server :

select convert(datetime,chgdate) as chgdate
     , convert(datetime,chgtime) as chgtime
     , StoreNo
     , Convert(datetime,rcdDate) as rcddate
     , Ztape
     , Fldname
     , etc etc etc
into EE_tmp_tst_history
from tblhistory

Just make sure EE_tmp_tst_history doesnt already exist, then try linking to that table from Access.

What drivers are you using ? How are you connecting ?

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
BobRosasAuthor Commented:
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
0
 
BobRosasAuthor Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
BobRosasAuthor Commented:
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!
0
 
BobRosasAuthor Commented:
Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now