Access is always saving design changes - how did I do that?

[Michelle K.]

All design changes I am making in my database are saved on object close.
There is no "save changes?" question asked. Is there a setting that does
that?

 

[Dirk Goldgar]

All design changes I am making in my database are saved on object close.
There is no "save changes?" question asked. Is there a setting that does
that?

Most often this is caused by having code that executes the statement:

DoCmd.SetWarnings False

.... without ever turning the warnings back on by executing the statement:

DoCmd.SetWarnings True

This can quite easily happen if an error is raised after turning the
warnings off, without error-handling code that traps the error and ensures
that warnings are turned back on again.

 

[Jerry Whittle]

Somewhere, either in a code module, code behind a form, or a macro, you have
Set Warnings set to False.

Often people do this before running things like action queries so that the
'you are deleting x records' message doesn't pop up. What sometimes is
forgotten is to Set Warnings back to True near the end of the code or macro.

 

[John Spencer]

Probably you have used DoCmd.SetWarnings False or used a macro to do so.
And you are missing the DoCmd.SetWarnings True to fix this.

Try running
DoCmd.SetWarnings True
via the VBA immediate window. See if you get the prompt once you have done that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Michelle K.]

Many thanks to all 3 correct responses. I had found a post earlier that said
to type "?setwarnings true" in the immediate window -- did that, but still no
prompts for saving/not saving. Your DoCmd.SetWarnings True in the immediate
window worked perfectly. Now to dig into the code and find the errant
SetWarnings False without a matching True . . . .

Again, thank you all!

Michelle K.

 

[Hans Up]

Many thanks to all 3 correct responses. I had found a post earlier that said
to type "?setwarnings true" in the immediate window -- did that, but still no
prompts for saving/not saving. Your DoCmd.SetWarnings True in the immediate
window worked perfectly. Now to dig into the code and find the errant
SetWarnings False without a matching True . . . .

I think it's better to avoid fiddling with SetWarnings. It might seem
useful at times ... like to suppress confirmation messages when running
an "action query". But you can run an action query differently, without
needing to change SetWarnings to False, and still avoid those
confirmations. Here is an example showing both approaches.

Public Sub Exec_qryFooInsert()
Const cstrQuery As String = "qryFooInsert"
'INSERT INTO tblFoo (foo_date)
'SELECT Now() AS foo_date;

'* method 1 *'
DoCmd.SetWarnings False
DoCmd.OpenQuery cstrQuery
DoCmd.SetWarnings True

'* method 2 *'
CurrentDb.Execute cstrQuery, dbFailOnError
End Sub

If you have other situations where turning off SetWarnings seems useful,
tell us about them and someone can probably suggest an alternative which
doesn't require messing with SetWarnings.

 

[David W. Fenton]

Public Sub Exec_qryFooInsert()
Const cstrQuery As String = "qryFooInsert"
'INSERT INTO tblFoo (foo_date)
'SELECT Now() AS foo_date;

'* method 1 *'
DoCmd.SetWarnings False
DoCmd.OpenQuery cstrQuery
DoCmd.SetWarnings True

'* method 2 *'
CurrentDb.Execute cstrQuery, dbFailOnError
End Sub

The last switch, dbFailOnError, should never be used without an
error handler.

An alternative to either of these is to use my RunSQL() function,
which I've posted many times (you can Google my name and "RunSQL"
and you'll get many links). It's basically Hans's method 2 with an
error handler, and intended to function as a drop-in replacement for
DoCmd.RunSQL().

 

[Tony Toews [MVP]]

All design changes I am making in my database are saved on object close.
There is no "save changes?" question asked. Is there a setting that does
that?

I feel that docmd.runsql and docmd.setwrnings should never be used.

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
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/