Different ways of writing code (2003 sp2)

Topic

New Reply

I am always amazed at how many ways there are of writing code that produces the same end. Help me to understand the difference in the following lines:

Set db = DBEngine(0)(0)
db.Execute “QueryName”, dbFailOnError
Set db = Nothing

and

CurrentDb.Execute “QueryName”, dbFailOnError

Other than the apparent briefness of the one line over three is one better than the other?
What is Set db = Nothing?

Reply | Quote

Replies

I would replace the line

Set db = DBEngine(0)(0)

with

Set db = CurrentDb

This amounts to the same, but the second line is shorter and easier to understand. It also makes it clear that the two fragments of code basically do the same.

If this is the only time you use CurrentDb in a procedure, it doesn’t matter which of the two you use. But if you have several lines that use CurrentDb, the first format is to be preferred. For example

Set db = CurrentDb
db.Execute “ThisQuery”, dbFailOnError
db.Execute “ThatQuery”, dbFailOnError
db.Execute “OtherQuery”, dbFailOnError
Set db = Nothing

is more efficient than

CurrentDb.Execute “ThisQuery”, dbFailOnError
CurrentDb.Execute “ThatQuery”, dbFailOnError
CurrentDb.Execute “OtherQuery”, dbFailOnError

The first code fragment creates an object db and sets it to CurrentDB once, then uses it three times and discards the object again. The second code fragment implicitly creates an object and sets it to CurrentDb, uses it and discards it three times.

The variable db is an object variable – you can recognize object variables by their being assigned a value using the keyword Set. Object variables take up a relatively large amount of memory, and therefore it is a good idea to discard an object variable when you no longer need it. VBA should automatically do so at the end of the procedure, but that doesn’t always happen, so it is good practice to discard object variables explicitly in the code. This is done by setting the variable to Nothing. This releases the memory used by the object.

Reply | Quote

Thank you Hans, that explains the code very well.

Variables make sense to me (well, most of the time) except when it comes to the db object. If a database is open it has to be loaded into memory, why is a second instance used in the case above where we are executing queries? Am I right in assuming that DoCmd uses a single instance of a database when it executes a query?

Reply | Quote

CurrentDb isn’t a copy of the physical database (the .mdb file), but a representation in memory of the structure of the database, with the items contained in the database and their properties. Each time you use CurrentDb, VBA has to reconstruct that representation. When you use CurrentDb (or DoCmd) to execute a query, it uses the information in the object to perform an action on the physical database.

Reply | Quote

Alright, I’m getting it! but I couldn’t do it without you.

Reply | Quote