Select specific records for a domain user.

[neptune2k]

Hi,

I'm creating an adp project in access for a my customer.

Customer's network configuration is:

Server:
Windows Server 2003 Small Business Server
SQL Server 2000

Clients:
10 and more clients running Windows XP Professional SP2 integrated in Active Directory
Microsoft Access 2000

The database is stored in SQL Server.
SQL Authentication is based on Windows Authentication.

I've this problem:

Every employee of the same level have privileges to read and write to a specific table, but i don't want that an employee read or write records within that table created by other employees.

table has this structure:

TABLE(username, field1, field2,...)

the question is :
It is possible to filter records by a specific employee?

I would be that an employee can view only the records he created when he open the adp project in his microsoft access 2000.

I figure out the adp project recognizes at startup the employee and than filters the table by his active directory username.

If it is possibile, is there a VBA function that retrieves current username who have logged in?

Thanks, al

 

[ajrimmer]

To find the SQL ID you can use:

CurrentProject.Connection.Properties("User Id")

 

[PWise]

in my adp
CurrentProject.Connection.Properties("User Id")
returns a empty string

 

[ajrimmer]

OK. I've read your original post again. You're using windows authentication (so no seperate login to the adp? ?as I've not done that).

Try this then.
In a VBA module:

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 25
Dim ret As Long, UserName As String
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = UserName & ""

Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox Err.Description
Resume Exit_GetCurrentUserName
End Function

(thanks/apologies to the original author-I can't remember where I got this from-no claims on writing it from me)

GetCurrentUserName should return their adf/windows login ID.

 

[PWise]

try

Environ("USERNAME")

For the current windows user

 

[ESquared]

pwise,

That way is not secure, because there are many ways for a user to set or change environment variables, including USERNAME.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]