What is the difference between these statements, and which do most prefer?

Question

I have noticed that a lot of code I have found on the internet will have variables defined and set that I would simply avoid...such as:

Dim db as Database Dim rs as Recordset Set db = CurrentDB Set rs = db.OpenRecordset

I would write this as:

Dim rs as Recordset Set rs = CurrentDB.OpenRecordset

I save 2 lines of code at the top plus one to Set db = Nothing at the end. Which, over hundreds of subs and functions can really add up...

But is one more preferred over another by most coders? Is there an actual reason to use one or the other? Is there an advantage to actually defining and spelling out the whole thing?? Is there any real savings in doing it my way, other than staving off carpel tunnel for a few more minutes?

Answer 1

In terms of execution, there is no real difference between the two VBA methods. However, the first is a more generic version as the db object can be set to either the local DAO database or an external DAO database using the Workspace.OpenDatabase method. The second is a more shortcut version as usually needed recordsets, querydefs, etc. derive from the working, local database. Below are examples of referencing external databases.

Short cut version:

Set db = OpenDatabase ("C:\Path\ToExternalDB\someotherdb.accdb")

Full generic version:

Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Path\ToExternalDB\someotherdb.accdb")

Therefore, should a developer change database environments (i.e., transition from local to external database source) and even workspaces, they can easily change the set db = ... line instead of adding this line to the code if developer had originally used only the CurrentDb namespace.

This is a good example of the trade-off decision between efficiency (less scripted lines) and scalability (translatable lines).

Answer 2

"The CurrentDb method creates another instance of the current database... The CurrentDb method enables you to create more than one variable of type Database that refers to the current database" from https://msdn.microsoft.com/en-us/library/office/aa221178%28v=office.11%29.aspx?f=255&MSPPError=-2147217396.
So if you use CurrentDB just once in your code than its fine not to declare it, however using it multiple times it won't be the same instance but always created a new one which may create strange bugs for you.