Solved How to get affected records count of an update query? (1 Viewer)

[ebs17]

because I never work outside of currentDB

I explicitly included a DB reference as an argument because I basically wanted to take into account unlinked tables from external databases. The test used by @MarkK via MSysObjects will then fail in this form.

Of course, one have to initialize this reference before calling it. But that was less of the content of this post. An attentive observer can create a CurrentDb or Opendatabase on their own. Sometimes there are already persistent references that one would then use.

@MarkK
I can share your thoughts on stricter refactoring, especially since I have a comparable function for select queries that returns a recordset. GetQuery and SetParams would therefore be immediately reusable, which means that encapsulation is an immediate advantage.

If you write a procedure longer than 8 or 10 lines, start to look for units of work you can delegate to subroutines.

Like @KitaYama, I see this as a very hard limit that can cost you the overview and create additional overhead.
I would be interested to see whether you implement this consistently in your own projects. In any case, one have to put a lot of effort into naming the subroutines in terms of descriptive names.
My limit would be more the number of lines of code that I can read at a glance without scrolling (around 30 - 40 lines).

Where is parameter name used?

In my case, directly in the query. Parameters here are given the prefix "par".

 

[The_Doc_Man]

notice especially the good sense in breaking problems up into tiny chunks.

I don't have anything technical to say here. You are getting good help. But I want to emphatically agree with the quoted statement. The "Divide and Conquer" method worked ever since the days of Caesar's Gallic Wars. It's a good philosophy that deserves attention.

 

[MarkK]

The test used by @MarkK via MSysObjects will then fail in this form.

No, the QueryExists() function I wrote in refactoring your code uses the database reference passed to the initial function. It will look in MyDB.MSysObjects, not CurrentDb.

There are naming errors in my GetQuery() function.

 

[MarkK]

I haven't tested, but this function seems a bit off.

Yes, those are errors. I never compiled that code.

 

[MarkK]

cutting down every function to less than 10 lines of code, seems too strict to me.

If you write a procedure longer than 8 or 10 lines, start to look for

I don't insist with any form of strictness that you not write procedures longer than 10 lines. I recommend that when you do so, "start to look" for the embedded units of work. Much of the time, your code can be simpler and more readable if you break them out into subroutines.

but after 10 or 20 years

Reusability is not about 10 or 20 years, it is about writing a common routine in a module that other routines in that module can reuse.