Query using Environ("username") 2

[mych]

Hi,

I using a query I have used a thousand times and for the life of me can't see why it will not work this time!

I have a table TblLocalVars. One of the fields in this table is UserID which is a text field. I am running on NT4 workstations.

The SQL for my Query is:

SELECT *
FROM TblLocalVars
WHERE (((TblLocalVars.UserID)=Environ("username")));

This should select a complete record for the user ID that is currently logged on.

Unfortunately I get the following error;

Compile error in query expression '(((TblLocalVars.UserID)=Environ("username")))'.

My user ID exist in the table and if I run the following Query;

SELECT *
FROM TblLocalVars
WHERE (((TblLocalVars.UserID)="2972972"));

My record is returned. (My NT UserName is 2972972)

Has anyone experienced this? What have I done wrong?

 

[PHV]

Have you by chance installed the last mdac updates/patches ?
You may have problem with the sandbox mode of JetSQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mych]

Yes I installed VB6 yesterday. What I can't understand is if I open a previous DB app I created that uses the same type of query (it is my standard way of Identifiying who is logged on), It gives me the correct record.

What do I need to do to revert my mdac?

 

[PHV]

If your SELECT instruction is build on the fly by code, you may consider this:
strSQL = "SELECT * FROM TblLocalVars" _
& " WHERE UserID='" & Environ("username") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mych]

Thanks for this.

On another forum I was pointed in the direction some code written as a module that is better at identifiying the username. Aparantly Environ("username") has a tendancy to truncate usernames over 8 characters. This should not have made any difference to me as all our usernames are 7 characters.

Upshot is I'm now using fOSUserName code and it works. What I cant understand is on my older app Environ("username") works OK. You would have thought it whould have stopped working on both apps.... go figure!

 

[GingerR]

Hmmm....I don't think it truncates after 8 characters. I'll have to check that at work tomorrow. I've been using Environ("Username") for years at work, successfully. Lately tho, the Sandbox thing has popped up. I don't know how or why some machines in my group at work get the Sandbox setting of 3 instead of 2 because our software installations are pretty controlled. Anyhow, you might want to read about it. It's a security issue in new jet. Certain functions which they deem possibly harmful won't run in queries but will in vba. I've changed all my db's to add this:

Function GetUserID()
GetUserID = vba.environ("Username")
End Function

and then use GetUserID() in my queries. Fortunately there are only a few.

Sandbox Info



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mych]

Thanks, PHV and GingerR,

Problem sorted and I now have three ways of getting a Username.

Cheers
Mych