Update Query in VBA

[Montez659]

Is it possible to trigger an Access Update query via VBA, or is this another one of those parameter issues?

I had

DoCmd.OpenQuery qryUpdateAfterImport

which
triggers an update to a table from a table, but it doesn't seem to be triggering but also there are no error messages.

Tried to wade through what Google threw back at me, but those all deal with an SQL string in VBA, which is possible, it will just be a fairly lengthy bit o' code. I'd ratehr trigger the already concoted update query.

Thanks wise men and women!

 

[Montez659]

And of course I found it 3.265 seconds after I post.

CurrentDb.Execute "qryUpdateAfterImport"

 

[boblarson]

And of course I found it 3.265 seconds after I post.

CurrentDb.Execute "qryUpdateAfterImport"

You should also use the part in red so it returns an error if there is an error or else it won't:

CurrentDb.Execute "qryUpdateAfterImport", dbFailOnError

 

[Montez659]

Thanks. Will that return an error code too or just fail and move to the next line?

 

[xenou]

I could stop running code if there is an error, so introduce an error trap:

On Err Resume Next
CurrentDb.Execute "qryUpdateAfterImport", dbFailOnError
If Err Then
    '//Update Failed.  Let somebody know.
Else
    '//All's well that ends well.
End If
On Error GoTo 0

 

[Montez659]

Thanks Xen. I've always been a bit sketchy on proper error trapping. I don't know how many books that I have read but for some reason my brain has a hard time wrapping around it.

 

[xenou]

All in good time.
I think if you read (and re-read) the VBA help on Resume and follow the links to the related articles you'll get all you need to know. VBA error handling is not as sophisticated as in most languages today (i.e, Try-Catch structure). But it gets the job done. Note that it's better not to use a lot of error trapping while writing code because you learn a lot when your code crashes and you are thrown into debug mode - mainly, you know exactly where the error occurred.

I should have given an example like this, as you can get more information about the error.

On Err Resume Next
CurrentDb.Execute "qryUpdateAfterImport", dbFailOnError
If Err Then
    [COLOR="Blue"]Msgbox "Error " & Err.Number & ": " & Err.Description[/COLOR]
Else
    '//All's well that ends well.
End If
On Error GoTo 0