Update problem.....

[cdnamm]

Hi everyone,
I want to use MS Access 2000 as a front-end to update and delete data from Microsoft SQL Serer 7.0 but I couldn't. Here is the error message from MS Access 2000:

"Operation must use an updateable query" or
"Cannot delete the specified table"

Could anyone help me with this?
Thank you very much!!!
cdnamm

 

[tlbroadbent]

The messages mean what they say.

1) You are attempting to update by using a query that is not updatable.
2) You are trying to delete or drop a table that you have no permissions to delete.

What is the security setup on SQL Server. Does the login used for SQL Server have appropriate permissions in the databse? The DBA or System Administrator should set up the permissions. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cdnamm]

Oh,
I am very sorry that I didn't make it clear on my
question. We have the full rights (even as DBA rights),
but still we couldn't do it. I have also searched on MSDN and it said that we have to have MS SQL Server 7.0 Service Pack 3 installed to solve that problem but it never did when we actually have the SP3 installed.
Thanks!!!
cdnamm

 

[tlbroadbent]

Can you post the queries that are failing? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cdnamm]

Hi,
It's just any simple update query that update one field
in a table...
Thanks!!!
cdnamm

 

[SQLBill]

Can you still post it? Sometimes the simplest query still has errors that the programmer misses, but someone else catches. I know I've had that happen.

-SQLBill

 

[cdnamm]

Hi,
I use Access 2000 to generate the query and it's just to
update one field in a table...
Thanks!!!

 

[SQLBill]

It's possible that Access might not be formating the query properly for SQL Server. They both use SQL, but Access SQL and SQL Server SQL aren't really the same language. It's like English....there's British English, American English, American Southern English, Texan English, etc. It's all English, but there's enough differences that two people speaking English could mis-understand each other.

-SQLBill

 

[cdnamm]

Is there a solution to this problem?
thanks!

 

[tlbroadbent]

The message says you are not using an updateable query so your query must not be simple as you say. If your query is so simple then the permissions are not correct for the connection.

Are you attempting to delete rows from a table or drop a table? In either case, SQL says that you don't have permissions to do it. Your explanation doesn't all fit together. Unless you can clarify things we cannot help you. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

There probably is, but we can't see your code so we can't say what the issue might be.

-SQLBill

___________________________________________
SELECT forum
CASE sqltype
WHEN 'MySQL' THEN 'forum436'
WHEN 'ORACLE' THEN 'forum185', 'forum186', 'forum759'
WHEN 'MS Access' THEN 'forum700', 'forum701', 'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'MS SQL Server' THEN 'This Forum!!!'
ELSE 'Use search to find proper forum'
FROM TEK-TIPS

 

[cdnamm]

Hi tlbroadbent?
Have you ever used Access to update and delete from MS SQL Server 7? I wanted to ask because I hoping to ask someone that ran into the same problem as the one I have right now. I am pretty sure that it's not the permissions issue because
I have full rights. It's ok that you don't know the answer to the problem. Thanks for your help!!!
cdnamm

 

[tlbroadbent]

I frequently used Access with SQL 7 and now with SQL 2000. I have dealt with the error "Operation must use an updateable query" numerous times. In order to update a SQL table from Access certain conditions must be met.

1) You must have permissions.
2) The SQL table must have a unique index, priamry key, identity column, or timestamp column so that the record can be uniquely identified. However, if this condition is not met a differnt error message usually results.
3) You cannot update an aggregate query.
4) You cannot update derived columns.

Do any of these conditions apply?

I don't know the answer to your problem because you have not provided adequate info for us to help you solve the problem. You won't even tell us what you are doing when you get the error message, "Cannot delete the specified table." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cdnamm]

Hi,
I think you just helped me solve the problem because the table we created didn't have a primary key or unique index.
Thank you very much!