Operation must use an updatable query. (Error 3073)

burakbaysal
burakbaysal used Ask the Experts™
on
I work in an Access 97 Database and there are many tables which are linked with SQL Server. Until 2 days the following code worked fine, but suddenly it begins to give Run-time Error 3073. Both Tables are used in the "qry_frmMonthlyPolicy_assistance_append" query and they need to have no records. They have no primary key and when I try to delete records from them manually from Access I couldn't do it. I could only delete records from them from SQL Server side. I didn't open database in read-only mode (I can delete records from other linked tables) and I have permission to delete them.
Could please someone help me?
Thanks

Code

Private Sub Button12_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tmpMonthlyRiskValue" --- Problem is in this line---
DoCmd.RunSQL "delete from rptMonthlyRiskValue" --- Problem is in this line---
DoCmd.OpenQuery "qry_frmMonthlyPolicy_assistance_append"
DoCmd.SetWarnings True
MonthlyPolicy
DoCmd.OpenReport "rptMonthlyPolicy_assistance", acViewPreview

Error Message:

Operation must use an updatable query. (Error 3073)

You tried to run, open, or modify a query that isn't updatable.
Possible causes:

•      You attempted to run a query that tried to update a field that can't be updated. For example, you may have created the query in such a way that you tried to update a field on the one side of a one-to-many relationship.
•      
You tried to use the obsolete OpenQueryDef method on a query that is in a database opened for read-only access.

The database is read-only for one of the following reasons:

•      You used the OpenDatabase method or the Visual Basic Data control, and opened the database for read-only access.
•      
The database file has been defined as read-only in your network operating system.
•      
In a network environment, you don't have write privileges for the database file.

Close the database, resolve the read-only condition, and then reopen it for read/write access.

•      You don't have permission to make changes to the query. To change your permission assignments, see your system administrator or the query's creator.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Sounds like a corruption of some sort.
I would try the following:
1. Compress/reindex Access
and/or
2. Delete the tables from access and relink them.
and/or
3. Delete the tables from SQL and Readd / Relink them.
and/or
4. Create a new database and import everything into the new database from the old database. (including specs/menus etc)

Author

Commented:
The Problem get solved when I deleted the tables and relinked them from SQL Server. I also give them Primary Keys. I still don't know the reason but the problem is solved.
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial