Solved

Linked SQL table shows #Deleted in fields.

Posted on 2010-09-22
9
553 Views
Last Modified: 2012-05-10
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
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 15

Expert Comment

by:MohammedU
ID: 33738350
0
 

Author Comment

by:BobRosas
ID: 33739879
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 33741661
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:BobRosas
ID: 33745885
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
 

Author Comment

by:BobRosas
ID: 33747028
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33747137
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33747160
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
 

Author Comment

by:BobRosas
ID: 33748603
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
 

Author Closing Comment

by:BobRosas
ID: 33748612
Thanks again!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question