June 14, 2005 at 11:17 am
Hi
I have an SQL database and this user has full permission of the database. The user can do an ODBC to link to the table and open it in Access, update or insert data. The SQL database has a simple SELECT query (select * from table where column1='test'). This user can link to that query but cannot update or insert data. I go to SQL and right click the VIEW, properties, click on permission and give that user full permission of the view but user cannot insert or edit the data in the link to the view in Access. I wonder if there is anyway I can set it up so that this user can insert data to that view in Access through the link. Thank you
June 14, 2005 at 5:10 pm
---- The user can do an ODBC to link to the table and open it in Access, update or insert data.
---- This user can link to that query but cannot update or insert data.
Pleasse make up your mind...
June 14, 2005 at 6:29 pm
It would be great if the user can link to the query and update or insert data because of permission requirements. We don't want the user to see all data in the linked table, just data in the query and can update and insert that query on the server through the link. Wonder if this is possible.
June 15, 2005 at 3:32 am
Access can get a little picky if it can't see a primary key / unique index. A simple way to check is to create a query in Access and add the view. If there is a primary key / unique index then those fields will be shown in bold in the table in the query designer.
If there are no fields in bold, then the view does not have a primary key defined. What you have to do is delete the link to the view and when you recreate it make sure that when you link the view, you select the fields that uniquely identify the record. You will notice that this dialog box mentions that this is necessary to update records.
June 15, 2005 at 6:07 am
Thank you. It works, with primary key in SQL server query, I can link to the query and insert or update data. Thuan Pham
June 16, 2005 at 3:52 pm
You may already be aware of this but you're running into an MS Access limitation. Pass-through queries and linked views are generally not updateable. In the MS documentation there is mention of ability to make a single table in the view updateable, but I have never been successful with that and finally gave up on it.
What I had to go to was unbound forms. Load the info you want, and then use code to update tables. HTH
Sam
June 17, 2005 at 1:26 am
Definitely not true.
Linked views are updatable a) if they are updatable in SQL server b) if Access can work out a unique indentifier for the table.
If you have a multi-table view then you may need a set of instead of update/insert/delete triggers to get it to work in SQL server.
The concept of the single updatable table is more of an adp type problem where you have to specify the unique table in the form design view. AFAIK adp looks directly at the schema information and works directly with the tables and not with the view. In English, what it does is generate update/insert/delete (SQL) statements based on the underlying tables and not on the view - what is known as being too clever by half.
June 17, 2005 at 7:50 am
Thanks jf!
You're obviously more knowledgeable. I just got frustrated with views and found another way.
Sam
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply