[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Anyone know what happened to the Environ$ variable in Access2007?

Posted on 2007-08-09
20
Medium Priority
?
602 Views
Last Modified: 2013-11-27
In Access 2003, I would create a Query, and part of my critieria would be =Environ$("UserName").  The query would return stuff specific to that user.  That's not working in Access 2007.  Any ideas anyone?
0
Comment
Question by:Faulkenator
  • 9
  • 5
  • 4
  • +1
20 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 19667228
Drop the $ sign:

Environ("UserName")

Works for me from the immediate window in 2K7.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19667243
Although, I just tested it with the $ sign and it works as well.  This could be an issue with macro security levels... I'm unfamiliar with how that works in and affects 2K7 databases.  In Access 2003, if you had issues calling this function from a query or property sheet, you would use a wrapper function like this instead of using Environ("Username") directly:

Public Function GetUsername() as string
    GetUsername = Environ("Username")
End Function

and subsequently call that function from your query like this:

SELECT *
FROM  YourTable
WHERE fldUser = GetUserName()

0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 19673613
"if you had issues calling this function from a query or property sheet, you would use a wrapper function like this instead of using Environ("Username") directly:

Public Function GetUsername() as string
    GetUsername = Environ("Username")
End Function"

Frankly this is just plain silly advice. Wrapping a function that's (allegedly) causing problems in some other function is not going to make the problem go away.

That said: Environ() returns values from the computers environment variables. If the computer's login/startup script does not set a value for USERNAME, then Environ("UserName") will return a blank string. Far more reliable is to use an API call to retrieve the user name directly from the OS. See...

http://www.mvps.org/access/api/api0008.htm
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 61

Expert Comment

by:mbizup
ID: 19673737
>Frankly this is just plain silly advice.

Please take a look at this article:
http://www.vb123.com.au/toolshed/04_docs/sandbox.htm

The author specifically mentioned trouble in a query, not in VBA.  He did not mention whether it was producing an error, or simply returning nothing.  Since the functions work for me in VBA (2007) I considered the possibility of a Sandbox issue.  The workaround for that in Access 2003 is to put "Unsafe Expressions" into VBA wrapper functions instead of calling them directly from property sheets and queries.  

Maybe I'm wrong about this for 2007, which I am just starting to use.

Most of all, If you are in doubt about another member's advice, phrase your comment in a professional manner.  Or better yet, let the author reply.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19673789
btw, here is Microsoft's list of unsafe expressions:
http://support.microsoft.com/kb/294698

If you look at the second list of functions, Environ is one of the expressions that can potentially cause errors when called from property sheets or queries (this is not an issue when these functions are embedded in VBA).

Still sound silly?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19673949
I went ahead and tested this in 2007.  My comment about macro security describes *exactly* what is happening here.  

If you use Environ$("UserName") in a query in Access 2007, you will receive an "Undefined Function" error (I made no modifications to any of the default security settings).

If you embed it in VBA in a wrapper function:

Public Function GetUsername() as string
    GetUsername = Environ("Username")
End Function"

And call it from your query like this:
SELECT * FROM  YourTable WHERE fldUser = GetUserName()

It works.  Try it :-)

API Functions are arguably better than Environ, but this directly answers the question "what happened to the Environ$ variable in Access2007?"





0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 19674045
Mea Culpa ;-(
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 19674100
Mea Culpa, but... :-)

Talking from a no knowledge basis (still in A2k2 land), if Environ$("UserName")  throws and Undefined Function error, does Environ("Username") do the same thing? Have MS does away with the $ version, or do neither work?

Understanding no changes have been made to security, is it JET that is complaining about that one built in function (Environ/Environ$), or does the new default security model not allow query calls out to a whole group of functions?

Is it a big deal to modify the security policy to permit calling Environ from a query, as distinct from calling it from VBA?

Who makes these security decisions at M$?  The same people who made Vista poll all the hardware 20 times a second to criple your machine?  :-/

0
 
LVL 61

Expert Comment

by:mbizup
ID: 19674340
Environ and Environ$ both work the same way.  Neither can be called from a query or a property sheet (for example you can't set the Default value of a textbox to Environ("username").  Both will work if called inderectly through a VBA function.

Take a look at that second group of functions in the MS link of Unsafe Expressions.  None of them work from queries or property sheets.  For many of them, I can't think of a reason to include them in a query (you can't call Beep() in a query).  But some do have their applications, and the queries would need to be rewritten in order to run under the default security settings.

This was not an issue in A2k2, it was a feature introduced in 2K3.  The default security settings in 2K3 were such that this only became an issue as security levels were increased.  It looks like 2K7 defaults to these higher security settings.  I imagine that the security settings in 2K7 can be lowered so that this is not a problem.  I'll check into this and post back later.
0
 
LVL 1

Author Comment

by:Faulkenator
ID: 19674654
Wow, I didn't think my silly little question would garner so many replies.  Thanks guys:  Yes, as one of the first things I did, was to write a function and wrap the Environ$ command in that function and that worked fine, but this would now force me to have to revisit all of my references. Ugh, and no big deal, but:  I like to get at the ROOT CAUSE of a problem, which is why I posed the question.  I am definitely interested in the security settings as a possible solution.  One more thing to have to add to the login policies. . .

In MS Access, I have set my Macro Settings to "Low", but I guess in "Vista Land", that's not good enough.  I sitll don't understand why Microsoft, who gave us the Environ$ function, would think it's not a secure thing to make available to a PC user.  "The Lord giveth, and he taketh away."  It seems silly to have it available in the VBA portion, but not the query or properties portion.  The user who is going to maliciously write a query in MS-Access can equally write stuff in MS Access-VBA, so what has been gained?

Thanks again for all the comments.  I will now go back and reread them (instead of skimming them)  to get a better understanding of the issues.
0
 
LVL 1

Author Comment

by:Faulkenator
ID: 19674683
Ok I read the link:

http://www.vb123.com.au/toolshed/04_docs/sandbox.htm

As for me, I have three databases in the queue behind this one to develop.  I don't have time to recode because MS can't keep the bad guys out of my system.  And, it may not be as true now as it was in older versions of Access, but calls to VBA functions in a query or property would slow it down to a crawl.

Yet another frustration. . .

Time to see if the Sandbox mode fix works in 2007.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19674771
This link is relevant to Access 2007:
http://office.microsoft.com/en-us/access/HA101674291033.aspx#2

What you need to do is:
- Verify (with your employer/sys admins/powers that be) that you are not violating any organizational security policies by altering Access's sandbox security level.
- Change the Sandbox registry key setting from 3 to 2

This will allow you to run unsafe expressions (like Environ) directly from your queries and property sheets without using wrapper functions.

The A2k7 link I gave you has step-by-step instructions for doing this in both Vista and XP.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 19674777
http://office.microsoft.com/en-us/access/HA101674291033.aspx#2
(Copied):

Close all instances of Access that are running on the computer for which you want to disable sandbox mode.
In Windows Vista:  
- Click the Start button , point to All Programs, click Accessories, and then click Run.
- In the Open box, type regedit and then press ENTER.
The Registry Editor starts.

- Expand the HKEY_LOCAL_MACHINE folder and navigate to the following registry key:
\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines

- In the right pane of the registry editor, under Name, double-click SandboxMode.
The Edit DWORD Value dialog box appears.

- In the Value Data field, change the value from 3 to 2, and then click OK.
- Close the Registry Editor.
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 19676081
Faulkenator:

"calls to VBA functions in a query or property would slow it down to a crawl." is indeed the bind. However there's no need to make thousands of calls to VBA to get the user name at all, consider this.

All you need do is call Environ, or the API approach once in the code that *launches* the query you want to run. Capture the current user ID, stuff it in a table (e.g. CurrentSession), and use that table in a INNER join in your query instead of having the criteria expression.

SELECT * from Whatever
INNER JOIN CurrentSession ON Whatever.UserName=CurrentSession.UserName;

Now you have an all SQL solution, at the cost of a JOIN vs. a VBA call.

mbizup:

Many thanks for the education, and apologies once again.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19676453
Not a problem. :-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19676975
Just to mention - for interested readers -  that a function call in a query needn't be a killer.
What *does* severely harm query performance are calls to functions which themselves make data requests.
Standard output manipulating function calls aren't nearly so harmful (but obviously have an impact) especially if used upon fields as criteria.

However a function call like this described here shouldn't be a drain on performance against Jet data - as the function is efficient enough and called precisely *once*.
There is no repeated execution of a function unless that function receives a parameter which has to be evaluated for each row.  i.e. accepts a field as a parameter.

Hence
SELECT FunctionName() As MyField
will see the function called once
SELECT FunctionName([FieldName]) As MyField
will execute the function for each row.  And if you view the query in a results window - *more* than just once.

Consequently Miriam's
SELECT *
FROM YourTable
WHERE fldUser = GetUserName()

should be quite fine as it stands.
And fldUser is free to be indexed for better performance - and that query would use such an index without interferance from the function call.
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 19679393
Very true. But if you are not using JET I think you may get the hit every time.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19681815
Morning all.

Not using Jet?  
How would you do that and yet include a VBA function call?

As the Acc data engine is just Jet+ lol, then perhaps you're meaning not using Jet based data (i.e. instead of a linked MDB, SQL Server tables or whatever) as the data source, and hence a linked table upon which a query is operating.
Well, that wouldn't matter - Jet is still wrapping the functionality.  It's fundamentally involved.

And so to the function call - that would still be called once.  Even as criteria.  Because Jet is wrapping the request.
What *might* (and I stress this is only "might") happen is that using non translatable SQL (i.e. referring to a local Access expression) then the filtering could occur locally.  However even this is not necessarily true.
Even if it did so - then the data overhead of it doing as much would far outweigh the execution time of the VBA function being called each time. :-)
But whether Jet and the ODBC provider build an efficient SQL statement to pass to the server - or do it locally, that function should still be called once - unless you have to pass a field parameter to it.
But that isn't the case here - and indeed, there's no indication in this thread (that I can see) that this isn't all MDB based data anyway.
0
 
LVL 1

Author Comment

by:Faulkenator
ID: 19684684
All you need do is call Environ, or the API approach once in the code that *launches* the query you want to run. Capture the current user ID, stuff it in a table (e.g. CurrentSession), and use that table in a INNER join in your query instead of having the criteria expression.

SELECT * from Whatever
INNER JOIN CurrentSession ON Whatever.UserName=CurrentSession.UserName;


Excellent suggestion.  I'll contemplate this approach.  One issue is that in a multiuser database, I have to implement some kind of structure for that too.  But that is easily do-able.

Thanks
0
 
LVL 1

Author Comment

by:Faulkenator
ID: 19684743
Thank you all for your help on this.

I am going to accept what follows as the quick answer.  (Thanks mbizup).  I learned a lot from all of your replies.

Rich:
mbizup:http://office.microsoft.com/en-us/access/HA101674291033.aspx#2
(Copied):

Close all instances of Access that are running on the computer for which you want to disable sandbox mode.
In Windows Vista:  
- Click the Start button , point to All Programs, click Accessories, and then click Run.
- In the Open box, type regedit and then press ENTER.
The Registry Editor starts.

- Expand the HKEY_LOCAL_MACHINE folder and navigate to the following registry key:
\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines

- In the right pane of the registry editor, under Name, double-click SandboxMode.
The Edit DWORD Value dialog box appears.

- In the Value Data field, change the value from 3 to 2, and then click OK.
- Close the Registry Editor.
Accept Multiple Solutions Accept as Solution
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question