VBA/Excel/Access/Word/Access/Access Environment

Add or delete users

 
Sub addUser()
   Dim myConnection As ADOX.Catalog
   Dim newUser As ADOX.User
   Dim userName As String
   Dim newPassword As String
   Set myConnection = New ADOX.Catalog
   myConnection.ActiveConnection = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\store.mdb;Jet OLEDB:System database=C:\store.mdw;User id=smith;Password=;"
   Set newUser = New ADOX.User
   newUser.Name = userName
   myConnection.Users.Append newUser
   myConnection.Users(newUser.Name).changePassword "", newPassword
End Sub

Change the password of the database from smith to jones

 
Sub changePassword()
   Dim myConnection As ADODB.Connection
   Dim strPassword As String
   strPassword = "ALTER DATABASE PASSWORD [jones] [smith];"
   Set myConnection = New ADODB.Connection
   With myConnection
      .Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CurrentProject.Path & "\mydb.mdb;"
      .Execute (strPassword)
    End With
End Sub

Changing the Access Environment

 
Property                                VBA Equivalent              Data Type
Application Title                       AppTitle                    String
Application Icon                        AppIcon                     String (Image name)
Display Form                            StartupForm                 String (Form name)
Display Database Window                 StartupShowDBWindow         Boolean
Display Status Bar                      StartupShowStatusBar        Boolean
Menu Bar                                StartupMenuBar              Menu bar name
Shortcut Menu Bar                       StartupShortcutMenuBar      Shortcut menu bar name
Allow Full Menus                        AllowFullMenus              Boolean
Allow Default Shortcut Menus            AllowShortcutMenus          Boolean
Allow Built-in Toolbars                 AllowBuiltinToolBars        Boolean
Allow Toolbar/Menu Changes              AllowToolBarChanges         Boolean
Allow viewing of code after an error    AllowBreakIntoCode          Boolean

Create user for Access database

 
Sub addUser()
   Dim myConnection As ADODB.Connection
   Dim newUser As ADOX.User
   Set myConnection = New ADODB.Connection
   With myConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Jet OLEDB:System database") = "C:\demo.mdw"
        .Open "Data Source=c:|BegVBA\store.mdb;User ID=JohnSmith;Password=;"
        .Execute ("CREATE USER jane [mycat] NULL")
       End With
End Sub

Creating a Password Programmatically with SQL command

 
Sub changePassword()
   Dim myConnection As ADODB.Connection
   Dim strPassword As String
   strPassword = "ALTER DATABASE PASSWORD [smith] NULL;"
   Set myConnection = New ADODB.Connection
   With myConnection
      .Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CurrentProject.Path & "\mydb.mdb;"
      .Execute (strPassword)
    End With
End Sub

Use Access Special Keys

 
Public Sub startupProperties()
   Dim myDatabase As Object
   Set myDatabase = CurrentDb
   With myDatabase
      .Properties.Delete "AllowFullMenus"
      .Properties.Delete "AllowToolBarChanges"
    End With
    Application.RefreshTitleBar
End Sub