You're running into a few things here:
1) For access to update, it must have a Unique Key to map the data to the
underlying tables. This is also the case for SQL Server views. The issue
is that you must have the same Key in both. Unless the 4 columns are unique
for SQLServer, you'll have problems.
2) Access doesn't process SQL Server views. If you make an Access Query,
it would do the processing locally.
3) #Deleted values happen when Access can't make a good key value mapping,
the resultant key is not unique, or if the datatype is mapped incorrectly.
Access does not have the exact same datatypes as SQL Server, and sometimes
it just guesses, and does it incorrectly.
Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x43469141.[Email address protected]
Sent: Wednesday, October 26, 2005 10:48 AM
To: LazyDBA Discussion
Subject: SQL Server/Access view processing
Hello All,
I recently created an aggregate view in SQL Server which returned around
2000 records in 4 columns. In Access I created a linked table which linked
to the view and made a combination of all 4 columns the unique record
identifier. Fine.
I then changed the value of columnC in the view so that it was obtained via
a subquery. However, this made Access run like a DOG (a slow, arthritic one)
and some of the lines just said #Deleted instead of the values I could see
when I opened view in SQL Server.
I tried recreating the linked table but this had no effect. Taking out the
subquery made the table open ok again.
The view runs instantaneously in SQL Server (with the subquery), so my
question is this:
Where does the processing of the view take place - on the server or the
client pc?
Also, why do some of the rows show as #Deleted when they are unique and
clearly there in SQL Server?
Yours confusedly,
Carol.
This email and any files transmitted within it are private and confidential.
If you are not the intended recipient, this email and any attachments within
it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using the
information contained within this email.
Nothing in this email message amounts to a contractual or legal commitment
on the part of Optilan unless confirmed by a communication signed on behalf
of the company.
Any email cannot be guaranteed to be secure, error free or free from
viruses. Although every possible care is taken by Optilan, Optilan does not
accept any liability whatsoever for any loss or damage which may be caused
as a result of the transmission of this message by email.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005
MS Sql Server LazyDBA home page