compact backend after all controls closed

[wineguy via AccessMonster.com]

Access 2007, lastest Xp, split db.

Trying to compact the back-end at the close of front-end. Under the
assumption that if an unbound form was called with no linked controls and all
other forms were closed then the ldb file for the back-end would go away. I
checked the ldb file and there is nothing in it except for my computer name
and "ADMIN". But it doesn't go away when the unbound form is called. Is there
something I'm missing or am is my primary assumption invalid. All I'm trying
to do is compact the back-end.

Any thoughts?

Thanks in advance,

Glynn

 

[Rick Brandt]

Access 2007, lastest Xp, split db.

Trying to compact the back-end at the close of front-end. Under the
assumption that if an unbound form was called with no linked controls
and all other forms were closed then the ldb file for the back-end would
go away. I checked the ldb file and there is nothing in it except for my
computer name and "ADMIN". But it doesn't go away when the unbound form
is called. Is there something I'm missing or am is my primary assumption
invalid. All I'm trying to do is compact the back-end.

Any thoughts?

You (or someone else) still has something opened that is connecting to
the back end. If it goes away when you close the file entirely then that
proves it is you. You could have something besides a bound form like a
persisted Recordset or Database object for example. If you truly close
all connections to the back end then the LDB file should be gone.

 

[wineguy via AccessMonster.com]

Thank you for feedback. Can the back-end conncection be closed in VB even if
a pesisted Recordset or DB object is still open? If so, how?

Thanks again in advance.

Access 2007, lastest Xp, split db.

[quoted text clipped - 7 lines]

Any thoughts?

You (or someone else) still has something opened that is connecting to
the back end. If it goes away when you close the file entirely then that
proves it is you. You could have something besides a bound form like a
persisted Recordset or Database object for example. If you truly close
all connections to the back end then the LDB file should be gone.

 

[Rick Brandt]

Thank you for feedback. Can the back-end conncection be closed in VB
even if a pesisted Recordset or DB object is still open? If so, how?

If either exist it is because your code created them. They would have to
have global scope or be in an instantiated class object. You would also
use code to close them.

 

[wineguy via AccessMonster.com]

Hi Rick,

I think I found the problem, but not sure how to solve it...

from the main switchborad form:
onclose event
prerform app cleanup, etc...

at the end of main form
docmd.openform made to the unbound form to compact the backend
end sub

from the unbound compact back-end form:
on load event
code to close all forms except compact back-end form
x = Forms.Count - 1
For i = x To 0 Step -1
MsgBox Forms(i).Name
If Forms(i).Name <> ExceptName Then
DoCmd.Close acForm, Forms(i).Name
End If
Next i

However, this did not close the main form which I suspect is the reason the
back-end ldb file is open.

I checked to see if main form was still loaded: If IsLoaded("switchboard")
then I tried to force a close using docmd.close form but get the runtime 2501
error..

so it seems that I can't proceed with the backend compact until I can close
the main switchboard.

any additional thoughts?

thanks again in advance

Glynn

 

[Rick Brandt]

I think I found the problem, but not sure how to solve it...

from the main switchborad form:
onclose event
prerform app cleanup, etc...

at the end of main form
docmd.openform made to the unbound form to compact the
backend end sub

from the unbound compact back-end form:
on load event
code to close all forms except compact back-end form x =
Forms.Count - 1
For i = x To 0 Step -1
MsgBox Forms(i).Name
If Forms(i).Name <> ExceptName Then
DoCmd.Close acForm, Forms(i).Name
End If
Next i

However, this did not close the main form which I suspect is the reason
the back-end ldb file is open.

I checked to see if main form was still loaded: If
IsLoaded("switchboard") then I tried to force a close using docmd.close
form but get the runtime 2501 error..

so it seems that I can't proceed with the backend compact until I can
close the main switchboard.

Your actual code would probably be easier to decipher than this pseudo-
code. You aren't opening the unbound form with the acDialog argument are
you? I can't think of anything else that would prevent closing the other
form.

 

[wineguy via AccessMonster.com]

Here is the code used at the end onclose event for the main switchboard form
(bendpath is the path to the back-end database)..

DoCmd.OpenForm "compactdbform", , , , , , (bendpath)
Exit Sub

here is the code for the on load event of the compactdbform:

If IsLoaded("switchboard") Then
MsgBox "switchboard still open"
DoCmd.Close acForm, "switchboard", acSaveNo
End If

The docmd.close gives the 2501 error

perhaps there is another way to close the main switchboard form and then call
the compactdbform?

thanks in advance,

Glynn

I think I found the problem, but not sure how to solve it...

[quoted text clipped - 26 lines]

so it seems that I can't proceed with the backend compact until I can
close the main switchboard.

Your actual code would probably be easier to decipher than this pseudo-
code. You aren't opening the unbound form with the acDialog argument are
you? I can't think of anything else that would prevent closing the other
form.

 

[Rick Brandt]

Here is the code used at the end onclose event for the main switchboard
form (bendpath is the path to the back-end database)..

DoCmd.OpenForm "compactdbform", , , , , , (bendpath) Exit Sub

here is the code for the on load event of the compactdbform:

If IsLoaded("switchboard") Then
MsgBox "switchboard still open"
DoCmd.Close acForm, "switchboard", acSaveNo
End If

The docmd.close gives the 2501 error

perhaps there is another way to close the main switchboard form and then
call the compactdbform?

Sorry, I see no reason why what you are doing should not work.

 

[Albert D. Kallal]

However, this did not close the main form which I suspect is the reason
the
back-end ldb file is open.

I checked to see if main form was still loaded: If IsLoaded("switchboard")
then I tried to force a close using docmd.close form but get the runtime
2501
error..

so it seems that I can't proceed with the backend compact until I can
close
the main switchboard.

any additional thoughts?

The above will not work....

Why?

Well, you can not call a un-bound form a form that is bound and then
close the calling form. It may look like the form is closed, but ms-access
will not have yet released the variable (internal memory) space.
This is just how the architecture of access works and how it reclaims
memory resource back.

it goes like this:

-----------> form1 with connection to BE
----------------> call form2 that is unbound
-----------------------> close 1st form.

So, we have an internal stack of:

-------------------
form1 |
------------------
form2 |
-------------------

When we close form1, the stack is:


-------------------
| <-form closed, but resources NOT YET returned to
------------------ pool of memory
form2 |
-------------------

It is ONLY when we close form 2 does the above WHOLE memory stack get
cleared out and return to the pool of memory for ms-access.

To see the above, close form2 (which is un-bound), and then re-open it, You
will now be able to compact from that form. So, at the end of the day you
CAN NOT CALL a un-bound form from a bound form, close it...and then hope
your connection will close..

I suppose you *could* try calling the 2nd form with a macro as opposed to a
code button, but I don't think the code stack will behave different.

Solution:

Well, call the compact routines from a un-bund form, and make sure that this
form was NOT called/opened by a form that is bound (else the bound form will
be "below" the memory stack of the next form...and again those resources
cannot be return until you empty that internal program stack.

Another solution and one I used for years is to call the compact routines
from a custom menu. That routine can then freely close all open (bound)
forms...and do the compact.
.....

if the macro idea works, do post back here....I not had time to try this,
but I quite sure it is the 2nd form loading on top of the interal memory
stack that prevents the conneciton resouces being killed...

 

[David W. Fenton]

The above will not work....

Why?

Well, you can not call a un-bound form a form that is bound and
then close the calling form.

Sorry, Albert, but this is simply not true. I just built a test
database with a single linked table and two forms. The first form
has the table as its recordsource. As soon as that form is open, the
LDB file on the back end is created.

I added command button to open a second form that is unbound. After
the command button opens the second form, the command button code in
the first form closes the first form:

DoCmd.openForm "Form2"
DoCmd.Close acForm, Me.Name

As soon as that happens, the LDB file disappers.

Now, if Form2 was opened with acDialog, you can't close the first
form, because the line of code that opened the second form is still
executing.

But as long as the second form is opened non-modally, the first form
(or any open bound form) can be closed and the LDB file is deleted.

It's perfectly possible to program this with the form you open for
the compact having a loop through all the open forms and closing
them:

Private Sub Form_Load()
Dim i As Integer

For i = Forms.Count - 1 To 0 Step -1
If Len(Forms(i).Recordsource) > 0 Then
DoCmd.Close acForm, Forms(i).Name
End If
Next i
End Sub

This will close all the bound forms and release the LDB file.

Now, if you're using a persistent connection to the back end for
performance, you'll need to close that connection independently.
Your method for doing that will depend entirely on how you've
initiated that persistent connection.