ACC: How to Change the Owner of a New Table (141887)

SUMMARY

This article describes a method that you can use to transfer the ownership of a newly created table to a specific owner. For example, if the functionality within a secured database enables the current user to import a table, that user will be the owner of the table and will have full permissions on that table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

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

Action: RunCode
Function Name: ChangeOwner("MyTable","MyNewOwner", "MyNewOwnerPass")

Using Visual Basic for Applications

Dim IsOwnerChanged as Integer
IsOwnerChanged = ChangeOwner("MyTable", _"MyNewOwner", "MyNewOwnerPass")