Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Different ways of writing code (2003 sp2)

    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?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Different ways of writing code (2003 sp2)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Different ways of writing code (2003 sp2)

    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?
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Different ways of writing code (2003 sp2)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Different ways of writing code (2003 sp2)

    Alright, I'm getting it! but I couldn't do it without you.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •