Need code to run on DB close

B

Ben

Is there a way to have a method/code run when an access database is closed?
I need something like AUTOEXEC but when exiting the database.

Thanks for any and all help
ben
 
V

VBA Dabbler

Joan,

I have the same issue as Ben, but also on Startup/Opening the database. I
want to launch a VBA Sub when the database is open independent of opening a
form, am I relegated to launching a form to do this?

Thanks,

VBA Dabbler
 
J

John Mishefske

VBA said:
Joan,

I have the same issue as Ben, but also on Startup/Opening the database. I
want to launch a VBA Sub when the database is open independent of opening a
form, am I relegated to launching a form to do this?

Thanks,

VBA Dabbler

:

The best way to do this is to use a form. Since the first form opened will also be the
last form closed (LIFO) then a startup form is perfect for running code on app start and
stop.

You don't have to display the form - it can be opened hidden.

Startup code can run by several methods:

1) specifying a startup form in Tools, Startup
2) Creating an AutoExec macro that calls a function
3) Specifying a macro to run on startup using the /x command line option

There are other marginal ways to do it but those three (with 1 and 2 the most popular) are
the primary methods.

I usually use method #2 with my function opening a startup form (hidden) to re-attach
linked tables and maintain a linked recordset to the back-end). This allows me to run code
at app shutdown by using the Form_Unload event of that hidden startup form.
 
V

VBA Dabbler

John,
Thanks for your response. It was to the point and extremely helpful.

Regards,

VBA Dabbler
 
C

CSZAGON

What if someone has opened the database with the shift key?
I cannot garantee that the form (hidden or not) that calls the Unload Event
will be open every time the database is closed and I still need a code that
is called when the Db is closed.

Is there a way?

Appreciate any help.
 
C

CSZAGON

But this is exactly what I want to control. Who can and who cannot open the
database with the shift key.

I've created a profile table with the IDs of those that should be able to
open the Db using Shift.
Initially the Db has the Shift key disabled.
When someone opens the Db, an AutoExec macro calls a code that validates the
ID logged on the computer vs. the IDs in the table. If there is an ID match,
the Shift key is enabled.

But, next time, if someone that doesn't fit the profile tries to open the Db
using Shift, it will work because the Shift key was enabled the last time the
Db was opened by the other guy that had the profile.

So the Shift key HAS to be disabled every time the database is closed. And
this is exactly what I don't know how to do.
Unless there is another way to control this, but I'm not aware of.

I hope my explanation is clear.

Thanks,
CSZAGON.
 
C

Chris Mills

You can't do that, CS. The logic has an ill in it.

It's too late to enable/disable the SHIFT key once the db is open and
running code.
That would require a Time Machine...which would become recursive... :)

You CAN change the SHIFT key for the NEXT time the db is run; but this would
usually be used just to reset it for the developer (say).

Why worry about disabling the SHIFT key? Instead, enable or disable F11
(or some code equivalent) to provide access to the db window AFTER it's open.

----------
There is no event which can be force-run on database close. A hidden form is
the nearest you'll get. It is subject to the vagiaries of ULS security, of
course.
----------
Imagining your logic would work (!!!), you wouldn't set it against a User,
you'd set it against a Group and see if they belong to that Group (using code,
ULS itself would be asking a bit much)
----------
So the Shift key HAS to be disabled every time the database is closed.
I hope my explanation is clear.

Perfectly.
Don't muck with it in a production environment;leave it disabled.
If the shift key is disabled when closed then it's disabled when next opened.
There is NO case (apart from developer access) where I can imagine any user
needing the db not to run (and db window can be provided later if necessary).

A developer (regarding AllowBypassKeys) would, I imagine, reset the flag by
some secret method, open it again, do their stuff, then set the
AllowBypassKeys prior to despatch.
Or dispatch, either the db or the developer :)

Chris
 
J

John Mishefske

Chris said:
You can't do that, CS. The logic has an ill in it.

It's too late to enable/disable the SHIFT key once the db is open and
running code.
That would require a Time Machine...which would become recursive... :)

You CAN change the SHIFT key for the NEXT time the db is run; but this would
usually be used just to reset it for the developer (say).

Why worry about disabling the SHIFT key? Instead, enable or disable F11
(or some code equivalent) to provide access to the db window AFTER it's open.

If you follow Chris' advice then this code snippet will come in handy (credit to all the
previous posters who contributed to this example):

Private Sub toggleDatabaseWindow()

' determine if the database window is currently visible
If fIsDBCVisible() Then
hideDBwindow
Else
showDBwindow
End If

End Sub
Public Sub hideDBwindow()
On Error Resume Next

DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
End Sub

Public Sub showDBwindow()
DoCmd.SelectObject acTable, , True
End Sub

The fIsDBCVisible() proc is available at:

http://www.mvps.org/access/api/api0069.htm

(Thanks Dev.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top