info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Problem with UPDATE query with SQL Server backend
46 views
Skip to first unread message
James Fortune
Feb 26, 2021, 11:04:20 AM2/26/21
to
Greetings everyone!
When I set up SQL Server Express in the past and used connection strings in Access (BTW using TempVars) to connect to the tables, I had no trouble running UPDATE queries in Access to change the contents of the tables on the back end. Now I'm working on a project that has a SQL Server backend (not Express) that was set up by the customer, who has much experience setting up SQL Server instances. When I try to run the UPDATE query like before, I get the error message "Operation must use an updateable query." I've tried the following things:
1) Gave the role assigned to the user db_datareader and db_datawriter rights
2) Assigned the table in question as a Securable with Update, Select, and Insert rights to the role
3) Used the owner login and password in the connection string
In all cases, I'm able to run Select queries on the tables. I don't want to use ODBC connections. Is what I'm trying to do possible with a SQL Server backend? The SQL Server admin said it was an Access issue, not a SQL Server issue. :)
Thanks in advance,
James A. Fortune
Change is the law of life. - JFK
When I set up SQL Server Express in the past and used connection strings in Access (BTW using TempVars) to connect to the tables, I had no trouble running UPDATE queries in Access to change the contents of the tables on the back end. Now I'm working on a project that has a SQL Server backend (not Express) that was set up by the customer, who has much experience setting up SQL Server instances. When I try to run the UPDATE query like before, I get the error message "Operation must use an updateable query." I've tried the following things:
1) Gave the role assigned to the user db_datareader and db_datawriter rights
2) Assigned the table in question as a Securable with Update, Select, and Insert rights to the role
3) Used the owner login and password in the connection string
In all cases, I'm able to run Select queries on the tables. I don't want to use ODBC connections. Is what I'm trying to do possible with a SQL Server backend? The SQL Server admin said it was an Access issue, not a SQL Server issue. :)
Thanks in advance,
James A. Fortune
Change is the law of life. - JFK
Ron Weiner
Feb 26, 2021, 8:55:28 PM2/26/21
to
James Fortune wrote :
James
Is the query an access query using multiple joined tables? I have seen
that error before. If so, try changing the query to a passThru type
using pure Sql Server Dialect, or create a Sql Stored Proc and execute
that from Access.
Ron W
--
This email has been checked for viruses by AVG.
https://www.avg.com
Is the query an access query using multiple joined tables? I have seen
that error before. If so, try changing the query to a passThru type
using pure Sql Server Dialect, or create a Sql Stored Proc and execute
that from Access.
Ron W
--
This email has been checked for viruses by AVG.
https://www.avg.com
Albert Kallal (Access MVP)
Feb 27, 2021, 9:13:48 PM2/27/21
to
Well, for a test that say takes 2 minutes or less?
Try linking to the one table from access. Then simple click on that linked table, and see if you can edit or update the row.
its possible that the table does not have a PK, and thus Access can't as a general rule update such linked tables, and thus you in code not using a linked table would also have the same issue. As for not using linked tables? (well, we will just leave the why not for another day - but as such it does not make a whole lot of sense).
It also possible that you are trying to operate against a view, and those tend to be even more problematic, since once again access will see such views as read only. And this again is due to the views not having a defined PK row. (in fact there is NO way to even define a pk for a view in SQL server. So, what occurs when you link to a view is a prompt appears, and you have to selected the PK row. Once you linked the view with a PK "set", then again you are able to update.
So, I would spend the whole 1.5 minutes, link to that table, and see if you can edit that row in question. If you can, then you of course can then delete that linked table and go back to testing your code. But, if you can't update rows with that simple linked table, then you not going to have any greater success attempting the same with VBA code to do the same.
so does the 1.5 minute test of linking a table and trying to update a row by simple click on the linked table and trying to edit a row work?
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Try linking to the one table from access. Then simple click on that linked table, and see if you can edit or update the row.
its possible that the table does not have a PK, and thus Access can't as a general rule update such linked tables, and thus you in code not using a linked table would also have the same issue. As for not using linked tables? (well, we will just leave the why not for another day - but as such it does not make a whole lot of sense).
It also possible that you are trying to operate against a view, and those tend to be even more problematic, since once again access will see such views as read only. And this again is due to the views not having a defined PK row. (in fact there is NO way to even define a pk for a view in SQL server. So, what occurs when you link to a view is a prompt appears, and you have to selected the PK row. Once you linked the view with a PK "set", then again you are able to update.
So, I would spend the whole 1.5 minutes, link to that table, and see if you can edit that row in question. If you can, then you of course can then delete that linked table and go back to testing your code. But, if you can't update rows with that simple linked table, then you not going to have any greater success attempting the same with VBA code to do the same.
so does the 1.5 minute test of linking a table and trying to update a row by simple click on the linked table and trying to edit a row work?
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
0 new messages