Access 2007 Runtime and Action Query Notices

[Doug]

I have developed an application using Access 2007. The user has installed
the MS Access 2007 Runtime. He has saved the database under his 'My
Documents' folder.

Question:
When I developed the application, it was in my 'trusted locations' and I
never received any notifications whenever an action query was executed (many
are in this application which append, delete and update tables in the
database). The user - however - who has the database saved in a location
that is not even on my PC receives continuous message boxes that he is about
to run a query that effects data... do you wish to continue... etc. If he
doesn't run the action query (accidentally hits 'no' on a message box), the
data no longer is reliable on the back-end and I have to manually fix the
problem. How do I turn these warnings off so all queries run when they are
expected to run on his computer?

 

[Arvin Meyer MVP]

Move the database to a Trusted Location. I'm not sure if a runtime is
allowed to turn off the warnings, but he should only get them once for each
action query.

 

[Doug]

Move the database to a Trusted Location. I'm not sure if a runtime is
allowed to turn off the warnings, but he should only get them once for each
action query.

Yes, but the Microsoft runtime does not seem to have an area to create/edit
trusted locations and

?!?

 

[Larry Daugherty]

If you are running the queries from VBA would it be effective to

Set Warnings Off
run your code
Set Warnings On
??

HTH

 

[Doug]

That's essentially what I did - and it works... I just had to do it for each
piece of code with an action query... there are many... and I'm not sure if
it is the best way to code - but it works - for now...

 

[Arvin Meyer MVP]

Yes, but the Microsoft runtime does not seem to have an area to
create/edit
trusted locations and

You can dink with the registry:

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted
Locations\Location1]
AllowSubFolders (REG_DWORD) = 1
Path (REG_SZ) "C:\MyTrustedDatabaseFolderNo1"

or run some code which does it Courtesy of MVP Graham Mendeno:

Public Function CreateTrustedLocation( _
Key As String, _
Path As String, _
Optional AllowSubfolders As Boolean, _
Optional Description As String, _
Optional Version As Integer = 12) As Boolean

Const cProcName = "CreateTrustedLocation"

Dim reg As New Registry, hk As Long
On Error GoTo ProcErr
With reg
hk = .OpenKey(HKEY_CURRENT_USER, _
"Software\Microsoft\Office\" & Version _
& ".0\Access\Security\Trusted Locations\" & Key, True)
.SetValue hk, "", "Path", Path

' Also try: .SetValue hk + 1, "", "Path", Path

.SetValue hk, "", "AllowSubfolders", IIf(AllowSubfolders, 1, 0)
.SetValue hk, "", "Description", Description
.CloseKey hk
End With
CreateTrustedLocation = True

ProcEnd:
On Error Resume Next
If hk <> 0 Then reg.CloseKey hk
Set reg = Nothing
Exit Function

ProcErr:
mb_Error cProcName
Resume ProcEnd
End Function

 

[Tony Toews [MVP]]

That's essentially what I did - and it works... I just had to do it for each
piece of code with an action query... there are many... and I'm not sure if
it is the best way to code - but it works - for now...

The problem with DoCmd.RunSQL is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony