Knowledge Base

How to determine the user name in a data access page in a Microsoft Access 2000 database (MDB)

Article ID: 251326

Article Last Modified on 7/3/2006

APPLIES TO

Microsoft Access 2000 Standard Edition

This article was previously published under Q251326

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

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

By using WIN32 API functions, you can determine the current network user name of a user who is logged on to a Microsoft Windows 2000 or Microsoft Windows NT network. Because, for security reasons, scripting languages such as VBScript and JScript do not support API calls, these functions cannot be called from a data access page. This article shows how you can modify a data access page and use server-side script to return the network user name.

This article shows you how to:

modify a table to include a field for network user names.
build the data access page that is based on the modified table.
add server-side script to the page to return the user name.
edit the HTML in the page to read the server-side script and to lock the controls.
save the data access page to a Web server as an Active Server Page (ASP).
set the permissions on the Active Server Page on the Web server to return the user name

The network user name can be used to:

display the currently logged on user on the page.
filter records based on the user name.
lock records if the user name does not match the name in a UserName field.

The last two options require that the network user name is stored in the table for the records to be filtered or locked.

In a Microsoft Access database (MDB), to determine the network user name, you have to save the data access page as an Active Server Page and then you have to run it on Microsoft Internet Information Server (IIS) 4.0 or later. This is because the server-side script requires the Active Server Page interpreter to execute

Modifying a table

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Open the sample database Northwind.mdb, and then open the Customers table in Design view.

Add the following field to the Customers table:

   Table: Customers
   --------------------
   Field Name: UserName
   Data Type: Text

Save and then close the table.
Open the table in Datasheet view. Type values in the UserName field. Be sure to include values that match your network user name (including case sensitivity), and some that do not.

Building the data access page

In the Database window, click Pages under Objects, and then click New.
In the New Data Access Page box, click AutoPage: Columnar, click the Customers table in the Choose the table or query where the object's data comes from box, and then click OK
On the View menu, click Design View to open the page in Design view.

Adding server-side script

With the page open in Design view, point to Macro on the Tools menu, and then click Microsoft Script Editor.

Type or paste the following code immediately before the </HEAD> tag:

<%
  'Returns the network logon name.
  Function GetLogonUser()
      GetLogonUser = Mid(Request.ServerVariables("LOGON_USER"), _
        Instr(1,Request.ServerVariables("LOGON_USER"),"\")+1)
      If Request.ServerVariables("LOGON_USER") = "" Then 
        Response.Write "Access Denied." & vbCrLf & _
          "You must log into the Domain to access this web page."
      End If
  End Function
%>

NOTE: The percent sign (%) around this code tell the Active Server Page interpreter that this code is server-side script.

Editing the HTML

Search for the HeadingText H1 tags that were added by the data access page. The default HTML added by the data access page is:

<H1 id=HeadingText 
style="DISPLAY: none; FONT-WEIGHT: normal; COLOR: inactivecaptiontext" 
MSOTITLE="YES">Click here and type title text</H1>

Replace the HeadingText H1 tags and contents with the following P and SPAN tags:
```
<P id=HeadingText style="FONT-WEIGHT: normal">
You are currently logged in 
as: <SPAN id=MySpan><%=GetLogonUser()%></SPAN>
</P>
                        
```
NOTE: The <% tags tell the Active Server Page interpreter to run this as server-side script, and are used to retrieve the value from the GetLogonUser function. You must type or paste this code as is, with no spaces between the server-side script and the SPAN tags.
Locate the Script Outline to the left on the screen.
Expand the Client Objects & Events folder, and expand the MSODSC.
Double-click the Current event. This will insert a client-side script block for the event.

Modify the script block as follows:

<SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(info)>

Type or paste the following code into the MSODSC Current:

<!--
'Check to see if the UserName stored in the table equals the
'currently logged on user. If it does not, then lock all controls  
'by setting the readOnly property.

dim el, textBoxes

'Create a custom collection of TEXTAREA tags.
set textBoxes = document.all.tags("TEXTAREA")
    
if MySpan.innerText<>UserName.value then
    for each el in textboxes
        el.readOnly = true
    next
    msgbox "You cannot edit data."
else
    for each el in textboxes
        el.readOnly = false
    next
    msgbox "You may edit data."
end if
-->

NOTE: This code will make all the controls read-only if the current network user does not match the user name in the current record.

Close the Microsoft Script Editor.
Save the data access page as MyCustomers.htm. If you are working on the Web server, save the page to <Drive>\inetpub\wwwroot, where <Drive> is the drive letter where you have IIS installed.

Saving the page as an active server page

Locate the file in Windows Explorer, make a copy of it, and then save the copy as <FileName>.asp where <FileName>.asp is the name of your ASP file.
Put the ASP file on a Web server in a folder with Scripts permissions.

Setting IIS permissions

IIS will return an empty string ("") for the user name if Allow Anonymous is enabled on the page. Allow Anonymous must be turned off at the page level in IIS to return the network user name. To do so, follow these steps:

In IIS, right-click the ASP file, and then click Properties. Click the File Security tab.
If you are using IIS 5.0 on Microsoft Windows 2000 Server, under Anonymous Access and Authentication Control, click the Edit button, and then click to clear the Anonymous Access check box. If you are using IIS 4.0 on Windows NT 4.0, click to clear the Allow Anonymous Access check box.
Make sure that the Integrated Windows authentication check box or the Basic authentication check box is checked. If the Basic authentication check box is checked, make sure that SSL is used on the Web site. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

298805 How to enable SSL for all customers who interact with your Web site in Internet Information Services
Click Apply to save the changes, and then close the properties window.
To test the page, browse to the Active Server Page from your Web browser, and then move to a record where your user name is in the UserName field. Note that you can edit these records. But you cannot edit records where your network user name is not in the UserName field.

REFERENCES

For more information about retrieving a user name on a data access page in an ADP, click the following article number to view the article in the Microsoft Knowledge Base:

259224 How to determine the user name in a data access page in an Access 2000 project

For more information about retrieving network information using API functions, click the following article number to view the article in the Microsoft Knowledge Base:

210088 How to retrieve workgroup information under Win32

For more information about Request.ServerVariables, click the following article numbers to view the articles in the Microsoft Knowledge Base:

188717 Request.ServerVariables("LOGON_USER") returns empty string

196074 How to store the authenticated user name in a session variable

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

299973 Using Microsoft Jet with IIS

Additional query words: inf dap

Keywords: kbhowtomaster kbdap KB251326

Microsoft KB Archive/251326

Contents