How to Force a Confirmation on a Select Query?

[aregan1]

Hi everyone -

I have a user who developed his own Access 2000 database. In general, he did a pretty good job. But he has a question that I haven't been able to answer. Here goes:

This user has written many Select queries. He is using the resulting data grid from a select query to update the access tables. (I hadn't even realized that the tables could be updated through the Select query results!)

Recently he accidentally updated some data in this manner that caused him a lot of headaches. So this is what he wants: before the change he made to the select query results actually updates the table, he wants to be prompted with "Do you really want to change this data?" In case he accidentally changed the data, he wants to be able to back out of it. I can't see how it's possible to give him this confirmation message. Can it be done? If yes, how?

Note: He also wants the same type of confirmation message to appear when he's updating a table directly. Again, is this possible? I can't see how.

Thanks for your advice...

- Anita

 

[Isadore]

Anita: You can call up a confirmation vb dialog box, with a Yes, No or Cancel button on it, and open this prior to running your update. However, from your description of the problem not quite sure how the SELECT turns into UPDATE. Pls explain with a tad bit more info.

 

[scriverb]

A Select query cannot on its own UPDATE a table. But, a Select query can be the input to a UPDATE query that links to a table and the update can be made. This is probably what is happening in this application.

Look at the code that is calling the process to update the tables where the problem is occuring. Since there is no automatic confirmation messages showing up there is probably some code like this:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdQueryName"
DoCmd.SetWarnings True

This would be the update query that needs to be investigated as having the Select query as input. By just removing the SetWarnings commands you can have the system prompts for confirmation returned to the process. Or you can use something like the following:

Dim vResponse as integer
vResponse = MsgBox("Are you sure you want to update the tables?", 292, "Update Confirmation&quot
If vResponse = 6 then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdQueryName"
DoCmd.SetWarnings True
else
MsgBox "You have cancelled the update of records"
end if

Let me know what you find in this database for more assistance.


Bob Scriver

 

[aregan1]

Thanks to both of you for responding. Let me explain further:

This is one of the select queries that my user is running:

SELECT [section schedule].term, [section schedule].[course key], [section schedule].[instruction type], [section schedule].instructor, [times, unique].[meeting times], [section schedule].[time period], [section schedule].location, [section schedule].[instruction load]
FROM [times, unique] INNER JOIN [section schedule] ON [times, unique].[time period] = [section schedule].[time period]
WHERE ((([section schedule].term)="FA03&quot)
ORDER BY [section schedule].[course key], [section schedule].location;

When I open this query, it shows me a data grid of the resulting rows. I can change the data in a column of one of the rows, click on the "X" to close the data grid, and when I look at the "section schedule" table itself, the change that I made to the column in the data grid has been applied directly to the table! (Bob, like you, I didn't think that a select query could update a table, but that is what is happening!)

There is no code calling this process. The user is opening the query directly (no forms involved). And unless I'm completely in the dark, I don't know of anyplace to put code within the query itself. I'd love to be able to put a confirmation dialog box, but where? And again, I need to also put a confirmation when the user edits a table directly, which I also don't think is possible.

I hope this explains better what is happening. Maybe you can try updating the results of a select query yourselves, to see if you get the same results as me. I'm really curious about that, since I also used to think that a select query couldn't update a table!

Thanks again for your help...

- Anita

 

[scriverb]

You problem is that you are allowing the end-user to have direct access to your database objects(tables, queries, etc.) For a secure system you should never allow this to happen.

What I meant is that a SELECT is by design not meant to perform an Automatic update to the underlying table. Usually a SELECT query is used to populate a form, subform, report, etc. You can control the user when they review a SELECT query by using the query as the Source Object of a subform control on a Main Form. This will appear much like the datasheet fiew of a query but you as the programmer can set the subform control property LOCKED to True. Now the data can be reviewed and not changed.

Once you have your forms setup and a menu to open automatically we can help you to keep the user from getting into the Design Area. You will never have a secure database if you allow the user access to these database objects. I have heard programmers comment that the best security for a database system is to not distribute it to the users. But, that is the extreme and we can surely help you with a compromise solution.





Bob Scriver

 

[aregan1]

Bob -

Thanks for your response. You confirmed what I suspected. I agree that if I developed this database for the user, I would have created forms for them to use to edit tables and query results, and I could have controlled when updates actually happen.

However, this is a case of a little knowledge being a dangerous thing. The user was clever enough to develop his own database, and uses it extensively. He essentially uses queries and tables to update his data directly, rather than taking the time to build forms. But he wants the same kinds of controls built in, as if he had taken the time to create the forms initially. My answer to him will be that he can't have it both ways.

Thanks again for confirming my suspicions...

- Anita

 

[scriverb]

Always remember that a little bit of knowledge is definately a dangerous thing if not supported by caution.

Who said this?? Me. Many times when working Users over the years.

Bob Scriver