Hi there. Here are a few functions 1

[Maquis]

Hi there. Here are a few functions which I have found to be extremely helpful and I thought maybe someone else would like them as well. Someone posted a portion of this code here a few weeks ago and I basically tweaked it to add the auto-recognition and restart features. (Sorry, I don't remember who posted it, or I'd give credit.)

What this basically does is hide the database design from your users, however if you as the designer open the database it will open to directly to the database window. I've found that it's generally a pain to mess with shifting into the database or hunting for transparent buttons whenever I've wanted to modify some code for a user.

It will also automatically set all those pesky startup options for you.

I make no claims as to the efficiency of the code. I'm sure some of the VB gurus here could vastly improve upon the code. (You know who you are, feel free to post improvements) All I know is this works for me.

Public Function StartUp()
    Dim Designer As String
    Dim Restart As Boolean
    Dim stAppName As String
    
    Designer = "yourlogonhere"
    FIND_USER
      
    If sLogon = Designer Then
        Restart = UnlockStartup
    Else
        Restart = LockStartup
    End If
        
    If Restart Then
        'Close database and re-open
        stAppName = "MSAccess.exe " & CurrentDb.Name
        Call Shell(stAppName, 1)
        DoCmd.Quit
    Else
        If sLogon <> Designer Then DoCmd.OpenForm &quot;TitleForm&quot;
    End If
    
End Function
-----------------------------------
Sub FIND_USER()

On Error GoTo ERR_FIND_USER

    Dim UserParam$
    Dim sChk As String
    Dim CurrentAuditor As String
    
    UserParam$ = Environ(&quot;S_USER&quot;)
    If UserParam$ = &quot;&quot; Then UserParam$ = Environ(&quot;USERNAME&quot;)
    sLogon = UCase$(UserParam$)

EXIT_FIND_USER:
    Exit Sub
    
ERR_FIND_USER:
    MsgBox Error$
    Resume EXIT_FIND_USER
End Sub
---------------------------------------------
Function LockStartup() As Boolean
    Dim Restart As Boolean
    
    Restart = False
    ChangeProperty &quot;StartupShowDBWindow&quot;, dbBoolean, False, Restart
    ChangeProperty &quot;AllowBuiltinToolbars&quot;, dbBoolean, False, Restart
    ChangeProperty &quot;AllowFullMenus&quot;, dbBoolean, False, Restart
    ChangeProperty &quot;AllowToolbarChanges&quot;, dbBoolean, False, Restart
    ChangeProperty &quot;AllowBreakIntoCode&quot;, dbBoolean, False, Restart
    ChangeProperty &quot;AllowSpecialKeys&quot;, dbBoolean, False, Restart
    ChangeProperty &quot;AllowBypassKey&quot;, dbBoolean, False, Restart
    Application.SetOption &quot;Show Hidden Objects&quot;, False
    LockStartup = Restart
End Function
-----------------------------------------------
Function UnlockStartup() As Boolean
    Dim Restart As Boolean
    
    Restart = False
    ChangeProperty &quot;StartupMenuBar&quot;, dbText, &quot;(default)&quot;, Restart
    ChangeProperty &quot;StartupShowDBWindow&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;StartupShowStatusBar&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;AllowBuiltinToolbars&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;AllowFullMenus&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;AllowToolbarChanges&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;AllowBreakIntoCode&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;AllowSpecialKeys&quot;, dbBoolean, True, Restart
    ChangeProperty &quot;AllowBypassKey&quot;, dbBoolean, True, Restart
    UnlockStartup = Restart
End Function
-------------------------------------------
Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant, Restart As Boolean) As Integer
    Dim dbs As Database, prp As Property
    Dim CurrentPropVal As Variant
    Const conPropNotFoundError = 3270

    Set dbs = CurrentDb
    On Error GoTo Change_Err
    
    CurrentPropVal = dbs.Properties(strPropName)
    If CurrentPropVal <> varPropValue Then
        dbs.Properties(strPropName) = varPropValue
        Restart = True  'need to restart database
    End If
    ChangeProperty = True
Change_Bye:
    Exit Function

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

To get it working just follow these steps:
1. Put the above code in a module in your db.
2. Create a macro called autoexec and put RunCode StartUp() in it.
3. In the StartUp function change the "Designer = " line to your logon id.
4. In the StartUp function change the docmd.openform command to reference your start up form. Maq B-)
<insert witty signature here>

 

[Craig0201]

Maq,

Will you repost as an FAQ? Much more likely to be found that way. Top post!

Craig

 

[wombatwoo]

hi Maq

I've tried your code listed above but VBA doesn't recognise the Database datatype in the change property function - dbs As Database. Can you understand why? I thought this sounded good and wanted to use it in my Uni project but I can't get it to work.

wombat

 

[KyleS]

Hi,
When you're in your VBA Editor, go to Tools-->References and make sure that Microsoft DAO (version number) is selected. Then change the code to Dim dbs as DAO.Database Kyle :

 

[930driver]

To use this as a form of 'security' you'll also need to disable the Shift Bypass option in the start up properties of your database, else someone can simply bypass your startup code. Bear in mind, though, that the Shift Bypass Disable can, it's self, be disabled.

If you really want to prevent your users from having access to design mode distribute an mde.
&quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[KyleS]

Very true,
But even with an MDE I can still get your tables and do the real damage (it's a lot harder to replace data than coding)

Ah, good old Access Security discussions... Kyle :

 

[930driver]

Yup, that's another level. If you want to completely lock down code, data, and all, you need the full blown Access security. &quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[Maquis]

Hi guys,

I can't believe someone actually found this thread after 5 months. Actually, I ended up taking Craig's advice and reposted the code as a FAQ. (FAQ181-1172)

The above code does include code for disabling the shift trick, however the data is still vulnerable. Like Kyle said, the best way to protect your data is to learn and properly implement Access security. (Something I haven't done yet, so I can't offer any advice.)

I work in the IT department and generally when I design a database, I'm more concerned with protecting the database design from prying users who want to tweak it a bit than I am protecting the data. The database goes into a secure directory where only the proper users have access to the database anyways, and if they delete/screw up essential data they realize that they are responsible for the data. I'm only responsible to make sure the database works properly. Therefore, hands off my queries!

Anyways, I hope you get it working properly. And good luck implementing the security. Maq
<insert witty signature here>