Delete Query Error (2000)

Topic

New Reply

I have a problem with executing delete queries. I join two tables or querys with one join run the query as a select query and then change the type to delete. I have been able to execute other delete queries using the same process, and I am getting the results I want in the select query…The error I keep getting is that it is a “read only” database error 3086. Any suggestions?? Thanks

Reply | Quote

Replies

Well, do you have more than one copy of the database open? That can sometimes cause a read-only copy to be opened the second time. Try editing a record in each table to see if it lets you. Otherwise, usually setting the Unique Records property to true takes care of Delete query issues.

Reply | Quote

Charlotte, Thanks this did the trick. I am surprised it was this simple as I had two Access programers here at the College who could not figure it out! I was certain that it was a more complex problem

Reply | Quote

Next time you encounter the problem, YOU can be the expert who knows how to solve it! Psst! Out of curiousity, which one was it–multiple sessions of the database or needing the Unique Records property set (DISTINCTROW in the SQL)?

Reply | Quote

Charlotte,
Would you tell me why you need the Distinctrow set?
I have had similar problems in the past with joined tabled delete queries.
Pat

Reply | Quote

There was a change in the query engine between Access 97 and Access 2000. The A97 engine assumed DISTINCTROW and the A2k engine doesn’t. I tripped over that when I first started using A2k and discovered that A97 delete queries didn’t work properly … along with some other queries that I had to redesign.

Reply | Quote

Thanks. From memory I have also had problems with update queries.

Reply | Quote

May be the same issue. I had the biggest problem with append queries with multiple tables in the query. In A97, you left out the PKs for tables that you didn’t want records appended to but needed for filtering or to retrieve values or insert keys populated by referential integrity. In A2k, you have to include *all* the required fields for all the tables or it refuses to cooperate. It is very intelligent about not trying to add records to those tables, however, which A97 wasn’t.

Reply | Quote

Yes, I will be the expert, I am actually really enjoying Access and all it can do! The problem was the Unique Records property. I had thought of the problem of having multipule sessions. Thanks again.

Reply | Quote