How to stop temporary access to the BE?

[FuzMic]

Hi cloud guys

In the Front End i would like to stop for a moment, other users accessing to the common BackEnd, so that i can do some File System Chores on the BE eg compact. I need a denied access on to the whole BE .mdb during the process eg in compacting. I will start this process only when all other users are not having any of BE tables opened.

Any ideas or am i asking for something impossible?

 

[Ranman256]

i have a table ,tConfig, with fields,
Shutdown. (y/n)
ShutMsg (s)

when i click it, any user that opens the app gets a message, ShutMsg, that the db is down try later.

this Function for shutdown is in many events:
if a user is in a form and closes it, they get the msg
form close
main menu button click
and various places.

sub btnOpenDtl_click()
   CheckShutdown
   code
  code
end sub

sub Form_close()
   CheckShutdown
  docmd.close
end sub

sub CheckShutdown() 
dim bStop as boolean
dim vMsg

  bStop= Dlookup("[Shutdown]","tConfig" )
 
  if bStop then 
    vMsg= Dlookup("[ShutMsg]","tConfig" )
     msgbox vmsg,vbInformation,"Shutdown"
     docmd.quit
 endif
end function

 

[FuzMic]

RanMan Thanks it give me good ideas

 

[Lightwave]

Not the complete answer but the following is a useful framework for automating some of the more mundane tasks when managing Access Databases. It could provide you with some ideas and may or may not be suitable based on the set up of your work practices. In particular for your task if you have a known time when everyone will be out of the backend.

Automating compact management on an Access Database


Firstly create the following function in your target MS Access database.

Public Function CompactDatabase()
Dim vStatusBar As Variant
DoCmd.SetWarnings False

If FileLen(CurrentDb.Name) > 2000000 Then
Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "The application will be compacted on close during compaction please do not interrupt")
Else
Application.SetOption ("Auto Compact"), 0
End If

End Function

Next open up notepad paste in the following code save it as a txt file and then in explorer edit the suffix to vbs. Then simply double click on the file within explorer to run it. You should see the access database you have identified in the VB script open and then immediately close at which point it carries out the compact. The eagle eyed will have spotted that the above function doesn’t actually perform a compact. All it does is set the compact on close option to True within Access settings. The VB Script then instructs the database to open then close and Access compacts the database on exit.

set oAccess = createobject("Access.Application")

oAccess.OpenCurrentDatabase "C:Path\TargetDatabaseyouwantCompacted.mdb"
oAccess.visible = true
wscript.sleep 1000
oAccess.Run "CompactDatabase"
oAccess.closecurrentdatabase

set oAccess = nothing

A VB script such as this could be used to remotely run any MS Access function or functions – simply substitute the “CompactDatabase” parameter (or add further run commands) with the name(s) of the function(s) in the database you wish to trigger and then run the VB Script.

Now you could simply call the vb script from Task Scheduler and if you have a server that is constantly on you could set it to run every day.

Here's a link that you could use with the above that walks you through using Task Scheduler. Apologies - its my own blog but it has pictures which I haven't got time to copy into this post.

Using Task Scheduler and VBS to automate things in Windows

 

[FuzMic]

Lighting Thunder thank you for the tip bits

Sent from my HTC_PN071 using Tapatalk