Operation must be an updateable query

[ano1optimist]

I have been running these queries in Access 2000 with no problems.
This week, I had to install Access 2003 to create some runtime
versions for another application, and now I keep getting "operation
must be an updateable query" messages when I try to run the same old
queries. Here is a sample of one that is no longer working:

UPDATE Personnel INNER JOIN dbo_personnel ON [Personnel].[p#]=
dbo_personnel.[empno] SET personnel.[ss#] = dbo_personnel.[ssn],
Personnel.Schedule = dbo_personnel.[workschedule], Personnel.Bureau =
dbo_personnel.[DEPTNAME], Personnel.Classification =
dbo_personnel.[CLASSDESC], personnel.sectiondetail =
dbo_personnel.[Section], personnel.LastName = trim( LEFT(
dbo_personnel.[NAME],InStr( dbo_personnel.[NAME],',')-1)),
personnel.FirstName = trim(MID( dbo_personnel.[NAME],INSTR(
dbo_personnel.[NAME],',')+1)), personnel.classcode =
dbo_personnel.classcode, personnel.paygroup = dbo_personnel.paygroup,
personnel.hiredate = DateValue(Left( [dbo_personnel].[hiredate],4) &
"/" & Mid( [dbo_PERSONNEL].[hiredate],5,2) & "/" & Right(
[dbo_personnel].[hiredate],2))
WHERE (( dbo_personnel.EMPNO<>"P0") and (
dbo_personnel.EMPNO<>"P00000")) AND
(
(nz(PERSONNEL.LASTNAME,'UNKNOWN') <> nz(LEFT(
dbo_personnel.[NAME],InStr( dbo_personnel.[NAME],',')-1),'UNKNOWN'))
or
(nz(PERSONNEL.FIRSTNAME,'UNKNOWN') <> nz(MID(
dbo_personnel.[NAME],INSTR( dbo_personnel.[NAME],',')+1),'UNKNOWN'))
or
(nz(personnel.[SS#],'UNKNOWN')<> nz( dbo_personnel.[SSN],'UNKNOWN'))
OR
(nz(personnel.classcode,'UNKNOWN') <> nz(
dbo_personnel.classcode,'UNKNOWN')) or
(nz(personnel.classification,'UNKNOWN') <> nz(
dbo_personnel.classdesc,'UNKNOWN')) OR
(nz(personnel.sectiondetail,'UNKNOWN') <> nz(
dbo_personnel.section,'UNKNOWN')) or
(nz(personnel.bureau,'UNKNOWN') <> nz(
dbo_personnel.deptname,'UNKNOWN')) or
(nz(personnel.paygroup,'UNKNOWN') <> nz(
dbo_personnel.paygroup,'UNKNOWN')) or

(nz(personnel.schedule,'UNKNOWN') <> nz(
dbo_personnel.workschedule,'UNKNOWN'))
)
WITH OWNERACCESS OPTION;
Personnel and Dbo_personnel are both tables. The queries themselves
have not changed. Something in Access changed but I don't know where
to start. Has anyone seen this happen before?

[Sherwood Wang]

"ano1optimist" <an**********@aol.com> wrote in message
news:25**************************@posting.google.c om...

I have been running these queries in Access 2000 with no problems.
This week, I had to install Access 2003 to create some runtime
versions for another application, and now I keep getting "operation
must be an updateable query" messages when I try to run the same old
queries. Here is a sample of one that is no longer working:

UPDATE Personnel INNER JOIN dbo_personnel ON [Personnel].[p#]=
dbo_personnel.[empno] SET personnel.[ss#] = dbo_personnel.[ssn],
Personnel.Schedule = dbo_personnel.[workschedule], Personnel.Bureau =
dbo_personnel.[DEPTNAME], Personnel.Classification =
dbo_personnel.[CLASSDESC], personnel.sectiondetail =
dbo_personnel.[Section], personnel.LastName = trim( LEFT(
dbo_personnel.[NAME],InStr( dbo_personnel.[NAME],',')-1)),
personnel.FirstName = trim(MID( dbo_personnel.[NAME],INSTR(
dbo_personnel.[NAME],',')+1)), personnel.classcode =
dbo_personnel.classcode, personnel.paygroup = dbo_personnel.paygroup,
personnel.hiredate = DateValue(Left( [dbo_personnel].[hiredate],4) &
"/" & Mid( [dbo_PERSONNEL].[hiredate],5,2) & "/" & Right(
[dbo_personnel].[hiredate],2))
WHERE (( dbo_personnel.EMPNO<>"P0") and (
dbo_personnel.EMPNO<>"P00000")) AND
(
(nz(PERSONNEL.LASTNAME,'UNKNOWN') <> nz(LEFT(
dbo_personnel.[NAME],InStr( dbo_personnel.[NAME],',')-1),'UNKNOWN'))
or
(nz(PERSONNEL.FIRSTNAME,'UNKNOWN') <> nz(MID(
dbo_personnel.[NAME],INSTR( dbo_personnel.[NAME],',')+1),'UNKNOWN'))
or
(nz(personnel.[SS#],'UNKNOWN')<> nz( dbo_personnel.[SSN],'UNKNOWN'))
OR
(nz(personnel.classcode,'UNKNOWN') <> nz(
dbo_personnel.classcode,'UNKNOWN')) or
(nz(personnel.classification,'UNKNOWN') <> nz(
dbo_personnel.classdesc,'UNKNOWN')) OR
(nz(personnel.sectiondetail,'UNKNOWN') <> nz(
dbo_personnel.section,'UNKNOWN')) or
(nz(personnel.bureau,'UNKNOWN') <> nz(
dbo_personnel.deptname,'UNKNOWN')) or
(nz(personnel.paygroup,'UNKNOWN') <> nz(
dbo_personnel.paygroup,'UNKNOWN')) or

(nz(personnel.schedule,'UNKNOWN') <> nz(
dbo_personnel.workschedule,'UNKNOWN'))
)
WITH OWNERACCESS OPTION;
Personnel and Dbo_personnel are both tables. The queries themselves
have not changed. Something in Access changed but I don't know where
to start. Has anyone seen this happen before?

is sql server back end yes? include key field in query for update to be OK
please.

*Sherwood Wang MVP*

[ano1optimist]

There is a front end application, back end application (tables only),
an Archive database and a workgroup security file. All are in Access
2000 format, no SQL tables, except one linked table. One other unique
thing I noticed is that I'm having difficulty compacting and repairing
one of the access .mdb files all of a sudden. I normally compact and
repair all of the files, including the system.mdw file. This week, I
get a message saying that it cannot complete the process because the
system.mdw is read only. I don't understand this. I can open the
system.mdw file and make changes to it... as I can do with all of the
rest of the databases. How would a database become read-only? I've
checked the file properties of all the files and read-only is not
checked anywhere.

Really appreciate your help.

[Sherwood Wang]

"ano1optimist" <an**********@aol.com> wrote in message
news:25**************************@posting.google.c om...

There is a front end application, back end application (tables only),
an Archive database and a workgroup security file. All are in Access
2000 format, no SQL tables, except one linked table. One other unique
thing I noticed is that I'm having difficulty compacting and repairing
one of the access .mdb files all of a sudden. I normally compact and
repair all of the files, including the system.mdw file. This week, I
get a message saying that it cannot complete the process because the
system.mdw is read only. I don't understand this. I can open the
system.mdw file and make changes to it... as I can do with all of the
rest of the databases. How would a database become read-only? I've
checked the file properties of all the files and read-only is not
checked anywhere.

Really appreciate your help.

many time file is copy from CD so is read only. database not become read
only by magic but by people. open exclusive mode not same as read only OK?
check please MDW make sure is MDW YOU USE. is possible you get confusion
with MDW use wrong MDW.

i no "dbo" is default prefix for sql table! so you query is use sql table
OK? if true put key in query to update.

do get all SP update from Microsoft please. better to go check all things
for corruption 2.

*Sherwood Wang MVP*

[ano1optimist]

I originally thought this had something to do with the fact that I was
receiving an odd message when trying to compact and repair my
databases as I usually do every other week. I got the message
"Microsoft Access can't delete ... system.mdw after compact and
repair. The compacted database has been named ...db1.mdb" It tells
you to remove the read-only status, delete the original database and
then rename the original one or to inform your administrator if you
can not remove the read-only status. I found an archive attribute set
on the databases, not sure how it happened but it was there. So,
after removing it, I was able to compact and repair all databases
without any error messages. However, when I try to run these same
queries that I have been running for months, I get the message that
you are about to run an update query and then get the error message
stating that the "operation must be an updateable query". I am
updating the "personnel" access table using the SQL table
dbo_personnel. I am only reading from the dbo_personnel table, not
updating it. I have refreshed the links on all of my tables but
continue to get this error message. I'm really stumped. Any help is
appreciated.

[Sherwood Wang]

"ano1optimist" <an**********@aol.com> wrote in message
news:25**************************@posting.google.c om...

I originally thought this had something to do with the fact that I was
receiving an odd message when trying to compact and repair my
databases as I usually do every other week. I got the message
"Microsoft Access can't delete ... system.mdw after compact and
repair. The compacted database has been named ...db1.mdb" It tells
you to remove the read-only status, delete the original database and
then rename the original one or to inform your administrator if you
can not remove the read-only status. I found an archive attribute set
on the databases, not sure how it happened but it was there. So,
after removing it, I was able to compact and repair all databases
without any error messages. However, when I try to run these same
queries that I have been running for months, I get the message that
you are about to run an update query and then get the error message
stating that the "operation must be an updateable query". I am
updating the "personnel" access table using the SQL table
dbo_personnel. I am only reading from the dbo_personnel table, not
updating it. I have refreshed the links on all of my tables but
continue to get this error message. I'm really stumped. Any help is
appreciated.

goto ms knowlede base. do search on "operation must be an updateable query"
is many suggestions there for problem. i hope you get good luk!

*Sherwood Wang MVP*

[Sherwood Wang]

"ano1optimist" <an**********@aol.com> wrote in message
news:25**************************@posting.google.c om...

I originally thought this had something to do with the fact that I was
receiving an odd message when trying to compact and repair my
databases as I usually do every other week. I got the message
"Microsoft Access can't delete ... system.mdw after compact and
repair. The compacted database has been named ...db1.mdb" It tells
you to remove the read-only status, delete the original database and
then rename the original one or to inform your administrator if you
can not remove the read-only status. I found an archive attribute set
on the databases, not sure how it happened but it was there. So,
after removing it, I was able to compact and repair all databases
without any error messages. However, when I try to run these same
queries that I have been running for months, I get the message that
you are about to run an update query and then get the error message
stating that the "operation must be an updateable query". I am
updating the "personnel" access table using the SQL table
dbo_personnel. I am only reading from the dbo_personnel table, not
updating it. I have refreshed the links on all of my tables but
continue to get this error message. I'm really stumped. Any help is
appreciated.

goto ms knowlede base. do search on "operation must be an updateable query"
is many suggestions there for problem. i hope you get good luk!

*Sherwood Wang MVP*