query in macro not working

[WPW07]

Hello,

I've been asked to take a look at an Access 2003 database. They use a
macro behind a button that contains 28 queries (make table, append,
and update). For some reason, the second to the last query
(qryAppendNewEmail) that's supposed to run in the macro doesn't work.
Warnings are set to no on the first macro line. All the others seem to
run properly. When I run the query manually it runs fine. Has anyone
experienced this before?

Some time ago, I converted some macros to code with a built in tool.
Does anyone know what that is, and do you think that's the best
course of action--convert the whole thing to code?

Thanks!

 

[Allen Browne]

As you say, the problem with RunSQL (or OpenQuery) in a macro is that you
either get lots of annoying confirmation dialogs, or you have no idea what's
working or why it doesn't work (if you turn SetWarnings off.)

A better solution is to use Exeute in code. You don't need to turn
SetWarnings off, and you can be notified if it fails at any point (using the
dbFailOnError switch.) But it's not quite as simple: if the queries rely on
the Expression Service to resolved expressions such as Forms!Form1!Text0,
they will need modifying (or extra action) to handle these parameters.

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

 

[John Spencer]

How to convert a macro to a vba module

Choose the macro
Choose Save As from the file menu
Fill in the desired name
Change the combobox to Module
Click OK
Click Convert

Is it the best way? In my opinion, yes.

You can then turn on error handling, get error messages, branch as needed,
display progress, ...

I would post the SQL of the qryAppendNewEmail (View: SQL from the menu).
Perhaps there is something in the query that is causing the problem when it is
not run directly from the query window.

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

 

[Tom van Stiphout]

On Tue, 26 Aug 2008 05:14:41 -0700 (PDT), WPW07 <[email protected]>
wrote:

And when this step fails, is the db left in an inconsistent state, or
is everything rolled back? Oh, I forgot, no transaction management in
macros. And turning off warnings, seems not such a great idea anymore
when you're debugging, does it?

-Tom.
Microsoft Access MVP

 

[WPW07]

And when this step fails, is the db left in an inconsistent state, or
is everything rolled back?  Oh, I forgot, no transaction management in
macros. And turning off warnings, seems not such a great idea anymore
when you're debugging, does it?

-Tom.
Microsoft Access MVP

Thanks for all of your replies. I figured out the problem. In the
Macro, the comment line for the particular query had the correct
name. The actual query name in the Action Arguments section had the
wrong query, one that differed by one word. I found this out only by
converting it all to code and running it.

Thanks again.