Solved

Sandbox mode blocking expressions or maybe not

Posted on 2007-03-23
16
1,656 Views
Last Modified: 2013-11-29
Hi Experts,

I'm trying to understand why I'm having such difficulty deploying an Access database to other computers.  We have two databases developed in Access 2003 and deployed using the Custom Startup Wizard and the Package Wizard in the 2003 Developers Extensions.  Basically, they only run correctly when the Macro Security Level is set to Low.  

What's so confusing about all this is that they both have a certificate attached (at first it was just a self-certificate, then later we bought a "real" certificate) and when I ran the "Find Expressions Blocked in Sandbox Mode" within the Custom Startup Wizard, it came back on both files and said there were none.  Yet from what I understand of the SandBox Mode, it's behaving exactly as if that's the problem.

What's more, I've been able to actually track down the offending line of code in one of the databases.  (It broke when it tried to reference a sub-sub-report)  The other database is less obvious.  The error is occuring somewhere where one combo box is referencing another combo box and has a before update as well as an after update action.  But like I said before, these errors only show up when the Macro Security Level is set to Medium.

The most troubling part of this is that on my computer, where some or most of the work was done on these databases, the database works great regardless of the Macro Security Level!  What up with that?  It makes me very queesy to think that there could be these types of errors in the databases that we're developing and I would not have any way to detect them on my machine.

I guess my question in all this is
1) what's going on? Why is it that they work fine on my computer and not on anyone else's?  Are the expressions being blocked by the sandbox mode and if so, why can't the startup wizard find them?
2) how do I fix it?  Is the only solution to perform all the testing on a different computer?  How do I know that it will behave correctly on someone else's computer?
0
Comment
Question by:Colelspg
  • 8
  • 5
  • 3
16 Comments
 
LVL 84
ID: 18783449
I'm not sure why the startup wizard won't find your errors, but in my experience the startup wizard does a good job at some things and a not-so-good job at others.

Regardless, you can get this to work on other machines, but you must carefully follow the steps needed. Have you seen this link:

http://office.microsoft.com/en-us/access/HA011225981033.aspx#180

This is topic #18 from that link:

How can I prevent the "Unsafe expressions are not blocked" message from appearing each time I open a file in Access 2003?

You need to do two things to avoid this warning — install Jet 4.0 SP8 or later, and enable sandbox mode.

Perform the following steps to download the service pack and enable sandbox mode.

   1. Download Jet 4.0 SP8 or later.

      For more information about downloading the latest Jet service pack, see About Microsoft Jet 4.0 SP8 or later. Installing all the critical Windows updates will automatically install the latest version of the Jet service pack on your computer. If your computer is up to date on critical Windows updates, you can skip this step. To install critical Windows updates or to verify that all critical updates are installed on your computer, visit Microsoft Windows Update.
   2. Exit, and then restart Access.
   3. Open a file.

      The "Unsafe expressions are not blocked" warning will be replaced with a message that asks whether you would like to block unsafe expressions.

      Security Warning: Unsafe expressions are not blocked.
   4. Click Yes. This will enable sandbox mode.
   5. When prompted to restart Access, click OK, and then exit and restart Access.

      The registry will be updated and Access will run in sandbox mode. You will not see the warning when you open a file on your computer as long as Jet 4.0 SP8 or later is installed and sandbox mode is enabled.

<end paste>

If you haven't followed these steps carefully, your enduser will continue to get the prompt. So make SURE they're at the correct Jet SP level, and then follow the steps outlined above carefully.

Regarding testing: You should ALWAYS test on a machine which is NOT your development machine. As you know, your dev machine has every file/control it needs to run correctly, while an enduser machine may not. I use vmWare, which allows me to build virtual machines so that I can exactly match my enduser configuration. Microsoft is now giving VirtualPC away for free (http://www.microsoft.com/windows/products/winfamily/virtualpc/default.mspx) although you still need to have the licensing for the various operating systems and programs you install. If you're going to develop professionally you would be well advised to invest in an MSDN subscription (and you can join the Empower program to get a subscription at a very good price: https://partner.microsoft.com). This way you can have every operating system Microsoft has ever put out, along with every piece of software (many with multiple licenses).
0
 
LVL 84
ID: 18783457
0
 

Author Comment

by:Colelspg
ID: 18783680
Thanks for the links, but  I had all ready checked them out before.  I still haven't been able to find anything the tells me for certain that this behaviour is caused by the sandbox mode.  It's not receiving the error "Undefined Function ..."

In one database, a certain report contains a bunch of "#Error" text boxes.  In this report, I was able to trace the offender to a text box that referenced a sub-sub-report.  If I changed it to a dlookup function (which I should have used in the first place, I know) the "#Error" went away.

But, here's the thing: when the database is opened with the Macro security level set to Low, everything works fine, even without the dlookup modification.  So what's really going on?  Doesn't the fact that the error goes away in Macro Level Low mean that the sandbox mode is blocking something?  Could there be something else going on, that is affected by setting the macro security level?

By the way, your point about testing is well taken and thanks for the link, I wasn't aware of the Virtual PC option.
0
 
LVL 75
ID: 18787540
"Doesn't the fact that the error goes away in Macro Level Low mean that the sandbox mode is blocking something?"

*** Absolutely 100% For Sure YES!!!!  ****

If you examine the original documentation (which I don't have handy at the moment) on Macro (in)Security from Microsoft ... there are a LOAD of commonly used functions ... which all developers that write code use on a DAILY basis ... that Microsoft considers 'unsafe' and will NOT work when Macro Security is set to Low.

This all came about because some dude (ok I suppose it could have been a female, but I DOUBT it) sent someone an MDB with an AutoExec macro that called some function which executed the Shell command which attempted to Delete C:\*.* ... in effect ... or something close to that. At the time ... circa 2003-2004 ... it was called a 'virus' ... in an MDB ...  because 'technically' ... something was 'executable' ... mainly the Shell command.

My colleagues and I ran some tests about 2-3 years ago on A2003 / Macro Security ... and we were able to circumvent it in several different ways, rendering it basically useless. So, unless you set macro security to Low, your code just isn't going to work.  Now ... maybe that's changed in A2007?  And ... I'm only speaking of Access ... can't say for Word, Excel ,etc.

In fact ... Garry Robinson (of www.vb123.com) wrote an article in Access Advisor circa that time period about the fallacies of macro security ... which lead my colleagues and I to investigate the subject ... per what I stated above.  If you search Google / Garry Robinson / macro security ... you will find the info.  At this very moment ... his site is 'doing routine maintenance' ... or I would include those links.

I know ... the big scare is ... "What if one of my clients receive an MDB from someone else that contains a 'virus' (per above) ... and Macro (in)Security is set to Low ... and they open it ... and get hosed!  Well .... what can I say?  Your clients know You ... and you know your clients, right?  Are they really receiving MDB's from other people?  My clients are not ... and I have warned all of them about the 'issue'.  You just can't protect everyone from themselves. Like ... you KNOW ... that many of your friends are STILL opening email attachments that contain viruses, malware, spyware, blowware, underware and the like ... cuz they just-don't-get-it!

mx


0
 
LVL 84
ID: 18792218
You can "get around" the issue by never using the disallowed functions in your Event handlers, form/report properties, or in a query:

=SomeFunctionName

But instead using the an event (like the Form's Current event) to run your code. Many devs have simply advised that their clients set macro security to Low, but my major clients are large government installations (NASA/Goddard, NRL, several major aerospace companies) and their IT staff has STRICT policies regarding settings and such. I've never been one to advocate calling functions in Event handlers, as I think it's a bit of a hack, so my journey was easier than others, but all in all it was pretty simple. The main thing to remember is this:

"Jet 4.0 Sandbox mode blocks the following Access functions and properties when called from an expression in a Jet query or from an Access property."

And a list of Functions follows (this is from the link at the second comment I posted). You can call ANY function from a VBA routine, but you cannot use many of them in a query or property ... such as an Event handler ... It's a bit more difficult with queries, but I don't typically call functions in queries so that wasn't a big issue for me. If you do, then you'll need to rewrite that code to avoid the function call, or covert your stored query to an "inline" query.
0
 
LVL 84
ID: 18792237
"I still haven't been able to find anything the tells me for certain that this behaviour is caused by the sandbox mode.  It's not receiving the error "Undefined Function ...""

I'm not sure about that, as I haven't seen your code, but merely setting Macro Security to Low does NOT enable sandbox mode. You may have sandbox mode enabled already; again I don't know this, but you could verify this by disabling sandbox mode (the link above shows what registry key to edit to enable/disable Sandbox mode), then try setting your Macro security to various levels to see what conditions cause the problems. I've also found that you don't always get the "Undefined Function" error message when this occurs, depending on several things (for example, if you've got error handling, you may be intercepting that message, or if you've turned Off system warnings, etc etc).
0
 

Author Comment

by:Colelspg
ID: 18794092
mx
<that Microsoft considers 'unsafe' and will NOT work when Macro Security is set to Low.
shouldn't this be
<that Microsoft considers 'unsafe' and will NOT work when Macro Security is set to Medium or High.
or did I miss something?

LSM
Thanks for hanging in there, I know it's a pain to try to do this without seeing the file (which unfortunately I'm not allowed to post).  When I checked the sandbox mode in the registry, it is set to 3 on both computers, but only runs properly on mine.  I'd still feel better if I could identify the reason why it works on my computer but doesn't work an a computer that, as far as I can tell, is identical to mine.  
I'm 99% sure that I am not using anything that is listed as "blocked" on the Microsoft links above, but there's a lot of code and I didn't write all of it ...
0
 
LVL 84
ID: 18794470
So both macines have Sandbox enabled ...

Have you actually seen the machine where the problem occurs? Or is a remote user describing what's happening? When I was reworking my apps to try and straighten this out, I had several users who reported "errors" when in fact they were viewing the security alerts generated by MS, and just hitting the Enter key immediately (which will not do what they expect). Once I got them to stop and actually read the messages to me I got them straight.

Hate to sound like a broken record, but are you absolutely 100% certain that the offending machines are using Jet Service Pack 8?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Colelspg
ID: 18794868
The computer is right here next to me.  I'm 100% certain that it has XP Professional Ver 2002, Service Pack 2, and I thought SP2 included Jet Service Pack 8. (?)  Right now it can't connect it to our network and hence the Internet, so I can't try a re-install. Other than connecting to Windows Update, I don't know how to verify if Jet Service Pack 8 is installed.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 18796054
Colelspg:
"shouldn't this be
<that Microsoft considers 'unsafe' and will NOT work when Macro Security is set to Medium or High.
or did I miss something?"

Yes/No ... sorry.  Typo as I was multiplexing between questions this and multi-user issues in the multi-tasking, multi-threading environment we operate in.  My apologies ...

mx
0
 
LVL 84
ID: 18796322
To verify, locate the MSJet40.dll, right click and check the Properties for the version. SP8 is this version:

4.0.8015.0

So that number or higher would be sufficient. I believe you're correct, XP w/ SP2 would be sufficient, here's a link that shows the version of Jet 4.0 and their corresponding numbers:

http://support.microsoft.com/kb/239114

Can you compile the database on the offending machine? I know that you may be shipping an .mde file, but it might be worth dropping the .mdb version of it on the machine and trying a compile ... make sure you don't have any odd code lying around that may be causing troubles.
0
 

Author Comment

by:Colelspg
ID: 18796425
msjet40.dll version 4.0.8618.0
When I open the mdb on the offending machine, and go to tools - macro - visual basic editor, then under debug - compile, it doesn't give me an error.  It also isn't finding any missing references from the tools - references menu.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
ID: 18804186
But when you attempt to run it on that machine, you get the security warnings? Do you get BOTH of them ... if not, let us know exactly what happens when you open the db on the offending machine.

Also, when you open the db on the offending, did you check the Digital Certificate settings on that db? It should show the Certificate Name in the top section of the Digital Signature section.
0
 

Author Comment

by:Colelspg
ID: 18824980
Sorry I'm taking so long to reply, I'm getting pulled in different directions right now.
On both machines, I get the same set of security warnings.  When it's at low, there are no warnings.  When changing the setting from low to medium, there is the warning about blocking unsafe expressions.  At the medium setting there is the usual "This file contains macros ... do you want to open?"

As far as Digital Certificates, it makes no difference when opened on the offending machine.  I tried sending two copies, one with a certificate and one without.  The offending machine found the certificate, but behaved the same on both dbs.

One thing I did discover that I should have picked up on earlier is that the offending machine is MS Access 2003 while the other machine is MS Access 2003 SP2.  I knew that they were both Access03, but didn't check the service packs.  Sure enough, when I unstalled the update, low-and-behold, it works.

Thanks for hanging in there with me LSM.  It's a great comfort to at least have identified what was causing it to behave the way it was.

By the way, out of curiosity, what does the "L" stand for?
0
 
LVL 75
ID: 18825009
Colelspg .... Thank You !!

LSM ... lots of good info ... logged, noted and Saved :-)

Macro (in)Security ... SCRAAAAAAREEEEEM !!!!!

lol.mx
0
 
LVL 84
ID: 18826518
Great info ... good to know re: service packs. Glad you got it fixed, and that's definitely one to file in the old memory banks.

BTW: LSMConsulting = Louis Scott McDaniel Consulting <g> ... me full name ...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now