Access is turning my db into a locked file

[ShovelinFishHeads]

Access is turning my db into a locked file, but I cant figure out why.

The db has never been split; it is not on a network.

Also, it looks like one subform is "locked" while other parts of the db are not. My guess is that there is something wrong with the tables and the queries that are "upstream" of this sub-form that is not set up correctly, but I can't see anything wrong with it.

I have tried closing the file and rebooting my laptop, but this didn't correct anything.

Any ideas?

 

[The_Doc_Man]

With regard to "Locked" sub-forms... A sub-form, just like a normal form, has properties such as AllowEdit, AllowInsert, AllowDelete, etc. Is there a way for you to put a breakpoint somewhere so that you can use either the debug window (to do debug.print statements to examine specific properties) or the locals window (to call up a tree-like diagram to let you see the properties)?

Also, a sub-form is a type of control that just so happens to contain another control, but there is nothing to stop the sub-form control itself from being locked. That's another property to test.

Sort of musing out loud here, if one form appears locked and other parts do not, and if the DB is not split, then it is neither the permissions on the folder or on the file itself. Permissions-level issues would affect everything. So that means it is specific to the tables involved. Do the forms directly open tables or do they open queries that open tables? (Because queries can be locked without locking the table.)

For experiment, make a copy of the DB and open it like you were debugging it. See if you have the same problem in the copy. Then see if the table on which the sub-form is based can be directly modified bypassing the form.

Have you tried a repair and compact operation?

Here is a thought for you... if the sub-form is based on a query and if the query was recently modified in a way to make it impossible to do a 1-to-1 mapping to underlying tables (perhaps because of an SQL aggregate function in one of the fields), that would do it. A direct update of a table shouldn't be able to do that, though...

But of course, if you created the table with a wizard, it is remotely possible that the wizard created a query for you. On the subform, what is the form's .Recordsource? Is it a table name, a predefined query's name, or an actual SQL statement?

 

[ShovelinFishHeads]

I just did a compact / repair and that had no effect.

The other items you mentioned are also very helpful and I am going through those now.

Subform is driven by a query that reads a table. I recently altered the table and query in order to set up a check box control on the form.

Maybe one thing I might try is swapping out the table and the query with those from an archived file.

 

[ShovelinFishHeads]

Oh....

And I also setup a "Salutations" lookup table that I used with the underlying table and query.

I'm going through the subform, query, and table removing that work now.

 

[AccessBlaster]

Open a new database copy everything into the new database, see if that helps.

 

[ShovelinFishHeads]

AccessBlaster,

That is another interesting idea.

I have the form working now and appears to work normally, but Access is saying "You cannot add or change a record because a related record is required in table 'Salutations'.

Might have to do a three finger salute right now.

 

[ShovelinFishHeads]

lucky that all of that happened in a test copy of work file

 

[ShovelinFishHeads]

well it looked like I might have gotten around this issue, but something else has cropped up.

No matter what file I open, Access creates a lock file for it. Opening Northwind creates a .laccdb file.

This is really looking strange. Any idea why that would happen?

Tools/Options?

 

[Galaxiom]

Access is saying "You cannot add or change a record because a related record is required in table 'Salutations'.

That message comes from Referential Integrity in the Relationships.

Access is checking that there is already a record in the parent table "Salutations", for a foreign key being inserted into the child table.

 

[Galaxiom]

No matter what file I open, Access creates a lock file for it. Opening Northwind creates a .laccdb file.

That is perfectly normal behaviour.

 

[ShovelinFishHeads]

Ok, thanks, Galaxiom

I'm over-writing a couple of my tables with tables I have in archive files so as to "roll back the clock" in some areas that I may have some problems in. And I will be doing rigorous testing to see that this has worked as intended.

There is one other question I will be taking to the forum in the Tables category.