Microsoft KB Archive/141887

= ACC: How to Change the Owner of a New Table =

Article ID: 141887

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q141887



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



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

