HowTo: for each record in query result do some VBA code ?

[Poweruser]

Another question I've always asked myself:

Is there any possibility to do some sort of "for each record in query result A do some VBA code (e.g. call a VBA procedure)?

Best regards,
D.R.

[NeoPa]

Indeed.

1. Open the QueryDef as a recordset in your code. I typically use the DAO option for this, but there is an ADODB version available too.
2. Process through the recordset using MoveNext().
3. Call your code within each iteration of your loop.
4. Continue While Not .EOF or Until .EOF.

[NeoPa]

Depending on exactly what you require, there is also the option of calling a function (NOT subroutine. It must return a value) as part of the query itself. It would have to be defined as Public in a standard code module, but as well as determining a value for you, you could get it to go off and dance somewhere too.

[Poweruser]

Wow, thanks again for your answer! I googled QueryDef and found enough information.

Question: Are there any (maybe hidden) problems, when using QueryDef with access tables connected to mysql (linked table).

Best regards,
D.R.

PS: There should be some kind of "thank you"-button, so anybody can see that you're giving very good and quick answers. Maybe you should become an Access MVP :)

[Poweruser]

This time I hope I'm not bumping too early.

One question is still open:

Are there any (maybe hidden) problems, when using QueryDef with access tables connected to mysql (linked table)?


Best regards,
D.R.

[NeoPa]

Wow, thanks again for your answer! I googled QueryDef and found enough information.

First let's look at the term QueryDef. Since you need to Google it, it must not be as obvious as I'd expected. QueryDef is simply the correct term for the queries that are saved in an Access database. Every saved query is a QueryDef. A Query is a looser term which covers QueryDefs, but also SQL strings called from within code in Recordsets etc.

This time I hope I'm not bumping too early.

The bump is probably not too early, but as I haven't yet had a chance to look at your previous post, was probably unnecessary. Certainly not a problem. For your peace of mind though, remember that forum based web posts are picked up as and when by various different members. For instance, I may well take a few days off sometimes if I go away from home for any reason. A lack of response for many hours, or even a day or two, doesn't necessarily mean a lack of interest or intention, just that this has to fit in with other things.

Question: Are there any (maybe hidden) problems, when using QueryDef with access tables connected to MYSQL (linked table).

One thing to bear in mind, is that the fact that this is managed by a back-end server means that any permissioning done there will effect such queries. One user may have different rights from another, so it may work correctly for one but not the other. If you're using linked tables though (where the linking is managed outside of the QueryDef object), it should all work pretty smoothly.

PS: There should be some kind of "thank you"-button, so anybody can see that you're giving very good and quick answers. Maybe you should become an Access MVP :)

Very kind of you to say so, but from what I see of the Access MVPs, they contribute more than I do at a professional level. This is just a hobby for me. I enjoy it of course, but cannot devote enough time to it even to be considered for that.

On the thanking side, a little note of thanks, as you've done, is always well appreciated :) Aside from that, if you feel an answer (post) particularly hits the spot then you can select it as the best answer. It's always nice to see one of your posts has merited that.