Security alters using Runtime 2007

[markmarko]

So I'm testing deployment with the backend on a server and front ends
packaged to use Runtime.

There's a startup warning (which I just found some solutions to, so probably
won't need advice here, but just in case someone has a great tip).

But also, when user action triggers append queries, user gets two warnings:
"You are about to run an update query that will modify data in your table."
and after clicking Yes there's another warning "You are about to update
1row(s)."

Using Access 2007, I can go to options and turn off those warnings, but
Runtime has no options settings. I had assumed the package would retain such
options, but apparently not.

Any advice on avoiding those warnings?

 

[Tim Johnson]

Have you tried, at the beginning of the event trigger, entering the code:

DoCmd.SetWarnings False

?

If not, this should work, assuming that this trigger is based off of a form
for a database that you have/are developed(ing), and that you are designing
using a full version of Access.

You can also reverse this, should you choose, at the end of the trigger
(otherwise the instance of Access will continue to ignore these warnings
throughout the session) by using 'True' instead of false in the above code.
Hope this helps

 

[Allen Browne]

Execute the query statements, instead of using RunSQL or OpenQuery.

Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Tony Toews [MVP]]

But also, when user action triggers append queries, user gets two warnings:
"You are about to run an update query that will modify data in your table."
and after clicking Yes there's another warning "You are about to update
1row(s)."

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

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
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[markmarko]

Thanks, all. I'm going to try Mr. Browne's strategy, it seems to fully
address the issue. I'm a little leery of turning of warning messages, as Mr.
Toews points out, it can run havoc later.

 

[markmarko]

Well, I'm having a little difficulty implementing the Execute technique
instead of OpenQuery.

Here's the SQL from the original query:
UPDATE [List-Codes-Sales] INNER JOIN [Record-Details-Sales] ON
[List-Codes-Sales].SalesCodeID = [Record-Details-Sales].Product SET
[Record-Details-Sales].Quantity = [TempVars]![NumberOfPayUnits],
[Record-Details-Sales].[ThisEntryIncludedInOrder?] =
IIf([TempVars]![NumberOfPayUnits]>0,Yes,No)
WHERE ((([Record-Details-Sales].[ThisEntryIncludedInOrder?])=Yes) AND
(([Record-Details-Sales].Product)=DLookUp("SalesCodeID","List-Codes-Sales","Code
= 'PU'")) AND
(([Record-Details-Sales].SalesOrder)=[Forms]![Entry-SalesOrder]![SalesOrderID]));


and here's what I've attempted to implement to run the SQL in VBQ using
Allen Browne's model:

"UPDATE [List-Codes-Sales] " & _
"INNER JOIN [Record-Details-Sales] " & _
"ON [List-Codes-Sales].SalesCodeID = [Record-Details-Sales].Product " & _
"SET [Record-Details-Sales].Quantity = [TempVars]![NumberOfPayUnits], " & _
"[Record-Details-Sales].[ThisEntryIncludedInOrder?] =
IIf([TempVars]![NumberOfPayUnits]>0,Yes,No) " & _
"WHERE [Record-Details-Sales].[ThisEntryIncludedInOrder?]=Yes " & _
"AND
[Record-Details-Sales].Product=DLookUp(""SalesCodeID"",""List-Codes-Sales"",""Code = " & Chr(39) & "PU" & Chr(39) & Chr(32) & """) " & _
"AND [Record-Orders-Sales].SalesOrderID= " &
[Forms]![Entry-SalesOrder]![SalesOrderID] & ";"

I receive the error 3061 "Too few parameters. Expected 2"

I'm at a loss about how to resolve it.

 

[markmarko]

Ok, I've changed some of it so references to variables are calculated (not as
literal strings). This code gets the error "too few parameters. Expected 1" ,
so at least it's down from 2!

"UPDATE [List-Codes-Sales] " & _
"INNER JOIN [Record-Details-Sales] " & _
"ON [List-Codes-Sales].SalesCodeID = [Record-Details-Sales].Product " & _
"SET [Record-Details-Sales].Quantity = " & [TempVars]![NumberOfPayUnits] &
", " & _
"[Record-Details-Sales].[ThisEntryIncludedInOrder?] = " &
IIf([TempVars]![NumberOfPayUnits] > 0, """Yes""", """No""") & _
" WHERE [Record-Details-Sales].[ThisEntryIncludedInOrder?]=""Yes"" " & _
"AND [Record-Details-Sales].Product= " & DLookup("SalesCodeID",
"List-Codes-Sales", "Code = 'PU' ") & _
" AND [Record-Orders-Sales].SalesOrderID= " &
[Forms]![Entry-SalesOrder]![SalesOrderID] & ";"

 

[markmarko]

Ugh, so I finally found the problem... It was a type, because I had to make
this change for 5 append/update queries, so I copy/pasted the last line, and
in this particular case, I needed to reference a different table.