Access 2007 halts a macro using TransferDatabase method

[Rob]

We have an .mdb file which is distributed among several disconnected users,
and we periodically issue an update to the database with an "updater"
database. This updater copies new records to the users' Db's, plus it copies
a large collection (a couple hundred or so) of queries using the
TransferDatabase method.

One of our users recently went to Access 2007, and is having trouble with
the last part: the program in the macro stops before sending the gueries to
the target DB to ask for the user's permission to complete the operation. It
asks this for each of the 200 queries (which is clearly a problem). How can
I get around this problem? Is there a security setting I can suggest that
the user use, or an option in the TransferDatabase method which might work?

 

[Jim Burke in Novi]

If you're just saying you don't want to be prompted to run the queries, you
probably just need to go into the DB, go to Tools, Options, Edit/Find tab and
make sure 'Confrim Action Queries' is not checked. It's possible that going
to the new version of Access may have reset this. You can set that in VBA
using

Application.SetOption "Confirm Action Queries", False

I'm assuming that would still work in Access 2007.

 

[Rob]

Jim,

No, it's not actually running the queries that brings up the alert; the the
code which copies the queries onto the user's version of the database. No
action query is performed however, so I think there's a setting that needs to
be checked/unchecked as you suggest, but not this one.

Rob

 

[Rob]

I think I found the answer. The way to get around the confirmation dialogs
is to add the target database's directory to the "Trusted Source" list. The
code then runs fine.


Thanks.