ACC: How to Change the Owner of a New Table

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 you want to secure. You should call the function immediately after importing the table to ensure permission 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

NOTE: In the following examples, 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 these examples in Access Basic.

   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")

REFERENCES

For more information about database security and object ownership, type "security ownership" in the Office Assistant, click Search, and then click to view the desired topic.

Keywords          : kbusage PgmHowTo
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto