Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Linked SQL table shows #Deleted in fields.

Posted on 2010-09-22
9
Medium Priority
?
559 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 1000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
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 …

722 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