Microsoft KB Archive/259224

Knowledge Base

HOW TO: Determine the User Name in a Data Access Page in an Access 2000 Project

Article ID: 259224

Article Last Modified on 6/24/2004

---

APPLIES TO

• Microsoft Access 2000 Standard Edition

---

This article was previously published under Q259224

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

IN THIS TASK

SUMMARY

• Creating the SQL Database and Table
• Writing a Stored Procedure to Return the User Name
• Create the Data Access Page

REFERENCES

SUMMARY

You can determine the current network user name of a user who is logged on to a Microsoft Windows NT or a Microsoft Windows 2000 network by using Win32 API functions. However, because scripting languages such as VBScript or JScript do not support API calls for security purposes, you cannot use these functions in a data access page.

Microsoft SQL Server provides the SUSER_SNAME() function to return the user name that is logged on to SQL Server. By using this function in a stored procedure, you can determine the network user name in a data access page in an Access project. This article shows you how to do this.

To demonstrate how this procedure works, this article asks you to do the following:

• Create a SQL database and a table.
• Write a stored procedure to return the user name.
• Build the data access page with a script to filter records based on the return value from the stored procedure.

back to the top

Creating the SQL Database and Table

1. Start Access 2000, click the Access database wizards, pages and projects option, and then click OK.
2. On the General tab, click Project (New Database), and then click OK.
3. Type a new file name for the project, and then click OK.
4. On the first page of the Microsoft SQL Server Database Wizard, select or type the name of a server to use to create the database.
5. Leave the user name and password blank. This will create the database using NT integrated security. You must have CREATE DATABASE permissions on the server to create a database on that server.
6. Type the name of a new SQL database to be created on the SQL Server.
7. Click Next, and then click Finish to create the new database.

8. In the Database window, click Stored Procedures, and then click New. Replace the existing code with the following stored procedure:

   Create Procedure CreateTestTable
   As
   CREATE TABLE TestTable (
       CustomerID nchar(5) NOT NULL ,
       CompanyName nvarchar(40) NOT NULL ,
       ContactName nvarchar(30) NULL ,
       ContactTitle nvarchar(30) NULL ,
       Address nvarchar(60) NULL ,
       City nvarchar(15) NULL ,
       Region nvarchar(15) NULL ,
       PostalCode nvarchar(10) NULL ,
       Country nvarchar(15) NULL ,
       UserName nvarchar(10) NULL 
   
   CONSTRAINT MyContraint PRIMARY KEY (CustomerID)
   
   )
       return 
                           

   Close and save the stored procedure.

9. In the Database window, double-click the new stored procedure to run it. This creates a table on SQL Server. You must have CREATE TABLE permissions on the server to run this stored procedure.
10. After the table has been created, open it in Design View. Insert sample data into the new table, making sure to enter your network user name in the UserName field.

back to the top

Writing a Stored Procedure to Return the User Name

1. In the Database window, click Stored Procedures, and then click New. Type the following stored procedure:

   Create Procedure spGetUserName
   As
   -- declare variables
   DECLARE @name varchar(30)
          ,@lenindex int
          ,@lenall int
          ,@username varchar(10)
   -- return the user logged into the SQL server
   SELECT @name = suser_sname()
   SELECT @lenall = Len(@name)
   SELECT @lenindex = CHARINDEX('\', @name)
   -- clean up the variables and return a field named NTUserName
   SELECT SUBSTRING(@name, @lenindex+1, @lenall-@lenindex) AS NTUserName
       return
                           

   Close and save the stored procedure.

2. In the Database window, double-click the spGetUserName stored procedure to run it. Note that one field and one record is returned with your network user name. Close the results.

back to the top

Create the Data Access Page

1. In the Database window, click Pages, and then click New. Click AutoPage: Columnar, select TestTable from the list of tables, and then click OK.
2. Open the page in Design view.
3. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
4. Locate the Script Outline in the bottom-left corner of the screen.
5. Expand the Client Objects & Events folder, and expand the window object. Double-click the onload event. This will enter a client-side SCRIPT block for the event.

6. Type the following code:

   'Return the current user from the connection.
   'This will return "sa" when using SQL authentication, and an empty 
   'string if using NT authentication.  You must use a stored
   'procedure to parse out the NT user name using SQL functions
   
   dim rs, strUserName
   
   'This property will return SSPI when using NT integrated security
   if MSODSC.Connection.Properties("Integrated Security")="SSPI" then
      set rs=MSODSC.Connection.Execute("spGetUserName")
      strUserName=rs("NTUserName")
      rs.close: set rs=nothing
   
   'if the connection is using SQL authentication, use the user id from
   'the MSODSC connection
   else
      strUserName=MSODSC.Connection.Properties("User ID")
   end if
   
   'apply a serverfilter to the page for the current user name
   MSODSC.RecordsetDefs.item(0).ServerFilter="UserName='" & strUserName & "'"
                       

7. Close and save the data access page.
8. To test the page, open it in Microsoft Internet Explorer 5 or later. Note that you only see records where the UserName field contains your user name.

back to the top

REFERENCES

For information about how to determine the user name in a data access page in a Microsoft Access database (MDB), see the following article in the Microsoft Knowledge Base:

251326 How to Determine the User Name in a Data Access Page in a Microsoft Access Database (MDB)

back to the top

Additional query words: inf adp

Keywords: kbhowto kbhowtomaster kbdap kbclientserver kbdapscript KB259224

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.