What's the Difference Between Docmd.Setwarnings and Currentdb.Execute

What's the difference between DoCmd.SetWarnings and CurrentDB.Execute

They do not both mask errors. DoCmd.SetWarnings masks errors and is system wide, not confined to the single application that you are using. DoCmd.SetWarnings False without the corresponding DoCmd.SetWarnings True will mean that action queries will run without any prompts in any Access application on the PC.

Execute does throw warnings, the warnings that you need, such as the query failed to execute, but does not give warnings you may not need, such as "Are you sure you want to run this query".

In this thread Allen Browne, Access MVP, says he does not use Set Warnings.

As an aside, I would generally recommend using an instance of CurrentDB, as this will allow you to return a record count, amongst other things, so:

Set db = CurrentDB
db.Execute sSQL, dbFailOnError

MS Access currentdb.excute has different result than Docmd.RunSQL

Very odd. But I've never had to work much with SQLServer tables. To use RunSQL and not get warnings, turn them off and on.

DoCmd.SetWarnings False
DoCmd.RunSQL ...
DoCmd.SetWarnings True

Why does this SQL statement ask for a value?

DoCmd.RunSQL "INSERT INTO List_Of_Agencies(T_PI_GroupName) VALUES ('" & test & "')"

CurrentDb.Execute Error 3061 and DAO.Recordset Error

In MS Access, stored queries can refer to open form or report controls as parameters. However, queries called via DAO methods like CurrentDb.Execute, do not see the GUI interface and hence cannot evaluate form or report controls.

So to continue using form controls as is, simply save your SQL statement as a stored query and call it with DoCmd.OpenQuery (which do not need to be closed for action queries like UPDATE, INSERT, DELETE). This is the coding counterpart to clicking the stored query via Navigation Pane.

DoCmd.OpenQuery "mySavedUpdateQuery"

Do note the above will raise prompts of data changes. To suppress such prompts, use DoCmd.SetWarnings:

DoCmd.SetWarnings False
DoCmd.OpenQuery "mySavedUpdateQuery"
DoCmd.SetWarnings True

Trying to update table in ms Access using VBA but getting failed , used multiple option but table not getting updated

If [Date & Time] is a date/time type field, use # delimiter instead of '. If field is a number type, don't use any delimiter.

Remove the comma in front of WHERE clause.

Need [ ] around Asset-ID field name because of the hyphen.

Advise not to use spaces nor punctuation/special characters in naming convention.

Currentdb.Execute with dbFailonError not throwing an error

Use Option Explicit, like Hans said. Always use Option Explicit!

You're missing a reference to the Microsoft Office ##.# Access Database Engine object. This is where dbFailOnError is defined. Because you don't have that reference, dbFailOnError is not defined. This reference is added to all Access databases by default, and I strongly recommend adding it.

And because you're not using Option Explicit, VBA doesn't mind that it's undefined and just casts that undefined variable to a zero.

If, for some reason, you don't want to add the reference, use the corresponding value for dbFailOnError:

.Execute testsql, 128

MS Access CurrentDb.execute not working

I don't why I hadn't seen this before but thanks to HansUp I got it to work.
The problem is that I was using a reserve word in the SQL query, so had to encapsulate the column name in brackets [TIMESTAMP]

Thank you all.

---

Related Topics