X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Make Linked SQL Server Table Read Only, SQL Server 2012    
post Jan 16 2018, 01:31 AM

Posts: 2,420
Joined: 27-February 09

Maybe if the SQL Admins granted read-only access to a bunch of tables, and maybe created a schema for him where he could create tables? Then he can query against the read-only tables (because he's explicitly denied UPDATE and DELETE against them or the entire other schema?)
Go to the top of the page
post Jan 21 2018, 03:47 AM

Posts: 194
Joined: 8-August 07
From: Doha, Qatar

yes, data has to be current.
I have a linked SQL server table now and have an inner join query to a ms access table but it's not as slow as joining it with pass thru query.
Thanks. Let me see if IT will let me have access to ssms. They say all I needed is datareader and datawriter access to create a view in SQL server. Is this correct?

That seems odd. My guess would be that the pass-thru *appears* slower because all the data is loading all at once (pass thrus, being read-only should be using snapshot) whereas the linked table is returning the first *batch* of records quickly for you to see while the remainder load in the background (an updateable data source will be using dynaset).

You may want to check by doing a
Select count(*) from [yourquery]

And substituting yourquery for the linked and pass-thru versions of your query and timing the results. The count(*) will force the sub query to process all records before returning a result.

As others have suggested, if you can move the Access data up to SQL or the SQL data down to Access then your join will perform much better. But if that is really not an option, then consider the below:

In your pass-thru you should avoid select * and only select the columns you need. Depending on the data types of the columns (especially varchar(max) and varbinary(max)) they may take a long time to transfer across the network. Try limiting the number of columns you return in the pass-thru to see if that has a positive impact on performance.

Consider modifying your application interface to fetch and display smaller chunks of data at a time. E.g. by passing a parameter in the pass thru query so you are only returning a subset of records (e.g. for a particular product, customer or time period). If the dataset is large, this is recommended practice anyway (reduces locking and network traffic) and there is little point in displaying thousands of rows of data at once when a user can only usefully consume a few hundred.

Go to the top of the page
post Apr 11 2018, 03:38 PM

Posts: 594
Joined: 19-December 01

Thanks for the suggestion. I'll try that.
How hard is it to create a read-only view in SQL server and grant all users access to use this read-only view? The view is for one table(ex:Select * from table1).
The reason I'm asking is because I learned that my IT has no clue how to do this. Questions he asked made me wonder if he knows what a view even is.
Can it be done in 15 minutes? Does it need all users to log out of the database to create this?
This post has been edited by curiosity5: Apr 11 2018, 03:52 PM
Go to the top of the page
post Apr 11 2018, 08:54 PM

UtterAccess VIP
Posts: 2,621
Joined: 12-April 07
From: Edmonton, Alberta Canada

Just create the view like this:

CREATE VIEW [dbo].[View_1]
SELECT        ID, LastName, FirstName, HotelName
FROM            tblHotels
select null,null,null,null where 1 = 0

So just add as many nulls as you have selects in your query.

Go to the top of the page
post Apr 25 2018, 04:42 PM

Posts: 594
Joined: 19-December 01

I gave up on IT to have them create a view for me.
So I tried limiting the number of columns and rows in the pass-thru as suggested. Now it's faster even when joining the pass-thru query to local tables.
Go to the top of the page
post May 2 2018, 04:34 PM

Posts: 594
Joined: 19-December 01

Thanks. Got it working! smile.gif

I thought pass through queries are read only. So why does this say it's editable?
Go to the top of the page
post May 2 2018, 06:43 PM

UtterAccess VIP
Posts: 2,621
Joined: 12-April 07
From: Edmonton, Alberta Canada

Well its only one post???

It looks to be wrong. I believe if you use a ADO recordset, you can have a resulting reocrdset that is updatable, but if the PT query is used as a source to a form, or combo box etc., it is read only. And DAO reocrdets based on PT query is also read only.

so in "general" the use of a PT query is read only. I think the only exception is a ADO recordset.

But in the context of that above link - the claim that the PT query is read/write is incorrect advice.

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
2 Pages V < 1 2

Custom Search
RSSSearch   Top   Lo-Fi    27th May 2018 - 07:00 AM