Operation must use an updateable query

[RL]

When I run an update query within Microsoft Access 2007, the following error
message appears:
'Operation must use an updateable query'.

Background:

The update query is part of a tool with an Access 2007 front end. The tool
contains several links to tables containing the underlying data. These tables
used to be in another Access 2007 database but I am in the process of moving
the underlying data from Access 2007 to SQL Server 2005.

So far I have recreated the tables in SQL Server 2005 and redirected the
links to the equivalent SQL tables.

I am logged into Windows as a user with administrator rights. The ODBC to
the SQL database connects using Windows authentication. My Windows login name
is associated with the dbo user in the datatbase that I am linking to.

The update query attempts to update one field for various records of one
table.

Any ideas what I'm doing wrong?

 

[John Spencer]

Does the table in SQL server have a field of the type TIMESTAMP in it?

In most cases, the table must have such a field if you are going to UPDATE
the table using an ODBC connection from Access.

IF all the data exists on the SQL Server, you could try using a pass through
query or just do the update in on the server using T-SQL.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[RL]

Thanks John.

Some of the data exists in Access and some of the data is stored in SQL
Server.

Each user will have a local copy of the Access front end, which essentially
contains cache tables where anything the user does is temporariliy stored.
Certain actions trigger update queries which move the data from the cache
tables and updates the server data.

Does that rule out the second option?

In response to your first query, no the SQL server table does not currently
contain a timestamp field. If I add that as an additional field within the
table that I'm trying to update, do you think that would resolve the issue?

RL

 

[John Spencer]

Yes, the second option seems to be ruled out unless you construct the pass
through query something like

UPDATE TableName
SET SomeField = 'xxx'
WHERE tableName PK = 123

I would add the TimeStamp field to every table on the server. That should
take care of the problem. I can't say that it will as I have NOT tested
Access 2007 running against any version of SQL server.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Skip]

When I run an update query within Microsoft Access 2007, the following
error
message appears:
'Operation must use an updateable query'.

There is a lot of discussion on this error on the web. As usual, the advice
varies wildly. Having said that, I have had luck setting the Unique
Records property for the query to Yes as shown below.

(Below are from notes I collected around the web):

RESOLUTION

Set the UniqueRecords property of the query to Yes. To set the UniqueRecords
property of the query to Yes, follow these steps:

1. Open the delete query in Design view.

2. On the View menu, click Properties.

3. Set the UniqueRecords property to Yes.

4. Save the query, and then close it.



If that doesn't work:

This error occurs when the current query's Update To row includes a field
from either a crosstab query or select query in which an aggregate(total)
was calculated for the field (using either the Totals row or a domain
function in the Field row). To update a field using the aggregate of another
field, calculate the aggregate in the update query itself, not a different
query.

Skips note: --- OR dump it to a table first, then use the table.

 

[Skip]

opps.. note that the steps below refer to a 'delete' query, but it also
applies for an Update query (I had researched the same error for both types
of queries)..

 

[Skip]

And just in case you want further reading, check this out

http://support.microsoft.com/kb/328828

(I need to wait 20 minutes before posting, so I can just make one post..)

 

[RL]

Thank you very much skip. I've been through each of your suggestions and the
suggestions from the link that you provided. I was optimistic because quite a
few of them highlighted issues where I had to make a change, however I am
still getting the same error!!

The following gives a response to each of that link's suggestions of what
may be causing the problem:

When the query is based on three or more tables and there is a
many-to-one-to-many relationship, you cannot update the data directly in the
query. You can update the data in a form or in a data access page. You can do
this based on the query when the RecordsetType property of the form is set to
Dynaset (Inconsistent Updates).
The query was originally based on 3 tables, with a one-to-many-relationship
between each. I have changed that by doing a make table and a subsequent
update query. Now the update query only requires two tables with an inner
join connecting them.

When the query is a crosstab query, you cannot update the data in the query.
The query is NOT a crosstab query.

When the query is a Microsoft SQL pass-through query, you cannot update the
data in the query.
Not sure whether the query is a Microsoft SQL pass-through query. The table
I am trying to update is an SQL table. The data I am updating the SQL table
with is retrieved from an Access table. Does that make this a Microsoft SQL
pass through query?!

When the query is calculating a sum, an average, a count, or other type of
total on the values in a field, you cannot update data in the query. Also,
you cannot update a query that references a field in the Update To row from a
crosstab, a query, a select query, or a subquery that contains totals or
aggregate functions. To work around this problem, use the Domain Aggregate
function in the Update To row of an update query. You can reference fields
from a crosstab query, a select query, or a subquery that contain totals or
aggregate functions.
The update query contains no aggregation.

When the query is a Union query, you cannot update data in the query.
It is not a Union query.

When the Unique Values property of the query is set to Yes, you cannot
update data in the query. To work around this problem, set the Unique Values
property of the query to No.
The Unique Values property was set to Yes but I have reverted that back to No

When the query includes a linked ODBC table with no unique index or a
Paradox table without a primary key, you cannot update data in the query. To
work around this problem, add a primary key or a unique index to the linked
table.
The linked table did not have a unique index or a primary key. I have now
added an ID field, which is both a unique index and a primary key. That is
not however the field I have used to link to the Access table. Should it be?

When you do not have Update Data permissions for the query or the underlying
table, you cannot update data. To resolve this problem, assign permissions to
update the data.
My login is associated with the dbo user name, which owns the
db_accessadmin, db_datareader and db_datawriter schemas.
Whenever I try to add any of the database role memberships, I get the
following error message saying “Add member failed… An exception occurred
while executing a Transact-SQL statement or batch. Cannot use the special
principal ‘dbo’. As such, I do not have any role memberships.
I do have Windows administrator rights (not sure if that has any value).
Will any of those permissions be causing the error message?

When the query includes more than one table or one query, and the tables or
the queries are not joined by a join line in Design view, you cannot update
data in the query. To resolve this problem, you must join the tables
correctly so you can update them.
The two tables are joined with one inner join.

When the field that you want to update is a calculated field, you cannot
update data in the query.
The update field is not a calculated field.

When the field that you try to update is read-only, the database is open as
read-only, or the database is located on a read-only drive, you cannot update
data in the query. To avoid this problem, do not open the database as
read-only. If the database is located on a drive that is read-only, remove
the read-only attribute from the drive or move the database to a drive that
is not read-only.
I have given all users alter, update, delete, insert, and select permissions
on the database and the relevant table. I have also removed the ‘Read Only’
flag on the Microsoft SQL Server directory and all underlying folders that
lead to the relevant database, as well as the database itself.

When the field in the record that you try to update is deleted or is locked
by another user, you cannot update data in the query. A locked record can be
updated as soon as the record is unlocked.
No one else has a copy of the front end yet, which is the only link to the
underlying database.


Any more thoughts as to where I'm going wrong?!

 

[John Spencer]

You might consider posting the SQL of the query that is failing. Sometimes
that helps people to spot a problem.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[RL]

Good call. This is the update query:

UPDATE [tBusiness Information]
INNER JOIN [tCurrent Status]
ON [tBusiness Information].[Company Name] = [tCurrent Status].[Company]
SET [tBusiness Information].[Current Stage] = [tCurrent Status]![Stage];

For simplicity's sake, I have reduced the tCurrent Status to one record
only. tBusiness Information still has thousands though.

BTW. I was originally under the impression that it was a database
permissions issue, but I have been able to do Access append queries to other
tables within the same SQL database. So...if it is a permissions issue, it is
either table specific or update specific.

 

[Skip]

Sorry to hear you still have the problem. I know it can be quite frustating.
I added my comments after a couple of your paragraphs.

When the query is a Microsoft SQL pass-through query, you cannot update
the
data in the query.
Not sure whether the query is a Microsoft SQL pass-through query. The
table
I am trying to update is an SQL table. The data I am updating the SQL
table
with is retrieved from an Access table. Does that make this a Microsoft
SQL
pass through query?!

Probably not. If it is a pass-through query, it's icon in the query pane
will be a globe (instead of the standard query icon). Also, you would only
be able to open it in SQL view instead of design view. (Also, to create a
pass-through query, you must specify it in the query designer by selecting
[Query],[SQL Specific],[Pass-Through]).

When the query includes a linked ODBC table with no unique index or a
Paradox table without a primary key, you cannot update data in the query.
To
work around this problem, add a primary key or a unique index to the
linked
table.
The linked table did not have a unique index or a primary key. I have now
added an ID field, which is both a unique index and a primary key. That is
not however the field I have used to link to the Access table. Should it
be?

This may be it. Did you refresh your links after you created your primary
key? Any time the schema for a linked table is changed, you need to refresh
the link to that table in your Access database (or the change is not seen).
With your table pane open, select [Tools],[Database Utilities], [Linked
Table Manager]. Once open, click [Select All] and finally [OK]. You should
get a message indicating all links were refreshed.

I hope this helps. I once resorted to changing the offending query into a
create-table query, which dumped the data into a temporary table. I then ran
the final update based on this table (instead of using the non-updatable
query). Pretty cludgy, but it was done with vba code so wasnt visible to the
user.

 

[RL]

Have been refreshing the links after every change to date, and the update
query is already updating the SQL table from an access table which was
created by a make table (if that makes sense)!

Think it's nearly time to give up! One last thing though...

I have been able to run the exact same update query in SQL using the
following script:
UPDATE [tBusiness Information]
SET [tBusiness Information].[Current Stage] = [tCurrent Status].[Status]
FROM [tCurrent Status]
WHERE [tBusiness Information].[Company Name] = [tCurrent Status].[Company]

If I save that as a stored procedure, is there any way I can call the stored
procedure either as an access macro, or using VBA? If so, have you any idea
how to do that?!

Thanks for all your help.
Ryan

Sorry to hear you still have the problem. I know it can be quite frustating.
I added my comments after a couple of your paragraphs.

When the query is a Microsoft SQL pass-through query, you cannot update
the
data in the query.
Not sure whether the query is a Microsoft SQL pass-through query. The
table
I am trying to update is an SQL table. The data I am updating the SQL
table
with is retrieved from an Access table. Does that make this a Microsoft
SQL
pass through query?!

Probably not. If it is a pass-through query, it's icon in the query pane
will be a globe (instead of the standard query icon). Also, you would only
be able to open it in SQL view instead of design view. (Also, to create a
pass-through query, you must specify it in the query designer by selecting
[Query],[SQL Specific],[Pass-Through]).

When the query includes a linked ODBC table with no unique index or a
Paradox table without a primary key, you cannot update data in the query.
To
work around this problem, add a primary key or a unique index to the
linked
table.
The linked table did not have a unique index or a primary key. I have now
added an ID field, which is both a unique index and a primary key. That is
not however the field I have used to link to the Access table. Should it
be?

This may be it. Did you refresh your links after you created your primary
key? Any time the schema for a linked table is changed, you need to refresh
the link to that table in your Access database (or the change is not seen).
With your table pane open, select [Tools],[Database Utilities], [Linked
Table Manager]. Once open, click [Select All] and finally [OK]. You should
get a message indicating all links were refreshed.

I hope this helps. I once resorted to changing the offending query into a
create-table query, which dumped the data into a temporary table. I then ran
the final update based on this table (instead of using the non-updatable
query). Pretty cludgy, but it was done with vba code so wasnt visible to the
user.

 

[RL]

No worries guys. It's all sorted now. I have used a pass through query to
execute a SQL stored procedure which does the update query that I was
initially trying to do in Access. It actually simplifies the front end quite
alot, because one stored procedure does what I was having to do with two
delete queries, two append queries and an update query in Access. So happy
days!

Cheers for your help, both of you!

Have been refreshing the links after every change to date, and the update
query is already updating the SQL table from an access table which was
created by a make table (if that makes sense)!

Think it's nearly time to give up! One last thing though...

I have been able to run the exact same update query in SQL using the
following script:
UPDATE [tBusiness Information]
SET [tBusiness Information].[Current Stage] = [tCurrent Status].[Status]
FROM [tCurrent Status]
WHERE [tBusiness Information].[Company Name] = [tCurrent Status].[Company]

If I save that as a stored procedure, is there any way I can call the stored
procedure either as an access macro, or using VBA? If so, have you any idea
how to do that?!

Thanks for all your help.
Ryan

Sorry to hear you still have the problem. I know it can be quite frustating.
I added my comments after a couple of your paragraphs.

When the query is a Microsoft SQL pass-through query, you cannot update
the
data in the query.
Not sure whether the query is a Microsoft SQL pass-through query. The
table
I am trying to update is an SQL table. The data I am updating the SQL
table
with is retrieved from an Access table. Does that make this a Microsoft
SQL
pass through query?!

Probably not. If it is a pass-through query, it's icon in the query pane
will be a globe (instead of the standard query icon). Also, you would only
be able to open it in SQL view instead of design view. (Also, to create a
pass-through query, you must specify it in the query designer by selecting
[Query],[SQL Specific],[Pass-Through]).

When the query includes a linked ODBC table with no unique index or a
Paradox table without a primary key, you cannot update data in the query.
To
work around this problem, add a primary key or a unique index to the
linked
table.
The linked table did not have a unique index or a primary key. I have now
added an ID field, which is both a unique index and a primary key. That is
not however the field I have used to link to the Access table. Should it
be?

This may be it. Did you refresh your links after you created your primary
key? Any time the schema for a linked table is changed, you need to refresh
the link to that table in your Access database (or the change is not seen).
With your table pane open, select [Tools],[Database Utilities], [Linked
Table Manager]. Once open, click [Select All] and finally [OK]. You should
get a message indicating all links were refreshed.

I hope this helps. I once resorted to changing the offending query into a
create-table query, which dumped the data into a temporary table. I then ran
the final update based on this table (instead of using the non-updatable
query). Pretty cludgy, but it was done with vba code so wasnt visible to the
user.

 

[Skip]

Great, glad you got it working!

No worries guys. It's all sorted now. I have used a pass through query to
execute a SQL stored procedure which does the update query that I was
initially trying to do in Access. It actually simplifies the front end
quite
alot, because one stored procedure does what I was having to do with two
delete queries, two append queries and an update query in Access. So happy
days!

Cheers for your help, both of you!

Have been refreshing the links after every change to date, and the update
query is already updating the SQL table from an access table which was
created by a make table (if that makes sense)!

Think it's nearly time to give up! One last thing though...

I have been able to run the exact same update query in SQL using the
following script:
UPDATE [tBusiness Information]
SET [tBusiness Information].[Current Stage] = [tCurrent Status].[Status]
FROM [tCurrent Status]
WHERE [tBusiness Information].[Company Name] = [tCurrent
Status].[Company]

If I save that as a stored procedure, is there any way I can call the
stored
procedure either as an access macro, or using VBA? If so, have you any
idea
how to do that?!

Thanks for all your help.
Ryan

Sorry to hear you still have the problem. I know it can be quite
frustating.
I added my comments after a couple of your paragraphs.


When the query is a Microsoft SQL pass-through query, you cannot
update
the
data in the query.
Not sure whether the query is a Microsoft SQL pass-through query. The
table
I am trying to update is an SQL table. The data I am updating the SQL
table
with is retrieved from an Access table. Does that make this a
Microsoft
SQL
pass through query?!

Probably not. If it is a pass-through query, it's icon in the query
pane
will be a globe (instead of the standard query icon). Also, you would
only
be able to open it in SQL view instead of design view. (Also, to create
a
pass-through query, you must specify it in the query designer by
selecting
[Query],[SQL Specific],[Pass-Through]).


When the query includes a linked ODBC table with no unique index or a
Paradox table without a primary key, you cannot update data in the
query.
To
work around this problem, add a primary key or a unique index to the
linked
table.
The linked table did not have a unique index or a primary key. I have
now
added an ID field, which is both a unique index and a primary key.
That is
not however the field I have used to link to the Access table. Should
it
be?

This may be it. Did you refresh your links after you created your
primary
key? Any time the schema for a linked table is changed, you need to
refresh
the link to that table in your Access database (or the change is not
seen).
With your table pane open, select [Tools],[Database Utilities], [Linked
Table Manager]. Once open, click [Select All] and finally [OK]. You
should
get a message indicating all links were refreshed.

I hope this helps. I once resorted to changing the offending query
into a
create-table query, which dumped the data into a temporary table. I
then ran
the final update based on this table (instead of using the
non-updatable
query). Pretty cludgy, but it was done with vba code so wasnt visible
to the
user.