Query recordset is locked

[TJBernard]

I have a database in which the tables are located in one MS Access file, and the queries, forms, reports, etc. are all located in a seperate MS Access file (MS Access 2003). The MS Access file that stores the tables, I call the "back end file".

I got a call regarding the database, and found that some how the back-end file got corrupted. When I want to open the file, I got the error...

"Database is not in a recognized format.... You must repair the file".

So I clicked "Yes" to compact and repair the back-end file, and the database worked correctly for me.

All except one query. I have a query that pulls in the main table of the database and then one of the subform queries. Linked on the main table primary key, it is a pretty simple query. This query has always been used to update data for the sub-form.

Today, after the compact and repair job, this query became a locked record set. I cannot add, edit or delete from the records in this query.

I can add, edit and delete from both the tables in the query, just not from the query itself. To make things even more strange, when I try to edit, I don't get the message at the bottom of the query stating "This recordset is locked".

I am just wondering if anyone has experienced this, and if there is something more trouble some brewing. If so, is there anyway to fix my query? Is my database back end file still corrupt (I have compact and repaired it since then). I even copied every table from the back-end file into a new MS Access database file, and I still get the same error with the query.

Very strange, and sort of a worry.

Let me know, and thank you for your time.

T.J.

 

[Dennisk]

try and regenerate the query by opening in SQL view, copy the SQL statement. Create a new query and paste the SQL into SQL view.

Queries are compiled and sometimes Access gets compiled code in a twist.

 

[TJBernard]

try and regenerate the query by opening in SQL view, copy the SQL statement. Create a new query and paste the SQL into SQL view.

Queries are compiled and sometimes Access gets compiled code in a twist.

I did not realize queries were compiled. I have tried your suggestion of copying the SQL into a new query SQL statement, and once again the query recordset is locked.

I then played with the SQL statement by taking out some of the fields in the select statement, and still the recordset is locked.

I am wondering if there is a deeper issue with the database, like it is corrupted, even after being compacted and repaired.

 

[Road_Warrior]

Check your relationships in the relationship window

Twice in the last 6 months Ive had a db corrupt and lost a join between a table and a sub table during the repair process.

 

[TJBernard]

Twice in the last 6 months Ive had a db corrupt and lost a join between a table and a sub table during the repair process.

Is there anyway to resolve the issue?

I found a work around for the current form structure, but I wonder if there is a true fix for the error.

Thanks!