Solved

Linked SQL table shows #Deleted in fields.

Posted on 2010-09-22
9
532 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

820 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