Access2k + SQL 7.0 Lock problem
From: Kyrill Fomichev (kfomichev_at_ikadent.ru)
Date: 10/28/04
- Next message: Oded Kovach: "Using Pass-Throw query"
- Previous message: burg: "Re: Relationships"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 28 Oct 2004 11:45:07 +0400
Hi.
I have an Access database (mdb) which has tables linked to SQL Server 7.0
via ODBC.
Among others there are three tables:
Clients (Stores info on all clients)
Persons (Stores names of contact persons in each client company, is joint
with Clients table)
Events (Stores info on telephone calls, meetings etc. with each Clent, is
joint with Clients table. It has info about when and which of our managers
made the contact, is the contact complete or planned, contact description,
the person of client company to contact )
I need to display a summary table which contains info on each client: when
and who of the employees made contacts with the client, who was the client's
contact person, thÕ contact description, and if there is a subsequent
contact planned - the same info on the planned contact. One single row for
each client.
First I made a set of nested queries in Access. This worked reliably but
slow.
Then I decided to write a view (with a subquery set) on SQL Server and
linked it as a table in Access. Now it works very quickly, but I got a great
problem:
Each time the summary table is opened in Access SQL Server places a great
number of locks on the Events table index pages. This prevents other users
from editing Events records while the first user looks at the summary table.
The form that displays the summary table has RecordsetType property set to
Static and RecordLocks property set to No Locks. The user has only "Select"
permission to the view set on server. It also doesn't matter how is this
view opened: as linked Access table, as Access SQL pass-through query or as
a view in Enterprise Manager.
The view source SQL is below (if this helps).
Dou yoy have any suggestions?
Best regards,
Kyrll.
CREATE VIEW dbo.Summary
AS
SELECT SummaryMaxDate.ManagerKey, Clients.ClientKey,
Clients.RusName, Events.EventDate AS Date1,
Events.Description AS Desc1, Persons.PName AS Name1,
Events_1.EventDate AS Date2,
Events_1.Description AS Desc2, Persons.PName AS Name2
FROM (SELECT Events.ClientKey, Events.ManagerKey
FROM Events
WHERE (((Events.ClientStatusKey) < 30))
GROUP BY Events.ClientKey, Events.ManagerKey)
SummaryMaxDate INNER JOIN
Clients ON
SummaryMaxDate.ClientKey = Clients.ClientKey LEFT OUTER JOIN
(SELECT Events.ClientKey, Events.ManagerKey,
MIN(Events.EventDate) AS [Min-EventDate]
FROM Events
WHERE (((Events.ClientStatusKey) < 30) AND
((Events.EventStatusKey) = 10))
GROUP BY Events.ClientKey, Events.ManagerKey)
SummaryMinPlanDate ON
SummaryMaxDate.ClientKey = SummaryMinPlanDate.ClientKey AND
SummaryMaxDate.ManagerKey = SummaryMinPlanDate.ManagerKey
LEFT OUTER JOIN
(SELECT Events.ClientKey, Events.ManagerKey,
MAX(Events.EventDate) AS [Max-EventDate]
FROM Events
WHERE (((Events.ClientStatusKey) < 30) AND
((Events.EventStatusKey) = 20))
GROUP BY Events.ClientKey, Events.ManagerKey)
SummaryMaxCompleteDate ON
SummaryMaxDate.ClientKey = SummaryMaxCompleteDate.ClientKey
AND
SummaryMaxDate.ManagerKey = SummaryMaxCompleteDate.ManagerKey
LEFT OUTER JOIN
Events ON
SummaryMaxCompleteDate.ClientKey = Events.ClientKey AND
SummaryMaxCompleteDate.ManagerKey = Events.ManagerKey AND
SummaryMaxCompleteDate.[Max-EventDate] = Events.EventDate
LEFT OUTER JOIN
Events Events_1 ON
SummaryMinPlanDate.ClientKey = Events_1.ClientKey AND
SummaryMinPlanDate.ManagerKey = Events_1.ManagerKey AND
SummaryMinPlanDate.[Min-EventDate] = Events_1.EventDate LEFT
OUTER JOIN
Persons ON
Events.PersonKey = Persons.PersonKey LEFT OUTER JOIN
Persons Persons_1 ON
Events_1.PersonKey = Persons_1.PersonKey
- Next message: Oded Kovach: "Using Pass-Throw query"
- Previous message: burg: "Re: Relationships"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
