UtterAccess.com
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    
 
   
curiosity5
post Jan 10 2018, 08:58 AM
Post#1



Posts: 594
Joined: 19-December 01



I have a linked SQL server table in a ms access database. Users have read/write access to this. Is there a way to make this linked table read only?
Reason: want to prevent myself from deleting data from this table by mistake

Go to the top of the page
 
GroverParkGeorge
post Jan 10 2018, 09:06 AM
Post#2


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


"... prevent myself from deleting data from this table by mistake "

Been there, done that. Washed my own mouth out with soap when I commented on the mistake. ohyeah.gif

So, you can do this in more than one way.

One would be to use a SQL Server login that has read-only privileges to create the connection. Maybe not the most convenient.

Another would be to create a view in SQL Server, based on that table, using a non-updatable syntax. Perhaps by using GROUP BY in the view. Then replace the table with this view in your Access accdb.

Still another might be to create the view, but when linking to it, do NOT designate a unique key for it in Access, which makes it non-updateable in Access.


--------------------
Go to the top of the page
 
BruceM
post Jan 10 2018, 09:18 AM
Post#3


UtterAccess VIP
Posts: 7,502
Joined: 24-May 10
From: Downeast Maine


How about a pass through query?
Go to the top of the page
 
GroverParkGeorge
post Jan 10 2018, 09:21 AM
Post#4


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Doh.

--------------------
Go to the top of the page
 
curiosity5
post Jan 10 2018, 09:22 AM
Post#5



Posts: 594
Joined: 19-December 01



that's what i thought, was hoping there is another way
Not familiar with SQL server at all.
I can't have ddladmin rights.
Someone said I would need db_datareader and db_datawriter access to create views in SQL server. Is this correct?
Do I need SQL server Management studio to do this?
Go to the top of the page
 
GroverParkGeorge
post Jan 10 2018, 09:38 AM
Post#6


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


It seems the Passthru query approach might be the best since you might find working on the SS side a bit of a hassle. Of course, if you DO plan to work more with SQL Server, this might be a good place to start learning.

Create and test the Passthru, then you can completely delete the link to the table to make sure it doesn't get modified.

--------------------
Go to the top of the page
 
curiosity5
post Jan 10 2018, 09:53 AM
Post#7



Posts: 594
Joined: 19-December 01



I tried pass thru queries too and it does seem to work.
But I thought I read somewhere not to use pass thru queries. Don't remember what the reason is.
Go to the top of the page
 
GroverParkGeorge
post Jan 10 2018, 10:30 AM
Post#8


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Probably because they are not updateable....

--------------------
Go to the top of the page
 
curiosity5
post Jan 10 2018, 11:22 AM
Post#9



Posts: 594
Joined: 19-December 01



Thank you. I'll go with that.

Go to the top of the page
 
curiosity5
post Jan 10 2018, 03:08 PM
Post#10



Posts: 594
Joined: 19-December 01



now I know why people don't like pass thru queries...omg...it's so slow!!
Go to the top of the page
 
BruceM
post Jan 10 2018, 03:52 PM
Post#11


UtterAccess VIP
Posts: 7,502
Joined: 24-May 10
From: Downeast Maine


My experience with passthrough queries (and SQL Server in general) is limited, but one of their advantages is that they tend to be fast. The only thing I can suggest offhand (because I did this not all that long ago) is to be sure the syntax is dbo.TableName rather than dbo_TableName. dbo_TableName is the default for linked table names, but if you use those names in a query I don't think it would be a passthrough query at all. Rather, you would be using the Access database engine to process the linked tables.

Other than that, or a better suggestion from somebody else, a search for "Access pass through query slow" should turn up a number of results.
Go to the top of the page
 
GroverParkGeorge
post Jan 11 2018, 09:00 AM
Post#12


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


That's counter to everything I've ever seen, or directly experienced.

Show us how you created this Passthru.

At least in theory, because the Passthru sends its SQL directly to the server, where it is executed and only sends back the recordset, it should be faster than anything on the Access side.


--------------------
Go to the top of the page
 
curiosity5
post Jan 11 2018, 07:25 PM
Post#13



Posts: 594
Joined: 19-December 01



example:
Select * from dbo.tablename;

result: very fast

But when I use this pass thru query to do an inner join with one of the access tables, result is very slow.
Go to the top of the page
 
GroverParkGeorge
post Jan 11 2018, 08:18 PM
Post#14


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Ah. That's a different story. We should have known that first.

When you join local tables to remote tables like that, it will be very slow. You hadn't mentioned, though, that that was your intention. You only wanted to know how to make a linked table read only.

Why do you need to do this? What is the full story?

Thanks.

--------------------
Go to the top of the page
 
curiosity5
post Jan 11 2018, 09:30 PM
Post#15



Posts: 594
Joined: 19-December 01



I currently have a linked SQL server table that I only need read only access to.
I have queries, forms, codes that uses this table and other ms access tables to create the output I need.
The database is getting complicated so I'm afraid I would do something incorrectly that would delete the data from the linked SQL server tables by mistake.
I thought the pass thru query can replace this updateable linked SQL server table, but guess not....getting output is too slow....



So how do i go about creating a view in SQL server?
Do I need SQL server management studio?
This post has been edited by curiosity5: Jan 11 2018, 09:34 PM
Go to the top of the page
 
GroverParkGeorge
post Jan 11 2018, 10:44 PM
Post#16


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Whether you join a linked table, or a linked view, or a Passthru from SQL Server with local Access tables, the result will be slow. This is something we have to deal with. It is not going to change much by replacing the Passthru with a view.

Is there another way to proceed? Perhaps, since this linked table doesn't need to be updateable, you could create a temporary local table to contain the records from it, and use that.

Does the data need to be current at all times? Or could you create a local, Access, temporary table and populate it with the needed records when the database opens.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jan 11 2018, 10:46 PM
Post#17


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


A view is just a query in SQL Server. Some of the syntax differs, but if all you need is the basic recordset from the table, you could easily use the same SQL.

Yes, you'd need SSMS to do this.

--------------------
Go to the top of the page
 
curiosity5
post Jan 12 2018, 08:19 PM
Post#18



Posts: 594
Joined: 19-December 01



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?
Go to the top of the page
 
GroverParkGeorge
post Jan 12 2018, 08:49 PM
Post#19


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


I can't say for sure, but if they give you DATA reader and DATA writer permissions, I can't see how you could use those permissions to create your own view. Let me verify that by referring to the Books Online Documentation, where it says:

QUOTE
Permissions

Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.
Examples

....

This post has been edited by GroverParkGeorge: Jan 12 2018, 08:59 PM

--------------------
Go to the top of the page
 
haresfur
post Jan 14 2018, 04:38 PM
Post#20



Posts: 278
Joined: 4-April 12
From: Bendigo, Australia


I'm guessing that your local table doesn't contain as much data as the SqlServer table. So another option is to push your local table, or the part of it you need up to SqlServer rather than pulling the SqlServer table down. That should be more efficient and you won't get the same bloat issues as you can get with temporary tables in Access. The downside is that you will need write permissions to the back end and need to learn enough TSql to do it.

Then use a pass through query to pull your data back down and it won't be updateable.

--------------------
-- Evan
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 08:26 PM