CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference? 
Author Message
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?

 Hi, everybody!
 I need to run action query from VBA (this query operate with only
Access base tables and attached Access tables). As I see there are
basically two ways to do this from VBA module -
 CurrentDb.Execute "QueryName"
and
 DoCmd.OpenQuery "QueryName"

 Can anyone explain me the difference between these two methods? Which
is preferable and why? After CurrentDb.Execute I have
CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

 And what is better
 DoCmd.RunSQL "DELETE * FROM TempTable"
or
 CurrentDb.Execute "DELETE * FROM TempTable" ?

 Any opinion appreciated.

           Best regards, Serg.

*** Remove asterisk for mail reply!!!



Sat, 26 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
Hi Serg,

    The Execute method works with SQL strings as you have it in your
examples.  Also, it bypasses the default warnings for action queries.  For
the RunSQL method, you would need to turn off SetWarnings and turn it back
on after the query has finished executing.

As for the difference between OpenQuery and Execute methods, sorry, I
haven't done any benchmark tests so can't say which one is better on saved
queries.

HTH
--
Dev Ashish    (Just my $.001)
The Access Web ( http://home.att.net/~dashish )
----

: Hi, everybody!
: I need to run action query from VBA (this query operate with only
:Access base tables and attached Access tables). As I see there are
:basically two ways to do this from VBA module -
: CurrentDb.Execute "QueryName"
:and
: DoCmd.OpenQuery "QueryName"
:
: Can anyone explain me the difference between these two methods? Which
:is preferable and why? After CurrentDb.Execute I have
:CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?
:
: And what is better
: DoCmd.RunSQL "DELETE * FROM TempTable"
:or
: CurrentDb.Execute "DELETE * FROM TempTable" ?
:
: Any opinion appreciated.
:
:           Best regards, Serg.

:*** Remove asterisk for mail reply!!!



Sat, 26 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?

says...
Quote:
>  I need to run action query from VBA (this query operate with only
> Access base tables and attached Access tables). As I see there are
> basically two ways to do this from VBA module -
>  CurrentDb.Execute "QueryName"
> and
>  DoCmd.OpenQuery "QueryName"

The answer's pretty simple: avoid DoCmd whenever possible. It's really
there ONLY to allow automation and parity with macros. It will never give
you any advantage, and is, if I remember from some tests I ran, generally
slower. DoCmd.OpenQuery exists to display a query datasheet -- it happens
to run an action query, if you pass it an action query.

I remember that DoCmd.RunSQL was significantly slower than
CurrentDb.Execute, and you get more options if you use Execute.

Basically, my rule is to avoid DoCmd whenever possible. I explain to
classes I teach that DoCmd exists to allow VBA to work with the Access
UI. Since you have no reason to interact with the UI when running an
action query, just don't. -- Ken



Sun, 27 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
One more advantage to using .Execute:

Only then do you have the dbFailOnError option. This allows you to run
the query only if it can run to completion without error, rather than
just skipping records that cause errors. And it allows you to wrap
several action queries in a transaction that will be committed only if
they all succeed, or rolled back if any one query fails. That's often
a very big advantage.

 -- Andy

Quote:

> Hi, everybody!
> I need to run action query from VBA (this query operate with only
>Access base tables and attached Access tables). As I see there are
>basically two ways to do this from VBA module -
> CurrentDb.Execute "QueryName"
>and
> DoCmd.OpenQuery "QueryName"

> Can anyone explain me the difference between these two methods? Which
>is preferable and why? After CurrentDb.Execute I have
>CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

> And what is better
> DoCmd.RunSQL "DELETE * FROM TempTable"
>or
> CurrentDb.Execute "DELETE * FROM TempTable" ?

> Any opinion appreciated.

>           Best regards, Serg.

>*** Remove asterisk for mail reply!!!



Mon, 28 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
Andy,

Do you know of any way to trap errors or somehow modify the
dbFailOnError?  My issue is with append queries where there could be
duplicates.  An append query just won't append the dup's.  Great!  Just
what I want.  But... dbFailOnError causes the whole query to fail if
dup's exist.  I've figured there is probably no way to avoid the
failure, except for taking off "fail on error" but thought I'd ask
anyway.

Keri

Quote:

> One more advantage to using .Execute:

> Only then do you have the dbFailOnError option. This allows you to run
> the query only if it can run to completion without error, rather than
> just skipping records that cause errors. And it allows you to wrap
> several action queries in a transaction that will be committed only if
> they all succeed, or rolled back if any one query fails. That's often
> a very big advantage.

>  -- Andy


> > Hi, everybody!
> > I need to run action query from VBA (this query operate with only
> >Access base tables and attached Access tables). As I see there are
> >basically two ways to do this from VBA module -
> > CurrentDb.Execute "QueryName"
> >and
> > DoCmd.OpenQuery "QueryName"

> > Can anyone explain me the difference between these two methods? Which
> >is preferable and why? After CurrentDb.Execute I have
> >CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

> > And what is better
> > DoCmd.RunSQL "DELETE * FROM TempTable"
> >or
> > CurrentDb.Execute "DELETE * FROM TempTable" ?

> > Any opinion appreciated.

> >           Best regards, Serg.

> >*** Remove asterisk for mail reply!!!



Mon, 28 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
Hi Keri,
Just trap the error and re-run the Execute if it was due to Dupes.  For
example:-

      Dim lodb As Database
      Dim strSQl As String
      On Error GoTo Wiggle_err
      Set lodb = CurrentDb
      strSQl = "INSERT INTO tblNames (ID,FName, LName) " _
                   & "SELECT ID, FName, LName FROM TestInsert;"
      lodb.Execute strSQl, dbFailOnError
Wiggle_end:
Exit Function
Wiggle_err:
      Select Case Err
          Case 3022 'Duplicate values in the key
              lodb.Execute strSQl
              Resume Next
          Case Else
              Msgbox Err.Description,,Err
              Resume Wiggle_end
      End Select


Quote:
>Andy,

>Do you know of any way to trap errors or somehow modify the
>dbFailOnError?  My issue is with append queries where there could be
>duplicates.  An append query just won't append the dup's.  Great!  Just
>what I want.  But... dbFailOnError causes the whole query to fail if
>dup's exist.  I've figured there is probably no way to avoid the
>failure, except for taking off "fail on error" but thought I'd ask
>anyway.

>Keri


>> One more advantage to using .Execute:

>> Only then do you have the dbFailOnError option. This allows you to run
>> the query only if it can run to completion without error, rather than
>> just skipping records that cause errors. And it allows you to wrap
>> several action queries in a transaction that will be committed only if
>> they all succeed, or rolled back if any one query fails. That's often
>> a very big advantage.

>>  -- Andy


>> > Hi, everybody!
>> > I need to run action query from VBA (this query operate with only
>> >Access base tables and attached Access tables). As I see there are
>> >basically two ways to do this from VBA module -
>> > CurrentDb.Execute "QueryName"
>> >and
>> > DoCmd.OpenQuery "QueryName"

>> > Can anyone explain me the difference between these two methods? Which
>> >is preferable and why? After CurrentDb.Execute I have
>> >CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

>> > And what is better
>> > DoCmd.RunSQL "DELETE * FROM TempTable"
>> >or
>> > CurrentDb.Execute "DELETE * FROM TempTable" ?

>> > Any opinion appreciated.

>> >           Best regards, Serg.

>> >*** Remove asterisk for mail reply!!!



Tue, 29 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
You are right, Keri. dbFailOnError is all or nothing. If you want to
evaluate the error and possibly allow the process to continue, you
could use a recordset loop with standard VBA error handling. This
recordset handling can of course still be wrapped in a transaction.
Performance suffers, but you have much more control. I have done this
in creating custom import routines that ignore certain kinds of errors
without aborting the whole process.

 -- Andy

Quote:

>Andy,

>Do you know of any way to trap errors or somehow modify the
>dbFailOnError?  My issue is with append queries where there could be
>duplicates.  An append query just won't append the dup's.  Great!  Just
>what I want.  But... dbFailOnError causes the whole query to fail if
>dup's exist.  I've figured there is probably no way to avoid the
>failure, except for taking off "fail on error" but thought I'd ask
>anyway.

>Keri


>> One more advantage to using .Execute:

>> Only then do you have the dbFailOnError option. This allows you to run
>> the query only if it can run to completion without error, rather than
>> just skipping records that cause errors. And it allows you to wrap
>> several action queries in a transaction that will be committed only if
>> they all succeed, or rolled back if any one query fails. That's often
>> a very big advantage.

>>  -- Andy


>> > Hi, everybody!
>> > I need to run action query from VBA (this query operate with only
>> >Access base tables and attached Access tables). As I see there are
>> >basically two ways to do this from VBA module -
>> > CurrentDb.Execute "QueryName"
>> >and
>> > DoCmd.OpenQuery "QueryName"

>> > Can anyone explain me the difference between these two methods? Which
>> >is preferable and why? After CurrentDb.Execute I have
>> >CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

>> > And what is better
>> > DoCmd.RunSQL "DELETE * FROM TempTable"
>> >or
>> > CurrentDb.Execute "DELETE * FROM TempTable" ?

>> > Any opinion appreciated.

>> >           Best regards, Serg.

>> >*** Remove asterisk for mail reply!!!



Tue, 29 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
Thanks, Terry, I'll give it a try!

Keri

Quote:

> Hi Keri,
> Just trap the error and re-run the Execute if it was due to Dupes.  For
> example:-

>       Dim lodb As Database
>       Dim strSQl As String
>       On Error GoTo Wiggle_err
>       Set lodb = CurrentDb
>       strSQl = "INSERT INTO tblNames (ID,FName, LName) " _
>                    & "SELECT ID, FName, LName FROM TestInsert;"
>       lodb.Execute strSQl, dbFailOnError
> Wiggle_end:
> Exit Function
> Wiggle_err:
>       Select Case Err
>           Case 3022 'Duplicate values in the key
>               lodb.Execute strSQl
>               Resume Next
>           Case Else
>               Msgbox Err.Description,,Err
>               Resume Wiggle_end
>       End Select



> >Andy,

> >Do you know of any way to trap errors or somehow modify the
> >dbFailOnError?  My issue is with append queries where there could be
> >duplicates.  An append query just won't append the dup's.  Great!  Just
> >what I want.  But... dbFailOnError causes the whole query to fail if
> >dup's exist.  I've figured there is probably no way to avoid the
> >failure, except for taking off "fail on error" but thought I'd ask
> >anyway.

> >Keri


> >> One more advantage to using .Execute:

> >> Only then do you have the dbFailOnError option. This allows you to run
> >> the query only if it can run to completion without error, rather than
> >> just skipping records that cause errors. And it allows you to wrap
> >> several action queries in a transaction that will be committed only if
> >> they all succeed, or rolled back if any one query fails. That's often
> >> a very big advantage.

> >>  -- Andy


> >> > Hi, everybody!
> >> > I need to run action query from VBA (this query operate with only
> >> >Access base tables and attached Access tables). As I see there are
> >> >basically two ways to do this from VBA module -
> >> > CurrentDb.Execute "QueryName"
> >> >and
> >> > DoCmd.OpenQuery "QueryName"

> >> > Can anyone explain me the difference between these two methods? Which
> >> >is preferable and why? After CurrentDb.Execute I have
> >> >CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

> >> > And what is better
> >> > DoCmd.RunSQL "DELETE * FROM TempTable"
> >> >or
> >> > CurrentDb.Execute "DELETE * FROM TempTable" ?

> >> > Any opinion appreciated.

> >> >           Best regards, Serg.

> >> >*** Remove asterisk for mail reply!!!



Tue, 29 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?


Quote:
>You are right, Keri. dbFailOnError is all or nothing. If you want to
>evaluate the error and possibly allow the process to continue, you
>could use a recordset loop with standard VBA error handling.

Isn't there an alternative solution that doesn't depend on the
peculiarities of the error handling of whatever DB engine happens to
be on hand? I'd really hate for this kind of update to fail, because
Jet 3.509b rev2.5 SR-9a recognises this behaviour as a bug and
"corrects" it...

It's not hard to create the update to avoid duplicates in the first
place:

 INSERT INTO OldTable
 SELECT * FROM NewTable
   WHERE NewTable.ID NOT IN
   (SELECT Original.ID FROM OldTable Original)
 ;

and at least you know this is always going to work...

Just a thought,

Tim F

--



Wed, 30 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
 Hi, Everybody!

 Thank you all!

On Sat, 12 Dec 1998 13:52:30 GMT,In
microsoft.public.access.modulesdaovba,

Quote:

>It's not hard to create the update to avoid duplicates in the first
>place:

> INSERT INTO OldTable
> SELECT * FROM NewTable
>   WHERE NewTable.ID NOT IN
>   (SELECT Original.ID FROM OldTable Original)
> ;

 As for this SELECT, in Jet 3.0 (Acess'95) it may be not very good - I
think it carrys out internel SELECT for _every_ record of external
SELECT, even thay are independent! I had test

 SELECT * FROM TableOne WHERE ID In
 (SELECT ID FROM TableTwo WHERE TableTwo.Name Like 'Test*')

and

 SELECT TableOne.* FROM TableOne INNER JOIN TableTwo
 ON TableOne.ID=TableTwo.ID
 WHERE TableTwo.Name Like 'Test*'

 So if TableOne have more then 50,000 records (my test) first query is
_very_ slow, and second - Ok.

           Best regards, Serg.

*** Remove asterisk for mail reply!!!



Wed, 30 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?
This would allow any type of subsequent error to pass undetected (the
record would be skipped), which could be dangerous.
Quote:

>Thanks, Terry, I'll give it a try!

>Keri


>> Hi Keri,
>> Just trap the error and re-run the Execute if it was due to Dupes.  For
>> example:-

>>       Dim lodb As Database
>>       Dim strSQl As String
>>       On Error GoTo Wiggle_err
>>       Set lodb = CurrentDb
>>       strSQl = "INSERT INTO tblNames (ID,FName, LName) " _
>>                    & "SELECT ID, FName, LName FROM TestInsert;"
>>       lodb.Execute strSQl, dbFailOnError
>> Wiggle_end:
>> Exit Function
>> Wiggle_err:
>>       Select Case Err
>>           Case 3022 'Duplicate values in the key
>>               lodb.Execute strSQl
>>               Resume Next
>>           Case Else
>>               Msgbox Err.Description,,Err
>>               Resume Wiggle_end
>>       End Select



>> >Andy,

>> >Do you know of any way to trap errors or somehow modify the
>> >dbFailOnError?  My issue is with append queries where there could be
>> >duplicates.  An append query just won't append the dup's.  Great!  Just
>> >what I want.  But... dbFailOnError causes the whole query to fail if
>> >dup's exist.  I've figured there is probably no way to avoid the
>> >failure, except for taking off "fail on error" but thought I'd ask
>> >anyway.

>> >Keri


>> >> One more advantage to using .Execute:

>> >> Only then do you have the dbFailOnError option. This allows you to run
>> >> the query only if it can run to completion without error, rather than
>> >> just skipping records that cause errors. And it allows you to wrap
>> >> several action queries in a transaction that will be committed only if
>> >> they all succeed, or rolled back if any one query fails. That's often
>> >> a very big advantage.

>> >>  -- Andy


>> >> > Hi, everybody!
>> >> > I need to run action query from VBA (this query operate with only
>> >> >Access base tables and attached Access tables). As I see there are
>> >> >basically two ways to do this from VBA module -
>> >> > CurrentDb.Execute "QueryName"
>> >> >and
>> >> > DoCmd.OpenQuery "QueryName"

>> >> > Can anyone explain me the difference between these two methods? Which
>> >> >is preferable and why? After CurrentDb.Execute I have
>> >> >CurrentDb.RecordsAffected, do I have such value after DoCmd.OpenQuery?

>> >> > And what is better
>> >> > DoCmd.RunSQL "DELETE * FROM TempTable"
>> >> >or
>> >> > CurrentDb.Execute "DELETE * FROM TempTable" ?

>> >> > Any opinion appreciated.

>> >> >           Best regards, Serg.

>> >> >*** Remove asterisk for mail reply!!!



Thu, 31 May 2001 03:00:00 GMT  
 CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?

Quote:

>Do you know of any way to trap errors or somehow modify the
>dbFailOnError?  My issue is with append queries where there could be
>duplicates.  

So why not put dupe checking in the query and append only the ones
which aren't dupes?

OTOH that would be a real ugly query if you had a number of fields in
the table.

Tony
----
Message posted to newsgroup and emailed.
Tony Toews, Independent Computer Consultant
The Year 2000 crisis: Will my parents or your grand parents still be receiving
their pension in January, 2000?  See http://www.granite.ab.ca/year2000
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm



Sun, 10 Jun 2001 03:00:00 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. receive returned value from doCmd.openquery

2. OpenQuery Method of DOCMD...

3. HELP: Inconsistent Results when running multiple queries with DoCmd.OpenQuery

4. VB 4 16 vs 32 bit differences in Execute Method

5. Close vs Nothing + CurrentDB vs DBEngine

6. Query will not start with MyQuery.Execute, but works fine with DoCmd.OpenQuery.

7. RecordLocking with currentdb.execute

8. CurrentDb() taking away Exclusive Access to, umm, CurrentDb()

9. CurrentDb vs. DBEngine

10. qdf.Execute vs. db.Execute

11. ADO: cmd.Execute vs cn.Execute

12. command.execute vs. connection.execute

 

 
Powered by phpBB® Forum Software