closing db connections

[shanedavid1981]

I am currently creating management reporting functions available through Access, and need to update the data tables on a daily basis.

Is there any way I can close access to the database at a certain time each day (say, 10am) for 10-15 minutes while this is done?

Sometimes managers (being managers) will have the DB open minimised and lock their terminals when not around, it's not feasible to expect them to remember each day to phsically log out of the DB, so I need to cut live connections from time to time.

There is no risk of data loss though, as the reports are from data sources they do not need to edit. They simply read what is displayed on screen and print reports from it.

Thanks )

 

[AccessGuruCarl]

Hi shanedavid,

One way...

Create a form, that checks the time of day.
Open this form Hidden, at start-up.

At that time, pop-up a new form, displaying

that the DB will close in xx minutes.

Run code to close all open forms, then exit the app.


Hope this helps...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[shanedavid1981]

hey, thanks for that )

how would i get a control on a form to constantly keep in current time with the system?

It's not something I have had to do before...

I can get it to take a snapshot of the current time, but not sure about refreshing every x minutes or something...

Thanks )

 

[alvechurchdata]

I can get it to take a snapshot of the current time, but not sure about refreshing every x minutes or something...

Take a look at the form's On Timer event.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[shanedavid1981]

thanks there, just what i needed )

 

[AccessGuruCarl]

Hi shanedavid1981,

I tried searching for a post that i found awhile ago pertaining to this,But couldn't locate it. I did however find the text copy i was working from.
Hope this helps...


Francescina (Programmer) May 5, 2001
Here is what I found in the ACCESS ONLINE ENCYCLOPEDIA (

http://www.unsoftwareag.com,

Article Code: N2)

How do I log out all users remotedly ?

From time to time it is necessary that all logged-in users leave the application in order to maintain or backup the backend.
The remote log out can be automatised in simply changing one single value in a table field of the backend.
The following steps are necessary:


Create a field [LogOutStatus] in a system table of the backend that holds the values 1, 2 or 3
Create a form in the frontend that stays open permanently (hidden)
Activate the timer interval of this form
Write code for the OnTimer event to control the field [LogoutStatus]
Write code to leave the frontend
Write code to verify the content of [LogOutStatus] on opening the frontend
The functioning:

The OnTimer-Event checks the value of [LogOutStatus]:
1 = nothing
2 = Display message "Application will shut down in a moment
3 = Another message Message if desired and closing frontend


On opening the frontend the same test has to be made.
Code examples:


Ontimer-Event select case dlookup("[LogOutStatus]","tblSystem")
case 1
' nothing
case 2
call MessageBeforeShutDown

case 3
' special Code for your application
application.quit
end select

Sub MessageBeforeshutdown
dim strMessage as string
strMessage="ATTENTION: The database has to be shut down in 5 minutes" _r> & "Please finish your work and leave the program."
msgbox strMessage

end sub

Please note:

- The time between changing the value of [LogOutStatus] and the shut-down of the frontends depends on the setting of the timer interval
- You need to control via VBA that the data manipulation will be terminated correctly
- All open objects have to be closed on shut-down
- You need to inform the users when the application is up again.


++++++
I'm trying this out and when I call MessageBeforeshutdown, I need to get an OK to continue. If the user is away from their desk and they don't OK out of the msgbox before the five minutes is up, nothing shuts down.

Any suggestions?

Marty

Francescina (Programmer) May 23, 2001
I recommend you to replace the msgbox with a form that pops up (with sound and a gif) and then closes automatically after lets say 5 seconds.
This is the solution that I developped currently but you do not need necessarily to inform the users.


----------------------------------------------------------------------------------------------

DbaseInc (IS/IT--Manageme) May 7, 2001
What I have done in my systems is create a table called tbl_Maintenance. I have four fields. StartTime, EndTime, Message, ForceOut. On log on, Switchboard, and main forms I have a on timer event that test for existance of record in the table. I have a global variable that counts number of notification. When Global Variable reaches 5 it then boots them out. This gives the user about 2 minutes to complete their work. It is not good to boot people out without allowing them to finish their work.

DbaseInc@Aol.com

----------------------------------------------------------------------------------------------

The following routine will give you a list that can populate a textbox (of course you'll want error trapping and set your objects = nothing in the exit).

There's also a download at Microsoft, the LDB viewer exe:

http://support.microsoft.com/support/kb/articles/q128/8/14.asp

Sub ShowCurrentUsers
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strUsers as string

Set conn = New ADODB.Connection


With conn
.connection = currentproject.connect
End With

Set rst = conn.OpenSchema(adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

Do Until rst.EOF

struser = rst!COMPUTER_NAME & vbcrlf

struser = struser & rst!CONNECTED & vbcrlf

rst.MoveNext
Loop

msgbox strusers

End Sub

----------------------------------------------------------------------------------------------------

Mikeauz (TechnicalUser) Jul 9, 2004
Hey if anyones still following this post I find it a lot cleaner to use a hidden listbox on every form instead of a dedicated Kickout form.

The on load event and then timer on each form(Requerys the listbox) event then just checks the listcount of the listbox and opens the warning form or 'About to close' form accordingly.

Only change needed is that it works of how many rows are in you maintenaince table rather than the value in one field

I would also try not to use msgboxes too much in your db as access wont boot someone out until they click okay, using pop up forms instead should alleviate this.

----------------------------------------------------------------------------------------------------



ribhead (TechnicalUser) Jan 7, 2005
Hello everyone. I have a question/comment on this method. I too have problems with updating my tables(maint) and have to wait until people get off the db. However when I'm in the db running code sometimes I have a person who opens it up while I'm running code and my program crashes. Is there a way to prevent people from opening while I'm in doing my "housekeeping"?
Thanks, Rib

++++
Porsche996GT2 (Programmer) Jan 7, 2005
Hello Rib,

Try incorporating this code into your invisi-form. Of course you have to change a couple of things in there to suit your program:

Private Sub Form_Load()

Dim LOS
LOS = DLookup("[LogOutStatus]", "tblMaintenance")

If LOS = 2 Or LOS = 3 Then
MsgBox "Database is not available at this time."
Quit
End If

End Sub

Hope this helps!
Porsche996GT2

JustLJ (MIS) Jan 7, 2005
Porsche996GT2 said:
If LOS = 2 Or LOS = 3 Then
MsgBox "Database is not available at this time."
Quit
End If

I use a Pop-up form (completely black with a white box in the middle and bold red text) and have the pop-up status checked with every opening of the database.

----------------------------------------------------------------------------------------------------

jedel (Programmer) Feb 24, 2005
Francescina,

This is an excellent solution to a problem that many administrators would have. I used the post for my database with a few modifications, like:
1. I didn't use a number system. A created a second form so I could create the message when I needed it as well as added a Timer control. This allows me to set the fields in the hidden form to what ever I want. When the timer ticks over, The message appears and the timers form sets to what I wanted. After that time, if the users haven't logged off, they are booted.

You could also set this up for any number of configurations like booting individuals and any criteria that you have set up.

Great post.
I'd give my right arm to be ambidextrous!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.