How to lock down tables?

[pikoy]

Can someone help me out on how to lock down the database tables?

Tried the code below but its not working. What am i doing wrong? or if you have a better way, i would appreciate it very much.

Private Sub LockDown()
DoCmd.ShowToolbar "Ribbon", acToolbarNo
DoCmd.ShowToolbar "Menu Bar", acToolbarNo
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i
End Sub


thank you

 

[Trevor G]

If you don't want users to access your tables, then hide the table.

 

[Galaxiom]

Several threads covering this topic on this forum.

Throw this line at Google:
"hide tables" access site:www.access-programmers.co.uk

There are many tricks to decrease the exposure of tables in Access but if you really don't want users to see your tables you have little choice but put them on SQL Server or equivalent and use recordsets to communicate with them.

Anything less is vulnerable and just a matter of persistence on the part of someone who wants to get to them.

 

[gemma-the-husky]

basically I use this.

You need to determine whether the user is a MASTERUSER or not. You could use access security, or test windows login, for this

if so, then let that user see everything, otherwise lock it down

Be careful, as I think you could lock yourself out completely.

This sets settings for the NEXT session, not the current session, so you have to do this twice, Try it and see.

Sub SetStartupProperties()
    If MASTERUSER Then
        ChangeProperty "StartupShowDBWindow", dbBoolean, False  'hide the dbwindow
        ChangeProperty "AllowBreakIntoCode", dbBoolean, True    'show code after error
        ChangeProperty "AllowSpecialKeys", dbBoolean, True      'special keys
        ChangeProperty "AllowBypassKey", dbBoolean, True        'bypass key
        ChangeProperty "StartupShowStatusBar", dbBoolean, True  'status bar
        ChangeProperty "AllowFullMenus", dbBoolean, True        'fullmenus
        ChangeProperty "AllowShortCutMenus", dbBoolean, True        'fullmenus
        ChangeProperty "AllowBuiltinToolbars", dbBoolean, True
    
    Else
        ChangeProperty "StartupShowDBWindow", dbBoolean, False  'hide the dbwindow
        ChangeProperty "AllowBreakIntoCode", dbBoolean, False   'show code after error
        ChangeProperty "AllowSpecialKeys", dbBoolean, False     'special keys
        ChangeProperty "AllowBypassKey", dbBoolean, False       'bypass key
        ChangeProperty "StartupShowStatusBar", dbBoolean, True  'status bar
        ChangeProperty "AllowFullMenus", dbBoolean, False     'full menus
        ChangeProperty "AllowShortCutMenus", dbBoolean, True        'fullmenus
        ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
   End If
End Sub


Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
    Dim dbss As Database, prp As Property
    Const conPropNotFoundError = 3270

    Set dbss = CurrentDb
    On Error GoTo Change_Err
    dbss.Properties(strPropName) = varPropValue
    ChangeProperty = True

Change_Bye:
    Exit Function

Change_Err:
    If err = conPropNotFoundError Then  ' Property not found.
        Set prp = dbss.CreateProperty(strPropName, varPropType, varPropValue)
        dbss.Properties.append prp
        Resume Next
    Else
        ' Unknown error.
        ChangeProperty = False
        Resume Change_Bye
    End If
End Function

 

[Galaxiom]

Dave's (Gemma's Master) Code can be made more consise.

Sub SetStartupProperties()
 
Dim boolState as Boolean
 
   If MASTERUSER Then
      boolState = True  [COLOR=seagreen]' Boolean defaults to False. Set it false on Else if you have slightest doubt.[/COLOR]
   End If
 
   [COLOR=seagreen]' Set properties same in both [/COLOR]
   ChangeProperty "StartupShowDBWindow", dbBoolean, False [COLOR=seagreen]'hide the dbwindow[/COLOR]
   ChangeProperty "AllowShortCutMenus", dbBoolean, True [COLOR=seagreen]'fullmenus[/COLOR]
   ChangeProperty "StartupShowStatusBar", dbBoolean, True [COLOR=seagreen]'status bar[/COLOR]
 
   [COLOR=seagreen]' Set different properties[/COLOR] 
   ChangeProperty "AllowBreakIntoCode", dbBoolean, boolState [COLOR=seagreen]'show code after error[/COLOR]
   ChangeProperty "AllowSpecialKeys", dbBoolean, boolState [COLOR=seagreen]'special keys[/COLOR]
   ChangeProperty "AllowBypassKey", dbBoolean, boolState [COLOR=seagreen]'bypass key[/COLOR]
   ChangeProperty "AllowFullMenus", dbBoolean, boolState [COLOR=seagreen]'fullmenus[/COLOR]
   ChangeProperty "AllowBuiltinToolbars", dbBoolean, boolState
 
End Sub

In the above example the Global variable MASTERUSER could be used directly in place of boolState but I started with the IF as a more general example.

Alternatively in a similar situation where MASTERUSER was not a Global it could be passed as an argument.

I often set control and form properties like this by passing an argument to the sub. Great for subs or functions that change modes from Locked to Editable on a form. Used in a For Each loop with a Tag to identify target controls on a form, the code to switch the properties can be remarkably brief.

If necessary a Not MyArgument variable can be set in the first step. (Particularly useful for example when setting the properties of a control to Enabled = True and Locked = False and vice versa.)

Surprisingly, I have not come across an example done this way by anyone else with developers tending to set out the whole thing out twice for some reason. Maybe they just find it easier to read.

 

[pikoy]

Thanks you All. I did lock myself from the tables too but i was able to get into it again and all is fine.

Used the concise version and it worked. Co-Workers kept on touching the tables and changed the relationships and didnt know how to put it back together... hence, the lockdown.

Thank you all again.

 

[gemma-the-husky]

Dave's (Gemma's Master) Code can be made more consise.

Sub SetStartupProperties()
 
Dim boolState as Boolean
 
   If MASTERUSER Then
      boolState = True  [COLOR=seagreen]' Boolean defaults to False. Set it false on Else if you have slightest doubt.[/COLOR]
   End If
 
   [COLOR=seagreen]' Set properties same in both [/COLOR]
   ChangeProperty "StartupShowDBWindow", dbBoolean, False [COLOR=seagreen]'hide the dbwindow[/COLOR]
   ChangeProperty "AllowShortCutMenus", dbBoolean, True [COLOR=seagreen]'fullmenus[/COLOR]
   ChangeProperty "StartupShowStatusBar", dbBoolean, True [COLOR=seagreen]'status bar[/COLOR]
 
   [COLOR=seagreen]' Set different properties[/COLOR] 
   ChangeProperty "AllowBreakIntoCode", dbBoolean, boolState [COLOR=seagreen]'show code after error[/COLOR]
   ChangeProperty "AllowSpecialKeys", dbBoolean, boolState [COLOR=seagreen]'special keys[/COLOR]
   ChangeProperty "AllowBypassKey", dbBoolean, boolState [COLOR=seagreen]'bypass key[/COLOR]
   ChangeProperty "AllowFullMenus", dbBoolean, boolState [COLOR=seagreen]'fullmenus[/COLOR]
   ChangeProperty "AllowBuiltinToolbars", dbBoolean, boolState
 
End Sub

In the above example the Global variable MASTERUSER could be used directly in place of boolState but I started with the IF as a more general example.

Alternatively in a similar situation where MASTERUSER was not a Global it could be passed as an argument.

I often set control and form properties like this by passing an argument to the sub. Great for subs or functions that change modes from Locked to Editable on a form. Used in a For Each loop with a Tag to identify target controls on a form, the code to switch the properties can be remarkably brief.

If necessary a Not MyArgument variable can be set in the first step. (Particularly useful for example when setting the properties of a control to Enabled = True and Locked = False and vice versa.)

Surprisingly, I have not come across an example done this way by anyone else with developers tending to set out the whole thing out twice for some reason. Maybe they just find it easier to read.

Yeah

I did it like this a few years ago, when I was puzzling out what worked, and what didn't.

I hadn't been back to it, just copied and posted it.