[X]My Assistant

[curiosity5]

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

[GroverParkGeorge]

"... 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.

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.

[BruceM]

How about a pass through query?

[GroverParkGeorge]

Doh.

[curiosity5]

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?

[GroverParkGeorge]

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.

[curiosity5]

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.

[GroverParkGeorge]

Probably because they are not updateable....

[curiosity5]

Thank you. I'll go with that.

[curiosity5]

now I know why people don't like pass thru queries...omg...it's so slow!!

[BruceM]

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.

[GroverParkGeorge]

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.

[curiosity5]

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.

[GroverParkGeorge]

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.

[curiosity5]

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

[GroverParkGeorge]

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.

[GroverParkGeorge]

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.

[curiosity5]

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?

[GroverParkGeorge]

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:

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

[haresfur]

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.