Solved

Linked SQL table shows #Deleted in fields.

Posted on 2010-09-22
9
523 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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