ACC: How to Change the Owner of a New Table

MORE INFORMATION

If your secured database application enables users to import a table, those users will be the owners of the table and will have full permissions on that table. The function below demonstrates how you can programmatically create a secured session as a user different from the current user and transfer ownership of an imported object from the current user to another user.

The following function should be called within a macro, a function or a Sub procedure that imports the table that you want to secure. You should call the function immediately after importing the table to ensure that permissions have been set.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Function ChangeOwner(NewTable As String, NewOwner As String, PWord As _
      String)

      Dim NewWS As WorkSpace
      Dim db As Database
      Dim cnt As Container
      Dim doc As Document

      Set db = CurrentDB()

      'Create a new session and append it to the Workspaces collection.
      Set NewWS = dbengine.CreateWorkspace("NewWS", NewOwner, PWord)

      'Assign variables for the table's Container and Document objects.
      Set cnt = db.Containers("Tables")
      Set doc = cnt.Documents(NewTable)

      'Establish new owner of the imported table.
      doc.Owner = NewOwner

      'Cleanup.
      NewWS.Close
      db.Close

      'Pass success status back to caller.
      ChangeOwner = True

    End Function
				

To use this function, use either of the following methods. Each presumes that the table has just been imported, that the table is named MyTable, and that a valid user named MyNewOwner with a password of MyNewOwnerPass exists.

Using a Macro

Using Visual Basic for Applications