Carol, sometimes to get around this sort of thing I just do a "build
table" query in Access and run against the SQL Sever view or base tables
when the Access app is loading. It's a slight of hand trick and easily
hidden under "apps always take a while to load" category, but now
everything is lightning fast that looks at this information.
Another variation of this theme is automate importing the view (they
import as tables in Access) rather than link.
>>> "Jay Grubb "
<mssqldba-ezmlmshield-x98339255.[Email address protected] 10/26/2005
11:18 AM >>>
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
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page