Update Queries don't work in form

I have a form with queries that update several fields to null.
I split the database with the splitter in access and placed the backend (
the Tables) on a server running XP and the Front end ( Queries and Forms) on
a work station running Vista.

Now I go to run a query I get the message "Operation must use an Updateable
Query"

I pressed the Help Button and the only things applicable were the item
dealing with permissions I checked them on both the FrontEnd and BackEnd and
I have full control permissions for both.

I even created a new Update Query and it doesn't work.

Anyone have any ideas?

TIA
--
Ron

On Thu, 3 Jan 2008 08:27:03 -0800, Ron wrote:

I have a form with queries that update several fields to null.
I split the database with the splitter in access and placed the backend (
the Tables) on a server running XP and the Front end ( Queries and Forms) on
a work station running Vista.

Now I go to run a query I get the message "Operation must use an Updateable
Query"

I pressed the Help Button and the only things applicable were the item
dealing with permissions I checked them on both the FrontEnd and BackEnd and
I have full control permissions for both.

I even created a new Update Query and it doesn't work.

Not all queries are updateable. Perhaps you could open the query in SQL view
and post it here, along with an indication of what the Primary Key of each
table is, and what relationships are defined.

John W. Vinson [MVP]

here is the SQL for one of my Queries:

UPDATE SweetOrder SET SweetOrder.[Order] = No, SweetOrder.Qty = Null
WHERE (((SweetOrder.SKU) Is Not Null));

What I am doing is setting [Order], which is a Yes/No, to No and [Qty]
Which is an Integer, to Null, When the [SKU], which is text(6 Numbers), is
not Null.

This Table is not in any relationship.

This worked before I split the Database
--
Ron


"John W. Vinson" wrote:

On Thu, 3 Jan 2008 08:27:03 -0800, Ron wrote:

I have a form with queries that update several fields to null.
I split the database with the splitter in access and placed the backend (
the Tables) on a server running XP and the Front end ( Queries and Forms) on
a work station running Vista.

Now I go to run a query I get the message "Operation must use an Updateable
Query"

I pressed the Help Button and the only things applicable were the item
dealing with permissions I checked them on both the FrontEnd and BackEnd and
I have full control permissions for both.

I even created a new Update Query and it doesn't work.

Not all queries are updateable. Perhaps you could open the query in SQL view
and post it here, along with an indication of what the Primary Key of each
table is, and what relationships are defined.

John W. Vinson [MVP]

On Thu, 3 Jan 2008 21:19:01 -0800, Ron wrote:

here is the SQL for one of my Queries:

UPDATE SweetOrder SET SweetOrder.[Order] = No, SweetOrder.Qty = Null
WHERE (((SweetOrder.SKU) Is Not Null));

What I am doing is setting [Order], which is a Yes/No, to No and [Qty]
Which is an Integer, to Null, When the [SKU], which is text(6 Numbers), is
not Null.

This Table is not in any relationship.

This worked before I split the Database

That's bizarre! There's nothing whatsoever wrong with the query. In your split
database, do you in fact have the table named SweetOrder in the table list? If
you open the table datasheet directly is it updateable? Do you get any warning
or error message? What's the code which executes this query?

John W. Vinson [MVP]

Wekk I wasn't ready for that!!!
It says the recordset(The Table) is not updateable.

Now I am really confused. If everything was updateable before I split the
database, what happened to make them non-updateable????? And what do I do tp
get the tables updateable???

Thanks
--
Ron


"John W. Vinson" wrote:

On Thu, 3 Jan 2008 21:19:01 -0800, Ron wrote:

here is the SQL for one of my Queries:

UPDATE SweetOrder SET SweetOrder.[Order] = No, SweetOrder.Qty = Null
WHERE (((SweetOrder.SKU) Is Not Null));

What I am doing is setting [Order], which is a Yes/No, to No and [Qty]
Which is an Integer, to Null, When the [SKU], which is text(6 Numbers), is
not Null.

This Table is not in any relationship.

This worked before I split the Database

That's bizarre! There's nothing whatsoever wrong with the query. In your split
database, do you in fact have the table named SweetOrder in the table list? If
you open the table datasheet directly is it updateable? Do you get any warning
or error message? What's the code which executes this query?

John W. Vinson [MVP]

I went to my Trusted Locations and Trusted the files where the FE and BE are
located.
I went in to each table and only three out of 13 were updateable.

I can not find anything in Access that talks about or indicares any this
about how an updateable table is created.

There has to be something??????
--
Ron


"John W. Vinson" wrote:

On Thu, 3 Jan 2008 21:19:01 -0800, Ron wrote:

here is the SQL for one of my Queries:

UPDATE SweetOrder SET SweetOrder.[Order] = No, SweetOrder.Qty = Null
WHERE (((SweetOrder.SKU) Is Not Null));

What I am doing is setting [Order], which is a Yes/No, to No and [Qty]
Which is an Integer, to Null, When the [SKU], which is text(6 Numbers), is
not Null.

This Table is not in any relationship.
I went to my Trusted Choices and put the file where the database is on the trusted list. I went and opened the BackEnd and It is updateable. I went back to the database FrontEnd and none of
This worked before I split the Database

That's bizarre! There's nothing whatsoever wrong with the query. In your split
database, do you in fact have the table named SweetOrder in the table list? If
you open the table datasheet directly is it updateable? Do you get any warning
or error message? What's the code which executes this query?

John W. Vinson [MVP]

On Fri, 4 Jan 2008 17:41:02 -0800, Ron wrote:

I went to my Trusted Locations and Trusted the files where the FE and BE are
located.
I went in to each table and only three out of 13 were updateable.

I can not find anything in Access that talks about or indicares any this
about how an updateable table is created.

There has to be something??????

Have you Compacted the backend?
Have you implemented Access workgroup security on the database... and does
your account have privileges to these tables?

John W. Vinson [MVP]

No I have not compacted neither the front end nor the backend
No I have not implemebted Workgroup security

--
Ron


"John W. Vinson" wrote:

On Fri, 4 Jan 2008 17:41:02 -0800, Ron wrote:

I went to my Trusted Locations and Trusted the files where the FE and BE are
located.
I went in to each table and only three out of 13 were updateable.

I can not find anything in Access that talks about or indicares any this
about how an updateable table is created.

There has to be something??????

Have you Compacted the backend?
Have you implemented Access workgroup security on the database... and does
your account have privileges to these tables?

John W. Vinson [MVP]

John = Where are You???
--
Ron


"Ron" wrote:

No I have not compacted neither the front end nor the backend
No I have not implemebted Workgroup security

--
Ron


"John W. Vinson" wrote:

On Fri, 4 Jan 2008 17:41:02 -0800, Ron wrote:

I went to my Trusted Locations and Trusted the files where the FE and BE are
located.
I went in to each table and only three out of 13 were updateable.

I can not find anything in Access that talks about or indicares any this
about how an updateable table is created.

There has to be something??????

Have you Compacted the backend?
Have you implemented Access workgroup security on the database... and does
your account have privileges to these tables?

John W. Vinson [MVP]

Ron,
some suggestions: if you have already tried these, just post back and say
so.
Some queries can't be updated - for example if they use any group by clause
or distinct clause - have you checked this query for these 2 things?
If you change the query to a select query and switch to datasheet view, are
you able to add new records to it? If you can't then it is not updateable.

Jeanette Cunningham

"Ron" wrote in message
...
I have a form with queries that update several fields to null.
I split the database with the splitter in access and placed the backend (
the Tables) on a server running XP and the Front end ( Queries and Forms)
on
a work station running Vista.

Now I go to run a query I get the message "Operation must use an
Updateable
Query"

I pressed the Help Button and the only things applicable were the item
dealing with permissions I checked them on both the FrontEnd and BackEnd
and
I have full control permissions for both.

I even created a new Update Query and it doesn't work.

Anyone have any ideas?

TIA
--
Ron