Docmd run update query

Here is the code I have. It look like you need to include the command UPDATE and becauswe the table name has a space in the name you need brackets around the name.

Something like this. If you ever get to the point where you're running large numbers of queries from VBA, I suggest for you to use one of the alternatives to DoCmd. RunSQL such as:. This is supposed to be faster than CurrentDB. Execute since it doesn't refresh the list of tables prior to running a query, or something like that. This is supposed to be faster than DoCmd since it doesn't refresh the list of tables prior to running a query, or something like that.

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.. If you're going to use docmd. 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. As always YMMV. This code works brilliantly, unfortunately the problem is getting the update query to run via VBA. The only drawback to using "DBEngine 0 0. In this case it would be cumbersome to click and run each of these queries individually, remembering their order carefully.

Here is where VBA comes to our rescue. All we need is the name of that query. An online shopping website offers vouchers to customers whose order value is greater than or equal to But only the latest order is considered for the gift. All other orders will be assumed as duplicate. Another condition is that the customer should have been a member of this online shopping account for the past days.

Let us assume that these details are combined from several tables into one. But here, we are using this scenario to explain an easy way of using the openquery method of the docmd object. The output would contain the contact details and names of customers who are eligible for the gift.

As this VBA is run, we will see confirmation alerts, just like how they pop up when we double click on the queries directly. Connect and share knowledge within a single location that is structured and easy to search. OpenQuery can run it while Currentdb. Execute gives an error message 'too few parameters'. I created another saved update query without input from control or function and Currentdb.

Execute worked. I don't want to see the warning message from Docmd. OpenQuery and I dont want to mess around by turning on and off the warning. Anyway of getting Currentdb. Execute work on this? When you want to update a column of some certain records with another column's value and IIf function, it is better to use DAO. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow.

Learn more.