Users cannot access SQL views via Ms Access

Question

Users cannot access SQL views via Ms Access

eseosaoregie

SSCertifiable

Points: 6889
More actions
April 3, 2009 at 3:54 am

#155505

I have a sql database with various views and tables which users access via linked odbc in MS Access. They have no problem viewing the tables but they access the views. The sql for one of the view is as follows
ALTER VIEW [dbo].[vw_SUS_Challenge_AandE_OUTPUT]
AS
SELECT dbo.SUS_Challenge_AandE.AANDE_ID, dbo.SUS_Challenge_AandE.HOSPITAL_NO, Practice.PRACTICE_LONG_NAME, AE.Practice_Code, GP.GP_NAME,
dbo.SUS_Challenge_AandE.NHS_NUMBER, Patient.FORENAME, Patient.SURNAME, Patient.POSTCODE, dbo.SUS_Challenge_AandE.PROVIDER_CODE,
dbo.PBC_Providers.providerName, 'AE' AS APC_OP, dbo.SUS_Challenge_AandE.POD, AE.Sex, dbo.udf_calculateAge(AE.Date_of_Birth,
dbo.SUS_Challenge_AandE.ATTENDANCE_DATE) AS Age, dbo.SUS_Challenge_AandE.COST, dbo.SUS_Challenge_AandE.NOTES,
dbo.SUS_Challenge_AandE.MONTH_OF_ATTENDANCE,AE.Arrival_Date,dbo.SUS_Challenge_AandE.PRICE_SOURCE, dbo.SUS_Challenge_Reasons.QUERY_STATUS,
dbo.SUS_Challenge_AandE.CHALLENGE_GROUP, dbo.SUS_Challenge_Groups.GROUP_NAME, dbo.SUS_Challenge_Reasons.REASON
FROM dbo.PBC_Providers RIGHT OUTER JOIN
BRENTREPODB02.Olympus.dbo.GP_PRACTICE Practice RIGHT OUTER JOIN
nwcscmdsdata.dbo.Acc_and_Emer_CMDS_Data AE INNER JOIN
dbo.SUS_Challenge_AandE ON AE.AandE_ID = dbo.SUS_Challenge_AandE.AANDE_ID INNER JOIN
dbo.SUS_Challenge_Groups ON dbo.SUS_Challenge_AandE.CHALLENGE_GROUP = dbo.SUS_Challenge_Groups.CHALLENGE_GROUP INNER JOIN
dbo.SUS_Challenge_Reasons ON dbo.SUS_Challenge_AandE.CHALLENGE_GROUP = dbo.SUS_Challenge_Reasons.CHALLENGE_GROUP AND
dbo.SUS_Challenge_AandE.CHALLENGE_REASON = dbo.SUS_Challenge_Reasons.CHALLENGE_REASON LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.NACS_GENERAL_PRACTITIONER GP ON AE.GP_Code = GP.GP_CODE COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.NSTS_PATIENT Patient ON dbo.SUS_Challenge_AandE.NHS_NUMBER = Patient.NHS_NUMBER COLLATE SQL_Latin1_General_CP1_CI_AS ON
Practice.GP_PRACTICE_CODE COLLATE SQL_Latin1_General_CP1_CI_AS = AE.Practice_Code ON
dbo.PBC_Providers.providerCode = dbo.SUS_Challenge_AandE.PROVIDER_CODE
WHERE (dbo.SUS_Challenge_Reasons.QUERY_STATUS = 'AA') OR (dbo.SUS_Challenge_Reasons.QUERY_STATUS = 'BB')

I have attached an image of the error message that the users get when they try and access the view in MS Access.
Any ideas on how to solve this would be very much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kevin Cole Old Hand Points: 304 More actions · Answer 1

I think the error message you are getting indicates a timeout. If you are using a linked table in Access then by default it is a DAO connection object and you cannot set the same breadth of tunable parameters that you can with an ADO connection. Specifically with an ADO connection in code you can set the connection and command timeout values to 0 (zero) which means no timeout, so it waits forever for rows to be returned. However with a default DAO connection you have no timeout values to set and so it gives up after a relatively short time.

If this is the case, then you could fix the situation by:

- creating an ADO connection inside a code module and connecting to the server via code

or

- put a clustered index on the view, which would have the effect of persisting the data in the indexed fields, causing the view to be constructed much more quickly

or

- simplify the view so it has fewer joins and takes less time and memory to build

Hope this helps.

--KC

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi

I think there is an authentication problem. If you have a look to your attached error message you see in last row that your Access application either tries to connect with windows authentication or without any log on information. Try to reconnect the views.

Greets

Flo

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

indeed ... an authorzation issue to the linked server BRENTREPODB02.

How did you set up the linked server usage security ?

BTW: nice view ... using the mixture of right and inner joins.

Probably no technical problem with that, just your dba may have

a usage problem analysing it from right to left. (so from bottom to top)

Most of us prefer the combination with left joins because of the ease of reading and interpreting.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Kevin Cole Old Hand Points: 304 More actions · Answer 4

I'm confused. I was assuming it was only the standard MS Access linked-tables connection. If there is nothing wrong with the table links, could there be a separate security problem with the views? Maybe if the views are owned by a different schema that his user doesn't have access to?