Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh linked tables (2000/03)

    In an effort to increase security, it's been suggested that all FE refresh linked tables, & if they cannot (because the unauthorised user does not have at least read permissions to BE folder) then the app closes.

    Edit; another suggestion was that the single PC used to update the BE tables runs the 'compact & repair' to ensure workspace does not increase disproportionately. My plan here was to have a REALLY basic FE that would run the 'pass through' & 'update' queries (during the night) automatically & that this would therefore not be an issue. Any thoughts?

    Can anyone give me a way of doing this? NB my VBA skills are next to nothing.

    TIA Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh linked tables (2000/03)

    1. Put the following code in a standard module in the frontend:

    Public Function RefreshLinks() As Boolean
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    On Error GoTo ExitHandler
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    If Not tdf.Connect = "" Then
    tdf.RefreshLink
    End If
    Next tdf
    RefreshLinks = True
    ExitHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    End Function

    Public Function TestPermission()
    If RefreshLinks = False Then Quit
    End Function

    Call TestPermission in an AutoExec macro, with Action: RunCode and Function name: TestPermission(), or in the On Open event of the startup form of the frontend.

    2. Running update queries will increase the size of a database. To compact and repair the backend, you can run this code when nobody is connected to the backend:

    Sub CompactBackend()
    ' Substitute path and name, without .mdb
    Const strBackend = "F:AccessBE"
    DBEngine.CompactDatabase strBackend & ".mdb", _
    strBackend & "_Compact.mdb"
    If Not Dir(strBackend & "_Compact.mdb") = "" Then
    Kill strBackend & ".mdb"
    Name strBackend & "_Compact.mdb" As strBackend & ".mdb"
    End If
    End Sub

    Note: you must set a reference to the Microsoft DAO 3.6 Object Library for the code to work, in Tools | References in the Visual Basic Editor.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh linked tables (2000/03)

    Thank you once again Hans, you always seem to be there ready with answers for me. Appreciated.

    One additional question; we have a 24/7 factory, so it's not impossible that there may be someone connected, even at one in the morning. Any ideas which strategy would be the most effective way to approach this scenario?

    Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh linked tables (2000/03)

    See the thread starting at <post#=262704>post 262704</post#> for a way to log out users from the backend automatically.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh linked tables (2000/03)

    Oh I like that. Can this be set for a specific time, instead of a time interval? Eg 23:55, to allow update just after midnight.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Refresh linked tables (2000/03)

    If there is concern over security, why not activate Access User Security? It's a reasonably robust method for keeping unauthorized users from ever opening the database. (As a matter of fact, if you are using NT folder permissions, the user won't be able to open the BE if they don't have Read permission.)
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh linked tables (2000/03)

    Instead of using a table tblLogOff with a field LogOff, you could check for the time. Set the TimerInterval to 300000 (this is 5 minutes in milliseconds). I would also make the form a popup form, and place a label with the text about the database being shut down on the form. When the time is after 23:55, make the form visible. With the message box from the original code, the database still wouldn't be shut down if the user doesn't click OK.

    Public Sub Form_Timer()
    Static LogOff As Boolean
    If Time > TimeSerial(23, 55, 0) Then
    If LogOff = False Then
    LogOff = True
    Me.Visible = True
    End If
    ElseIf LogOff = True Then
    Quit
    End If
    End Sub

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh linked tables (2000/03)

    Many thanks gents, I'll work on building in that (amongst other things) this afternoon.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •