Access Locking File Still There - but Why?

[Guest]

Hi All

Okay - here is a puzzler for you clever people out there....

Configuration Details
Windows XP Pro SP2
Access 2003

FE/BE Configuration
BE - Access 2000 file format
FE - Access 2003 file format
FE/BE communicate via linked tables

Here is the problem
During the open event of the first form, all linked tables are deleted and
re-created
The form that is opened at launch creates a persistent connection to a table
T_System in the backend (i.e. the form is bound to a table)
The form is then hidden and a second form is opened up with a datasource of
T_System (same table)

I have a backup routine that does the following
1. cycles through the Forms collection in reverse order, closing all forms
down
2. when all forms are closed, a check is made to see whether there is a
locking file for the backend database
3. if there is - the backup aborts

Now for some reason, whenever I run this, after I have closed the forms
down, there is still a LDB file sitting out there. If I open the forms up
manually, and then close them, or even put them into design mode, the LDB
goes - which is what I would expect.

There is only me on the system during development.

Does anyone know what might be causing this, or where I could look to find
out more.

This backup code I have used in other projects and it works fine - the only
difference that I could see is that the second form in these cases is NOT
bound to a record source - but once it is closed, surely the connection goes?

 

[ruralguy via AccessMonster.com]

Hi Chris,
You probably already know this but there must still be a table open in the
BackEnd for the ldb file to still be there. You may want to inspect the Forms
collection to make sure it is empty.

 

[Guest]

Hi ruralguy

Thanks for the quick response.

I don't think there are any (obvious) tables open - I stepped through the
code having just opened up the logon and main menu form

I checked the collection just in case but there were no forms open (I step
down the collections from count to 1)

30 For FormCounter = Forms.Count To 1 Step -1
40 DoCmd.Close acForm, Forms(FormCounter - 1).name
50 Next FormCounter

and then pause - but the lock it there. So there are no forms open, but a
lock - it is driving me nuts AND if I just open the forms (keeping an eye on
the LDB file) I can see the creation when I epexct it, and it quite nicely
goes away again when both forms are closed/swapped into design mode

As the code is used sucessfully elsewhere - I don't think it is the code
(but you never take anything for granted).

The only thing I can see that is different in this case is that the two
forms both connect to the same table - whereas where it works, one of the
forms is unbound, but I just cannot see why this would make a difference?

 

[Douglas J. Steele]

The Forms collection, like most collections in Access, starts at 0:

For FormCounter = Forms.Count To 0 Step -1

 

[Douglas J. Steele]

Ooops. Never mind. I see you're subtracting 1 from FormCounters.

It's more usual to see

For FormCounter = (Forms.Count - 1) To 0 Step -1
DoCmd.Close acForm, Forms(FormCounter).name
Next FormCounter

Try putting

MsgBox Forms.Count

after that code's run.

 

[Guest]

Hi Douglas

I think I have that covered by the statement

docmd.Close acForm, Forms(FormCounter - 1).Name

so when I have 2 forms open , then count is 2, but the reference is 1 and 0

or have I missed something really silly!

 

[ruralguy via AccessMonster.com]

Hi CHris,
Maybe it is not a form but a RecordSet in a standard module? I know this
sounds like beating a dead horse but it is almost certain to be an open
recordset somewhere.

Hi Douglas

I think I have that covered by the statement

docmd.Close acForm, Forms(FormCounter - 1).Name

so when I have 2 forms open , then count is 2, but the reference is 1 and 0

or have I missed something really silly!

The Forms collection, like most collections in Access, starts at 0:

[quoted text clipped - 85 lines]

 

[John W. Vinson]

Maybe it is not a form but a RecordSet in a standard module? I know this
sounds like beating a dead horse but it is almost certain to be an open
recordset somewhere.

Try looping through the Tabledefs collection looking for linked tables.

John W. Vinson [MVP]

 

[Guest]

Thanks Douglas - that is a much better way of doing it - it is much clearer-
you know how it is when something just works, you just tend to leave it, but
I shall convert to this clearer way

 

[Guest]

Hi John

The linked tabledefs collection is the same pre and post form closing. Is
there something in the tabledefs that show that there is an active link (as
opposed to there just being linked tables?)

 

[Guest]

Hi Ruralguy

That is a possibility - I have checked the currentdb.recordsets.count (but
this is zero both before and after the form closing) - what else can I check
to see if there are open recordsets - is there something that supplies this.
I've looked at the currentdb object, but nothing seems to fit the bill

--
Regards
Chris

Hi CHris,
Maybe it is not a form but a RecordSet in a standard module? I know this
sounds like beating a dead horse but it is almost certain to be an open
recordset somewhere.

Hi Douglas

I think I have that covered by the statement

docmd.Close acForm, Forms(FormCounter - 1).Name

so when I have 2 forms open , then count is 2, but the reference is 1 and 0

or have I missed something really silly!

The Forms collection, like most collections in Access, starts at 0:

[quoted text clipped - 85 lines]

bound to a record source - but once it is closed, surely the connection
goes?

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[jbguernsey]

Hi Ruralguy

That is a possibility - I have checked the currentdb.recordsets.count (but
this is zero both before and after the form closing) - what else can I check
to see if there are open recordsets - is there something that supplies this.
I've looked at the currentdb object, but nothing seems to fit the bill

--
Regards
Chris

Hi CHris,
Maybe it is not a form but a RecordSet in a standard module? I know this
sounds like beating a dead horse but it is almost certain to be an open
recordset somewhere.

Hi Douglas
I think I have that covered by the statement
docmd.Close acForm, Forms(FormCounter - 1).Name
so when I have 2 forms open , then count is 2, but the reference is 1 and 0
or have I missed something really silly!
The Forms collection, like most collections in Access, starts at 0:
[quoted text clipped - 85 lines]
bound to a record source - but once it is closed, surely the connection
goes?

Message posted via AccessMonster.com

Hope you don't mind my butting in.

You have clearly expended quite a bit of time and effort trying to
establish what is keeping the LDB file open ...

Maybe you are asking the wrong question here.

If there is nothing keeping the LDB open then the question might be:
what failed to close the LDB?

Try deleting the LDB by code after you have closed all your forms.
If you can delete it then there is NOTHING keeping it open.
Somehow it failed to be closed when it should have been.

It might be enough just to delete it or you might want to track down
why it stays open.

Of course, this might be totally wrong but it seems worth a look.

Jeff Bailey

 

[Guest]

Hi Jeff

Thanks for the suggestion - but I cannot delete the file, so something has it!

I have noticed that if I do the following the ldb file still stays alive:

1. Open system in design
2. Open logon screen - ldb file appears
3. Switch to design mode - ldb file goes
4. Switch back to form view - ldb file re-appears
5. Switch back to design mode - ldb file is still there

ldb file now only goes if form is closed down completely

Now the logon form, does call modules which do some DB work, (Dlookup, and 1
recordset), but if they were keeping a lock open I would expect them to do it
each time - and the same code is called on each open event

--
Regards
Chris

Hi Ruralguy

That is a possibility - I have checked the currentdb.recordsets.count (but
this is zero both before and after the form closing) - what else can I check
to see if there are open recordsets - is there something that supplies this.
I've looked at the currentdb object, but nothing seems to fit the bill

--
Regards
Chris

Hi CHris,
Maybe it is not a form but a RecordSet in a standard module? I know this
sounds like beating a dead horse but it is almost certain to be an open
recordset somewhere.

Chris W wrote:
Hi Douglas

I think I have that covered by the statement

docmd.Close acForm, Forms(FormCounter - 1).Name

so when I have 2 forms open , then count is 2, but the reference is 1 and 0

or have I missed something really silly!

The Forms collection, like most collections in Access, starts at 0:

[quoted text clipped - 85 lines]
bound to a record source - but once it is closed, surely the connection
goes?

Message posted via AccessMonster.com

Hope you don't mind my butting in.

You have clearly expended quite a bit of time and effort trying to
establish what is keeping the LDB file open ...

Maybe you are asking the wrong question here.

If there is nothing keeping the LDB open then the question might be:
what failed to close the LDB?

Try deleting the LDB by code after you have closed all your forms.
If you can delete it then there is NOTHING keeping it open.
Somehow it failed to be closed when it should have been.

It might be enough just to delete it or you might want to track down
why it stays open.

Of course, this might be totally wrong but it seems worth a look.

Jeff Bailey

 

[Guest]

Update:

I have noticed that if I remoe the recordsource to the second form, then
when I unload the forms, the ldb file goes. I seems to be something to do
with two forms having the same recordsource, Access is getting confused

 

[Guest]

Thanks all of you for your help.

I have managed to find the problem!

The main form which is bound to a table, has a button on it which calls a
common function to take the backup.

This function then closes the form down, BUT, because the form was bound to
a table, Access does not release the lock, until the function has fully
completed - I guess the lock is effectively still in scope

This would explain why the routine works in other systems, where the form it
is being called from is not bound to any table

 

[ruralguy via AccessMonster.com]

Excellent! Glad you got it sorted Chris.

Thanks all of you for your help.

I have managed to find the problem!

The main form which is bound to a table, has a button on it which calls a
common function to take the backup.

This function then closes the form down, BUT, because the form was bound to
a table, Access does not release the lock, until the function has fully
completed - I guess the lock is effectively still in scope

This would explain why the routine works in other systems, where the form it
is being called from is not bound to any table

Hi All

[quoted text clipped - 37 lines]

difference that I could see is that the second form in these cases is NOT
bound to a record source - but once it is closed, surely the connection goes?

 

[Guest]

Chris, Please post how you decided to resolve your problem. I understand
what your saying, but I am not sure how to resolve it.

 

[Guest]

And interestingly, even when I move the function calls out of the form and
call them from the menu - the lock stays there until the functions have
completed (and they do not do any DB work - it is all file copy, reads etc)

What I did to get round the problem was in the Unload event I re-set the
forms recordsource to a local table (and set a switch to prevent the current
event from being actioned). This for some reason allows the ldb to go when
the form is closed

Any way it works now so I can move on

Hope this post is of use for someone else

--
Regards
Chris

Excellent! Glad you got it sorted Chris.

Thanks all of you for your help.

I have managed to find the problem!

The main form which is bound to a table, has a button on it which calls a
common function to take the backup.

This function then closes the form down, BUT, because the form was bound to
a table, Access does not release the lock, until the function has fully
completed - I guess the lock is effectively still in scope

This would explain why the routine works in other systems, where the form it
is being called from is not bound to any table

Hi All

[quoted text clipped - 37 lines]

difference that I could see is that the second form in these cases is NOT
bound to a record source - but once it is closed, surely the connection goes?

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Guest]

Hi Steven

I have posted to ruralguy what I did. I cannot explain why this solution
works, as I am not clear on the root cause. Howver, resetting the
recordsource of the form in the unload section allows the ldb to clear when
the form is closed (if the recordsource is left, then so is the ldb file!!!)