Shutdown A Database and Send Messages to Users Running On Local PC's

In situations where a database is copied to a local PC, shutting down the database and sending messages to users becomes a little more complex because the standard Workbench software relies on the front-end database being shared by all the users.  To get around this complication, you need the Shutdown and Message code that you add to you front end local database to look for a file on the server. The best way to do this is to look for a file associated with the backend version of your database. The same approach can be used when using multiple front-ends to link to one backend database.

So open the shutdown form in your database and find (and change) this line of code in the CheckForMessage subroutine

dbShutdownFilePath = Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & WBENCHSDFTYPE

to either

Dim BackendPath as string
backendPath = "\\Server\folder\backendData.mdb”
dbShutdownFilePath = Left(backendPath, Len(backendPath) - 4) & WBENCHSDFTYPE

or

Dim BackendPath as string
backendPath = GetLinkedPath_FX("tblMyTable") & “backendData.mdb”
dbShutdownFilePath = Left(backendPath, Len(backendPath) - 4) & WBENCHSDFTYPE

Also do the same for the workbench messages in the CheckForMessage subroutine.   

If you take the second option, you will need add these two modules

Function GetLinkedPath_FX(tableName) As Variant

'fxP --------------------------------------------------------
'fxP
'fxP--> GetLinkedPath_FX - Return the directory path of a linked table in the database
'fxP
'fxP tableName "string" is a linked table in the database
'fxP
'fxP Return the directory path of the database where the linked table belongs
'fxP
'fxP
'fxP eg dbpath = GetLinkedPath_FX("orders")
'fxP will return the path that the linked table belongs in like
'fxP "C:\Office97\Access\Samples" for the orders table in the northwind database
'fxP
'fx GR March 2001 for finding the path to a linked table
'fxP --------------------------------------------------------

Dim conPath As Variant
Const linkedID = ";DATABASE="

On Error Resume Next
conPath = CurrentDb.TableDefs(tableName).Connect
If InStr(conPath, linkedID) > 0 Then
  GetLinkedPath_FX = GetDBPath_FX(Mid(conPath, Len(linkedID) + 1))
Else
  GetLinkedPath_FX = Null
End If

End Function

Function GetDBPath_FX(Optional DbPathStr, Optional DbNameStr) As String

'fxP --------------------------------------------------------
'fxP
'fxP--> GetDBPath_FX - Return the directory path of the database
'fxP
'fxP DbPathStr "string" is a file path string (this is optional)
'fxP DbNameStr "string" database name without folder is returned
'fxP
'fxP Return the directory path of the database that you are using now
'fxP If you are using Access 2000, use the FXL9_startup module.
'fxP
'fxP
'fxP eg dbpath = GetDBPath_FX
'fxP
'fx Original This routine was published in SmartAccess newsletter #4
'fx GR Modified March 2001 for passing path strings
'fx GR Modified March 2003 to return name of database
'fxP --------------------------------------------------------


Dim strPath As String
Dim intlastslash As Integer

If IsMissing(DbPathStr) Then
  strPath = CurrentDb.Name
Else
  strPath = DbPathStr
End If

For intlastslash = Len(strPath) To 1 Step -1
  If Mid(strPath, intlastslash, 1) = "\" Then
    Exit For
  End If
Next intlastslash

GetDBPath_FX = Left(strPath, intlastslash)

If Not IsMissing(DbNameStr) Then
  DbNameStr = Mid(strPath, intlastslash + 1, Len(strPath) - intlastslash)
  'now remove the file type
  DbNameStr = Left(DbNameStr, InStr(DbNameStr, ".") - 1)
End If

End Function

Don’t forget to test !!

Other Access Workbench Topics
Home Page | What is an LDB File | Advanced Ways To Start The Workbench   | Orders

The Access Workbench Help File  - Internal Database Logging