Macros blocked in distributed DB

[s goforth]

I am attempting to distribute a 2007 Access db with mainly VBA code but 2
Macros. The Macros are Autoexe which starts a function and a macro to handle
the ribbon. The db has been developed on a machine running Office 2007 under
VISTA.

When opened on a machine with Windows XP using AccessRunTime 2007, the
program fails with a message (error 2950) saying all macros are blocked.

How do I allow macros?

 

[Tom Wickerath]

Hi STG:

I believe this article, by Jeff Conrad, has what you need:

Using Access 2007 Runtime, how can I create a Trusted Location
if there's no UI to create it?
http://accessjunkie.com/faq_33.aspx


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[s goforth]

Thanks for your prompt reply. I have carefully read the post to which you
referred and found it helpful, but it doesn't get me all the way to a
solution.

AccessJunkies' discussion of setting trusted locations using Access works on
the source machine with VISTA and Access 2007. I have used that approach.
However, the receiving machine with Windows XP and AccessRunTime doesn't
offer that opportunity.

I am clearly over my head at the point of modifying the register of the
recieving XP machine with code. I suppose that with access to the other guy's
machine I could use regedit if he trusted me. I don't feel that planning to
routinely using regedit on someone else's machine is something I should do.

That seems to leave a script which I do not know how to write. Is there a
cook book source for a simple script that can somehow be incorporated into
the package that will set C:\SRSRA as a trusted location for my program on
the other guy's machine?

On second thought that seems to be something that MS would make to be
impossible. That would just open the door to malware.

 

[Pete D.]

If you follow the instructions in the link given and download the free
developer/runtime you can add the registry key using the installer. You
could also sign the code with a digital certificate and have users accept
you as a trusted publisher. It is different right now but with 2007 release
it is soon to become the norm so might as well get over the hump now.

 

[Arvin Meyer [MVP]]

It should work about the same with XP or Vista. The Trusted Location is an
Office 2007 feature, not an operating system feature.

You should also be able to set your Security Level to Low and once done,
answer the macro prompt only once. I believe that is done from the Office
button at the upper left corner, but my Access 2007 laptop is at the office,
so I can't say for sure.

 

[s goforth]

Several good replies, but still no gravey!

AccessRunTime does not display an "Office button", nor can I find a way to
get to the security settings for Access 2007 when the database is started on
a computer which has no Office program other than AccessRunTime.

I used regedit to check the setting for trusted locations on the computer
using AccessRunTime and the db appears to be in a location listed in the
register as trusted.

It should work about the same with XP or Vista. The Trusted Location is an
Office 2007 feature, not an operating system feature.

You should also be able to set your Security Level to Low and once done,
answer the macro prompt only once. I believe that is done from the Office
button at the upper left corner, but my Access 2007 laptop is at the office,
so I can't say for sure.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Tom Wickerath]

Hi STG:

AccessRunTime does not display an "Office button", nor can I find a way to
get to the security settings for Access 2007 when the database is started on
a computer which has no Office program other than AccessRunTime.

Here's one way that should work, as long as you install the runtime to the
same folder on each machine (ie. a constant database path). On your
development machine, use Regedit to open your registry. Navigate to the key
in question. From Jeff's article, this is:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted
Locations\Location0

Verify that the AllowSubFolders and Path (REG_SZ) keys have the correct
values shown. Then back up just this branch of the registry (Registry >
Export Registry File...). Make sure that the option button is selected for
the branch in question. Save the file. This should produce a new text file,
which you can open with any text editor, that has the .reg file extension.

Take this .reg file to a new computer that does not include a copy of Access
2007. Double-click on the .reg file to add the contents to the registry. Then
try installing your runtime solution. If this much works, then you may be
able to (?) find a way to include this .reg file with an installation package
(I've actually not tried that myself, since I deal with Access 2003
solutions, and I don't have a need to worry about runtime issues). Otherwise,
as Pete D. indicated, you should be able to add the registry key to the
installer (again, not something I've ever tried, since I don't deal with
runtime solutions [thank goodness]).

Good Luck!

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pete D.]

You can create a file with the .reg extension to write the key for you. See
http://support.microsoft.com/kb/310516 for instructions on the format.
Also, Office 2007 gives total control of the install (trust center settings)
to system administrators and they can block macros/code at many levels.
Good news is you can see what has been blocked by looking at trust center
for greyed out options to see what is blocked. Bad news is runtime doesn't
provide you access to look at the trust center so you may be stuck. You
don't mention if the non-working machine is on a network or work computer.
Checking with the network folks at deployment location may provide insite
into if some options have been locked out. For instance, changing trusted
locations is locked out at my work so I must sign all projects with a
certificate if it isn't installed in a predetermined allowed location such
as My Documents directory. It gets tricky when I split file into front and
back at work to put the backend on a server. I have to be careful not to
leave any code/macros in the backend by accident. Just food for thought.
Pete D.

 

[s goforth]

Tom and all,

Thanks for your good input. I have followed your instrustions and beeen able
to change the response to macros on the development machine just as expected.

On the development machine with the regular version of Accesss 2007, after
deleting the registry entry the db stops at the first macro. The option of
enabling the macro is offered and, if selected, the db runs even without the
registry entry. I can re-enter the proper values in the registry and the db
runs just as desired. The machine does not even need to be re-booted.

On the machine with only AccessRunTime 2007, I have carefully added the
location of the db as a trusted location in the registry as you suggested,
Tom, and I am confident the entry is correct. The program still stops with a
2950 error and no option to continue is offered.

I have tried everything suggested and still have no success.

Hi STG:

AccessRunTime does not display an "Office button", nor can I find a way to
get to the security settings for Access 2007 when the database is started on
a computer which has no Office program other than AccessRunTime.

Here's one way that should work, as long as you install the runtime to the
same folder on each machine (ie. a constant database path). On your
development machine, use Regedit to open your registry. Navigate to the key
in question. From Jeff's article, this is:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted
Locations\Location0

Verify that the AllowSubFolders and Path (REG_SZ) keys have the correct
values shown. Then back up just this branch of the registry (Registry >
Export Registry File...). Make sure that the option button is selected for
the branch in question. Save the file. This should produce a new text file,
which you can open with any text editor, that has the .reg file extension.

Take this .reg file to a new computer that does not include a copy of Access
2007. Double-click on the .reg file to add the contents to the registry. Then
try installing your runtime solution. If this much works, then you may be
able to (?) find a way to include this .reg file with an installation package
(I've actually not tried that myself, since I deal with Access 2003
solutions, and I don't have a need to worry about runtime issues). Otherwise,
as Pete D. indicated, you should be able to add the registry key to the
installer (again, not something I've ever tried, since I don't deal with
runtime solutions [thank goodness]).

Good Luck!

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Several good replies, but still no gravey!

AccessRunTime does not display an "Office button", nor can I find a way to
get to the security settings for Access 2007 when the database is started on
a computer which has no Office program other than AccessRunTime.

I used regedit to check the setting for trusted locations on the computer
using AccessRunTime and the db appears to be in a location listed in the
register as trusted.

 

[Tom Wickerath]

Sorry, I seem to have missed your earlier reply. I have just notified the
author of this tip, Jeff Conrad, and asked him to look at this thread.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[roccogrand]

Thanks Tom for checking with Jeff about this issue.

Based on Jeff and John's statements in Inside Out, it's almost worth buying
Access 2007 just to get around all the constraints imposed by the runtime
version such as having to add error handling to every macro in a distributed
app. I ask, Why did Development make macros primary when the developer has
to edit them just make an app work in runtime? With A2003 we had VBA as
primary and the wizard created error handling statements. Perhaps the
wizard's code wasn't the best but it worked and having to rework an app
wasn't necessary.

David

 

[Tom Wickerath]

Hi David,

I think it's pretty much worth having your clients just purchase a copy of
Office Professional, instead of dealing with run-time issues, unless you have
some type of special circumstances, such as a non-profit organization that is
budget strapped, or a lot of users. My guess is that you'd want at least
15~20 users minimum, for a given application, before deciding that dealing
with various run-time related issues was worth the effort.

I ask, Why did Development make macros primary when the developer has
to edit them just make an app work in runtime? With A2003....

I guess because they have developed a true fear of having any VBA code in an
application. None of the downloadable templates for Access 2007 include any
VBA code. In fact, if you want to submit a template, one of the requirements
is that it not have any VBA code, not even a simple MsgBox "Hello World!".
While I have a copy of Access 2007, I am not using it much at all. By far, my
efforts are firmly concentrated on using Access 2003.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[roccogrand]

Excellent reply Tom. It may just work.

Last week the client asked for a contract tracking app and I gave them the
pros and cons of going with Access 2007. They selected Access 2007 for
future compatibility and so getting them to invest in it now may not be all
that difficult. Hopefully, the proof of concept that I built over the
weekend will get them to trust me again, but I have never had so many
problems with an Access app before. With the contracts app, I am staying
away from multi-value fields, far away. I do need to use the Attachments
data type.

They'll need seven more Office licenses and a gaggle of Trusted Location
set-ups.

David

 

[roccogrand]

Yes, I can probably get around using it in the production app but for the
proof of concept, it was a quick way to show that users will be able to open
external files regardless of whether they are Word, Excel, PDF, or MapPoint
files associated with a particular contract.

Unlike with my use of multi-value fields, I will research Attachments in
depth before using it in future apps.

I welcome being informed about bad experiences with Attachments.

David

 

[Jeff Conrad [MSFT]]

After carefully reading through all the various replies, I'm now starting to
wonder if something else is at issue here.

You briefly mentioned that your AutoExec macro runs a function. Could you
describe in more detail what this function does? I'm wondering if this is a
References issue.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Jeff Conrad [MSFT]]

Hi David,

I was not around during the development cycle for the 2007 release (so don't
assume my next statements are totally accurate), but I think part of the
reason for the wizards-writing-embedded-macros-change were security
concerns. Also, you have to remember that even though the pre-2007 wizards
wrote VBA code, it wasn't the "best" code around. Specifically, it used a
lot of outdated DoCmd.DoMenuItem.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[roccogrand]

Thanks Jeff,

I understand from a mutual colleague that you are relatively new to the
Development Team. And I have read over and over why the Team made macros
primary and VBA secondary. The problem is that the user doesn't have the
option of selecting which mode is primary. Although I am not a coder, I have
gotten lots of value from the code wizard in the past five years of using
Access. Much of the value comes from the error handling.

With my first app for a client that has a mixed full version and runtime
environment, I am now faced with adding error handling to every macro that
Access created (Inside Out page 1328). When I started the app I wasn't aware
that A2007 was using macros and not VBA. I was going along dumb and happy
until I needed to have a detail form open a full-page form and found myself
opening a macro, not VBA code like I had seen Access do since 2000.

It would have been OK with me to have copied OpenForm code from my Access
2003 apps but after weeks of working on the app, there I was with my pants
down around my knees.

I would like to thank you and John for writing Inside Out. It should be
required reading for any wannabee developer. Page 1328 by itself is worth
the price of the book.

David

 

[Jeff Conrad [MSFT]]

Hi David,

in message:

Comments in-line...

I understand from a mutual colleague that you are relatively new to the
Development Team. And I have read over and over why the Team made macros
primary and VBA secondary. The problem is that the user doesn't have the
option of selecting which mode is primary. Although I am not a coder, I
have
gotten lots of value from the code wizard in the past five years of using
Access. Much of the value comes from the error handling.

Preaching to the choir on that one. I agree that it would be nice if the
user could voluntarily select an option to have the wizard write VBA code
versus embedded macros. The closest you can come is to keep the database in
the MDB format, because the wizard writes VBA code if you use that format.
You could keep it in MDB format, create your procedures, and then update the
format to ACCDB. Not a wonderful solution, but it is a workaround.

With my first app for a client that has a mixed full version and runtime
environment, I am now faced with adding error handling to every macro that
Access created (Inside Out page 1328). When I started the app I wasn't
aware
that A2007 was using macros and not VBA. I was going along dumb and happy
until I needed to have a detail form open a full-page form and found
myself
opening a macro, not VBA code like I had seen Access do since 2000.

My vote would have been that the wizard write out some basic macro error
handling as well since it was a new feature in Access 2007.

It would have been OK with me to have copied OpenForm code from my Access
2003 apps but after weeks of working on the app, there I was with my pants
down around my knees.

I hate it when that happens.

I would like to thank you and John for writing Inside Out. It should be
required reading for any wannabee developer. Page 1328 by itself is worth
the price of the book.

Thank you for the nice comments, I really appreciate it.
If I had known, however, that we only needed to write one page.......
Just kidding.


--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com