macro question (A97)

Topic

New Reply

I’m trying to clean up some stuff from a previous programmer. As part of a process, a macro is called. They had this interesting habit of, while doing several append and update queries within the macro, opening select queries that may or may not have to do with a subsequent append or update query. This just displays data to no one but the server. The other thing that’s done is that, at the end of the process, every single query that was used is closed. So for example(using my favorite Disney characters):

qappMickey – an append query is run against the tblMickey table
qselMickey – a select query is opened on the tblMickey table
qupdMinnie – an update, based on qselMickey is run against the tblMickey table
qselMinnie, a select query is opened, based on the tblMickey table
qappMinnie – an append query is run against the tblMinnie table from the tblMickey table
qupdPluto – an update query is run against the tblMinnie table
close qupdPluto
close qappMinnie
close qselMinnie
close qupdMinnie
close qselMickey
close qappMickey
quit

Now, is this just one person’s interpretation of how to release memory, or is there some legitimate reason for doing this? If I remove the select queries and close steps, it doesn’t seem to cause any harm. I just want to be able to defend my actions down the line.

Reply | Quote

Replies

I would think that the only queries you have to close would be the Select queries. Which prompts the same question you asked, why are the select queries being run anyway?

Reply | Quote

It sounds like someone’s attempt at verifying the append queries worked. Did the person who wrote the macro have the ability to watch it run on the server? If so, that’s probably the case.

Macros….phewy…..

Reply | Quote

Yes, we can either walk into the room and look at the server, or we can log in remotely from another location. The explanation that was given to me when I started here was that this was the way the process worked best. So as long as I can prove that it runs more efficiently without having the application think, oh, now I have to try to close something that’s not open, I can happily delete all the extra lines of code.

Once I’ve cleaned everything up, I’ll try to work on losing some macros, but it’s a slow process.

Thanks, Mark and Drew!

Reply | Quote

Actually, it’s an easy, if somewhat crude, process to lose the macros. Just right click on the macro in the database window and select Save As from the shortcut menu. For the “As”, select module. Access will convert the macro to a rudimentary routine in a module and will even add error handling and comments if you so choose. It will save it as something like “Converted Macro- Macro2” and you can then start working with the code equivalent. Unfortunately, the equivalent may be Access 95 code, but at least it is a step up from macros.

Reply | Quote

It’s not the macro conversion (I really love doing that, and thanks, Charlotte, for the reminder)that’s the issue; it’s the other half of the process – getting everyone to agree, getting all our other software to work with the modules, documenting everything, etc.

Reply | Quote