access 2007 reproduceable bug, dir() cannot be called from macrocondition?

[Alain Bourgeois]

Dear all,


I have an access (2000) application running without any problem in
access 2000 / 2002 / 2003.

This application has more than 400 macros. Some macros have a similar
code in the "condition" column:
Dir ("c:\afile.mdb")>""
And if the condition is true, some processings are done.

In access 2007, although I am not on run-time, that I enabled all
macros, I am in a trusted location, and I am not running in sandbox
mode, I get the error message that there is a function that microsoft
access can't find in an expression (This can be reproduced very easily
by creating a one-line macro with the code Dir ("c:\afile.mdb")>"" in
the condition, setting a msgbox action, then running the macro).

The Dir() function can be called and works from a module in access 2007,
but not from a macro. When chosing "runcode" as action, clicking "...",
build-in functions, Dir() and dir$() do not appear in access 2007 (these
appear under access 2K/XP/2K3).

I would like to make it work. Does someone know if a bug fix exists for
this or if this bug is known at MicroSoft?

I know an "easy" workaround would be to replace Dir() with MyDir() and
to create a MyDir(string) function in a module. OK for 1 macro, but I
have > 400 macros and I don't even have the possibility to identify the
macros where there is a Dir() in the conditions, so this workaround is
not suitable for me.

Furthermore, are there other build-in functions than Dir() having the
same problem?????

Thanks for your help.


Regards,
Alain

 

[Allen Browne]

Hi Alain

I can confirm the problem, on WinXP (so it's not an o/s issue), in an MDB
(not just ACCDB), in a trusted location and with Sandbox off (so it's not a
security issue.)

You can work around it with a wrapper function:
Function MyDir(strFile As String) As String
MyDir = Dir(strFile)
End Function

But that doesn't address your questions about why it fails, of if there are
other functions that fail also. Perhaps someone else can contribute
thoughts/experiences.

 

[Allen Browne]

Alain, this is due to sandbox mode.

Microsoft explains how to turn it off here:
http://office.microsoft.com/en-us/access/HA101674291033.aspx#2

The macro should work after that.

 

[Alain Bourgeois]

Allen,

Changing the registry key didn't help for me.
We had several problem bringing an app to access 2007, disabling sandbox
solved some problems... but not this one!

Regards,
Alain

 

[Allen Browne]

Solved it here, Alain.
I was able to reproduce your issue.
Then closed Access, hacked the registry, restarted, and Dir() in the macro
worked.

If yours did not, there must be another factor, such as permission or
virtual folders, or ...

 

[Alain Bourgeois]

Allen,

in your first post, you wrote you were able to reproduce the problem
"with Sandbox off".
Which value did you type in registry key?
Are you sure you didn't change something else?

 

[Allen Browne]

I was wrong.

I tested on a new laptop, believing sandbox was off.
When I double-checked, sandbox was not turned off for ACE.

That was the only change I made to get it working.

 

[Alain Bourgeois]

Thanks Allen, seems to work now.

I was wrong.

I tested on a new laptop, believing sandbox was off.
When I double-checked, sandbox was not turned off for ACE.

That was the only change I made to get it working.

 

[Alain Bourgeois]

Latest investigations:
If I don't change sandbox mode, no "custom" function can be called in a
macro condition in access 2007 (it works fine in other versions).
If I create the following function in a module:

Function MyDir(s as String) as String
Mydir(s)=dir(s)
End Function

-> I cannot run a macro if
MyDir("c:\file.txt")>""
is in the condition.

This will probably cause some incompatibilities for our applications.