Problem referring to existing tabledef object

[nsenor]

You do not have to ever declare any

Dim db as DAO,Database
and
set db = ThisDb

If you use the ThisDb construct
You just use ThisDB as if CurrentDb was a well formed Object
e.g.

Set tbl = ThisDb.TableDefs("SomeTable")
Debug.Print tbl.Fields("SomeName"),Value

or

With ThisDb
...
someVar = .SomeProperty
....
End with

and you won't run into any problems. You can do that as many times and in as many places in the same project as you need to. Every time you make a declaration and an assignment, it is a redundant repetition and it tells on the speed

If the construct where to be in a class, unless it is PublicNotCreata ble (which is more or less a glorified std module), it has to be instantiated to be used. That (I think) would be too much hassle for just CurrentDb.

[NeoPa]

Hi people.

That's very interesting. Like IslaDogs I had no idea a property could be set within a Standard Module. This is very interesting in itself.

My own handling of the value of CurrentDb() works very slightly differently, in as much as I have places in my code which are entry points and thus I check and set a whole bunch of variables that the rest of my code expects to be present & correct. From that point forward (Logically rather than positionally.) I use the object variable and know that it's set appropriately. Thus there is no requirement to check the validity of the value each time it's referenced.

Nevertheless, I like the flow of this approach. Whether it's used exclusively or just at those points where it's necessary to check / set upon entry hardly matters.

@IslaDogs.
Your timings are interesting, and worth considering in some circumstances, but my principal interest is in logical and manageable code for portability between developers etc so I have to say I like this approach. Time delays of a millisecond, or even measured in such, are not an issue for me generally. Not unless, as in your tests, they're used so many times that the delay becomes significant.

[NeoPa]

@NSenor.
We cross-posted there.

I agree you can use it that way - but you aren't constrained to do so if you prefer, as I do, the approach where it's reliably set in an object variable.

NB. Please remember to use the [CODE] tags as you have in previous posts. I have a feeling you may have more suggestions in you so it would be worth saving me the effort of fixing them all afterwards ;-)

[nsenor]

Thanks for the editing NeoPa

My reply box is in plain text now for a reason i cannot fathom. I will revert to adding {CODE} tags manually.

Of course, there is no compulsion in the use of the method

The beauty of this Construct is more evident if you were to use a resource like Excel WorksheetFuncti on in Access frequently as I do. It has a problem that you cannot declare it once as a New object. Every time you need to use it, you have to assign it and invoke Excel Application.

Declaration:

Public fn_Sht as Excel.WorksheetFunction

Use:

Set fn_Sht = Excel.Application.WorksheetFunction

This calls Excel every time you invoke it. You may reduce resource consumption a little by declaring an Excel Application Object but the master stopper is to declare the WorksheetFuncti on first as a module level variable and then as a Public Property that exposes the private variable (after checking for Nothing(ness).

Declaration:

Private m_fn_Sht as WorksheetFunction

Use:

Public Property Get fn_Sht() As Excel.WorksheetFunction
    If m_fn_Sht Is Nothing Then
        Set m_fn_Sht = xlApp.WorksheetFunction
    End If
    Set fn_Sht = m_fn_Sht
End Property

The drawdown on resources is very noticeable at 1st use after Reset or Startup. After that, you hardly notice you are working in Excel from Access.

This is what informed my use of it for CurrentDb. Although the stress on resource is not an issue, but the ease of Access and shortness of code is appealing.

[isladogs]

@nsenor
Thank you for offering this as an alternate approach but I'm not convinced it has any real advantages so far.
I can see you are very keen on this approach but I've still not seen any benefit compared to the standard approach that I use.
Execution times are similar in each case. Your approach doesn't mean less code.

@NeoPa
I was interested in timing in case this approach was significantly faster or slower.
My point was that the differences were so small as to be irrelevant.

[NeoPa]

@IslaDogs.
Absolutely. Please don't see my comments as dismissive of your offering. Just how I interpreted the results. It looks very much like we agree on that :-)

@Nsenor.
It's an approach I'll consider using in future certainly, and it's nice to know about it.

[nsenor]

@IslaDogs
Maybe my English is not so good to explain what I mean. It's like you know how sweet honey tastes but you have difficulty explaining to someone who is used to sugar. They are both sweet.

Let us assume in a project, you need to make reference to CurrentDb in say 20 procedures in a VBA Project.

EASE OF USE:
Conventionally you will have to write at least 21 lines of code

Declare a Public variable

Public ThisDb as DAO.Database

For the 20 times you need to use CurrentDb you have to write the assignment Statement

Definition:

Set ThisDb = CurrentDb

If you are an Object destructor enthusiast, you will need an additional 20 lines of code for the 20 objects created

Destruction:

Set ThisDb = Nothing

OR you may wish to create only 1 reference to CurrentDb and use it for the entire session. In this case, you must check to make sure CurrentDb is not Nothing every time you need to use CurrentDb

If ThisDb is Nothing Then[INDENT]Set ThisDb = CurrentDb[/INDENT]
End If

So at the minimum you need 21 lines of code for reference to CurrentDb in 20 procedures. In an ideal situation, you may need up to 91 lines.

If you use a Public Property (which is actually an embellished variable definition) to call a module level variable, you will only need 7 lines of code even for 1000 references to CurrentDb in a VBA Project.

Declaration:

Private m_ThisDb as DAO.Database

Public Property Get ThisDb() As DAO.Database[INDENT]If m_dbThis Is Nothing Then[INDENT]Set m_dbThis = CurrentDb[/INDENT]
End If
Set ThisDb = m_dbThis[/INDENT]
End Property

Just 7 lines of code and you are done with CurrentDb problems.
No more declarations, no more definition.
ThisDb has become a self-defining, self-checking variable.
It will self-destruct because it is actually a reference to a private variable.

PERFORMANCE:
If you do the conventional method of declaring and defining

Dim db as DAO.Database
Set db=ThisDb

every time you need a reference to CurrentDb, you will not get the performance (or even the ease of use) advantage.

That one declaration is enough
That one definition is enough

You just use ThisDb as a variable. The efficiency is superb.

[isladogs]

@nsenor
I appreciate your attempts to try & explain why you believe this is so efficient.
However, did you read my initial reply in post #15?

If so, you will see that your code is no faster than using Set db=CurrentDb once for the entire session.
In my opinion, there is absolutely no reason to check that value each time it is used.
I set the value at the start of the session and don't destroy it as there is no benefit in doing so.
That means I just use db.Execute each time it is needed.

I'm not denigrating your code. It works perfectly and is about as fast as using CurrentDb

However - sorry but I don't see your code as any more efficient than the approach I use.
After repeated tests, it is no faster (the differences are negligible).
It also does not use less code.

My approach needs just 2 lines :

Public db As DAO.Database
Set db = CurrentDb

Some years ago, I got into a similar discussion with an MVP who had claimed back in 2009 that using DBEngine(0)(0) was up to 5000x faster than using CurrentDb. See https://www.experts-exchange.com/art...ternative.html.
He continued to repeat the same comments on numerous occasions over subsequent years despite evidence to the contrary.
He was similarly adamant that his recommended approach was the best.
In his case, it was easy to prove him wrong. In fact using DbEngine(0)(0) is usually slightly slower than CurrentDb

[nsenor]

We learn every day

@Isladog
Please where do you define

Set db =CurrentDb

so that all other procedures in all other modules know that it has been set?

[isladogs]

I normally set it in the load event of the startup form.

Private Sub Form_Load()
    Set db = CurrentDb
End Sub

That value is retained throughout the session.
There is no point clearing it by using Set db=Nothing or the advantages of assigning it in the first place are lost.

That's ALL you need to do ..
So what could possibly go wrong ..and how would any issues be solved?

1. On the rare occasions that I need to start the database using the shift bypass, I can set db by just typing that code line in the immediate window

2. Alternatively, (e.g. if no startup form is used), a process similar to yours could be employed adding a sub(or function) in a standard module:

Sub SetCurrDb()
    Set db = CurrentDb
End Sub

Then just type, SetCurrDb in the immediate window or assign a keyboard shortcut using an Autokeys macro
If this is a function, then it could of course be run from an autoexec macro instead.

3. If the value of db is lost after a program crash, error 91 occurs. If you really wanted to, that error could be used to reset the value.
However, during development I would prefer to know that there is an issue - so I don't do this.

For the same reason, I wouldn't use a tempvar for this purpose as tempvars usually retain their value after a crash

4. If you need to use an external database for any procedure, just set its value as a different variable when needed

Hope that covers everything

[nsenor]

Yes. Thanks @Isladogs.

I guess that covers everything for development that is for private use and crashes can be managed from the backend.

[isladogs]

No reason to limit the approach to private use. I have several commercial apps that have used this approach for almost 20 years without problems.
Due to thorough testing during development, program crashes are almost non-existent anyway.

Sorry but I didn't understand your reference to handling crashes from the BE.

[isladogs]

@nsenor
I revisited this topic (over 18 months later) and have just updated my web article comparing the speed of CurrentDB vs DBEngine(0)(0) and now including the use of ThisDb. See https://www.isladogs.co.uk/speed-com...s-2/index.html

After additional tests, I now agree with you that using ThisDb is indeed faster than either DBEngine(0)(0) or CurrentDb,
Typical times are 14 milliseconds to 68 milliseconds to 320 milliseconds respectively to set a variable referencing the current database..
I apologise for disputing your results back in 2021.

However, as each of the methods is very fast, they all form an insignificant part of the time needed to run a typical query
For that reason, it makes little difference overall which methos is used....as long as the variable isn't set repeatedly in a loop