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

Add or delete users

   <source lang="vb">

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

</source>
   
  

Change the password of the database from smith to jones

   <source lang="vb">

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

</source>
   
  

Changing the Access Environment

   <source lang="vb">

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

</source>
   
  

Create user for Access database

   <source lang="vb">

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

</source>
   
  

Creating a Password Programmatically with SQL command

   <source lang="vb">

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

</source>
   
  

Use Access Special Keys

   <source lang="vb">

Public Sub startupProperties()

  Dim myDatabase As Object
  Set myDatabase = CurrentDb
  With myDatabase
     .Properties.Delete "AllowFullMenus"
     .Properties.Delete "AllowToolBarChanges"
   End With
   Application.RefreshTitleBar

End Sub

</source>