Is this an ADO or DAO call?

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi All,
Just a point of curiosity...

All through my code, when I want to run a query, I use code like:

Dim ADOCon as ADODB.Conection
....
....

Set ADOCon = CurrentDB.Connection
....
ADOCon.Execute strSQL


I've recently seen someone not declare an object at all, and, in
code, just use:

CurrentDB.Execute strSQL

If I use this code shortcut, am I making a DAO call? I just wonder
what the diffenece is? Is there any other advantage or disadvantage to one
way over the other?

- Max
 
Well, CurrentProject.Connection is a ADO reference to the
current database, and CurrentDB is the DAO reference.

As far as speed goes, I think (not sure) that DAO is a bit
faster.

You don't need to reference DAO to use the CurrentDB. You
could:

Dim db as Object
Set db = CurrentDB
db.Execute strSQL

Chris Nebinger
 
Max Moor said:
Hi All,
Just a point of curiosity...

All through my code, when I want to run a query, I use code like:

Dim ADOCon as ADODB.Conection
...

Set ADOCon = CurrentDB.Connection
Click to expand...

Are you sure you're using that, and no

Set ADOCon = CurrentProject.Connection

? I don't think the DAO Connection object returned by
CurrentDb.Connection is compatible with the ADO Connection object.
...
ADOCon.Execute strSQL


I've recently seen someone not declare an object at all, and, in
code, just use:

CurrentDB.Execute strSQL

If I use this code shortcut, am I making a DAO call? I just
wonder what the diffenece is? Is there any other advantage or
disadvantage to one way over the other?
Click to expand...

Yes, CurrentDb.Execute is calling the Execute method of a DAO Database
object. In a one-line statement like that, the Database object is
created, its Execute method is called, and the object is destroyed all
on one line.
 
Are you sure you're using that, and no

Set ADOCon = CurrentProject.Connection
Click to expand...

My mistake. You're right. It's the above.

Yes, CurrentDb.Execute is calling the Execute method of a DAO Database
object. In a one-line statement like that, the Database object is
created, its Execute method is called, and the object is destroyed all
on one line.
Click to expand...

I got a bit messed up. What I meant to ask is if the equivalent ADO call:

CurrentProject.Connection.Execute strSQL

is too weird? I wonder why the examples I've seen have an ADODB.Connection
object being declared at all, when this is available to do. Is there
something wrong with this?
 
Well, CurrentProject.Connection is a ADO reference to the
current database, and CurrentDB is the DAO reference.

As far as speed goes, I think (not sure) that DAO is a bit
faster.

You don't need to reference DAO to use the CurrentDB. You
could:

Dim db as Object
Set db = CurrentDB
db.Execute strSQL
Click to expand...

Thanks Chris. I sort of got myself mixed up and asked the question all
wrong. I meant to wonder if the ADO equivalent:

CurrentProject.Connection.Execute strSQL

was an okay thing to do. It works fine. I just wonder why the examples of
executing SQLs in code declare an object rather than just coding the
shortcut. I wonder if there is something wrong with it?
 
Max Moor said:
My mistake. You're right. It's the above.



I got a bit messed up. What I meant to ask is if the equivalent ADO
call:

CurrentProject.Connection.Execute strSQL

is too weird? I wonder why the examples I've seen have an
ADODB.Connection object being declared at all, when this is available
to do. Is there something wrong with this?
Click to expand...

As far as I know, there's nothing particularly wrong with
CurrentProject.Connection.Execute, any more than there is with
CurrentDb.Execute. It may even be more efficient, since I think
CurrentProject.Connection just returns a reference to the existing
Connection object, while CurrentDb actually creates a new Database
object.

It's generally considered good practice to declare a separate object
variable in these cases, though, because then you can ensure that it is
properly destroyed when you're done with it. Theoretically, an inline
reference to an object shouldn't result in any hanging references, but
there have been known to be VBA bugs that left implicit object
references hanging around when they should have been destroyed.

And certainly if you're going to use the base object more than once in a
procedure, it will be more efficient to get a reference to it once and
use that repeatedly, rather than getting a new reference each time you
want to work with it.
 
Back
Top