Selasa, 14 Agustus 2012

RE: [MS_AccessPros] Re: CurrentDB.Execute versus DoCmd.RunSQL

 

And it's harder to trap errors using RunSQL. That's what the dbFailOnError
parameter is for - to pass any error to your error handler.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Tuesday, August 14, 2012 4:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: CurrentDB.Execute versus DoCmd.RunSQL

Ray

You don't don't need:
Dim db As Database, strSQL As String
Set db = CurrentDb
if you are using CurrentDb.Execute

This is all you need:
CurrentDb.Execute "SELECT tblContacts.* INTO tblContacts_bck " _
& "FROM tblContacts;", dbFailOnError

You could have also written it this way:
Dim db As Database, strSQL As String
Set db = CurrentDb
strSQL = "SELECT tblContacts.* INTO tblContacts_bck " _
& "FROM tblContacts;"
db.Execute strSQL, dbFailOnError
Set db = Nothing

but that's a lot of extra typing for no reason.

The difference between the two is that DoCmd.RunSQL will present the user with a
warning about the number of records that are about to be inserted. It also
confirms the insertion. Sometimes you want these but usually not.

The CurrentDB.Execute does not give any warnings.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, "Ray" <rayfrew@...> wrote:
>
>
> This might be a simple question but could someone please give some
> guidance on when to use CurrentDb.Execute strSQL and when to use
> DoCmd.RunSQL strSQL as they seem similar.
>
> I have a simple query "SELECT tblContacts.* INTO tblContacts_bck FROM
> tblContacts;"
>
> For the "Execute" option I seem to need:
>
> Dim db As Database, strSQL As String
>
> Set db = CurrentDb
>
> strSQL = "SELECT tblContacts.* INTO tblContacts_bck FROM tblContacts;"
>
> CurrentDb.Execute strSQL
>
>
>
> whereas for the RunSQL option I only seem to need:
>
> strSQL = "SELECT tblContacts.* INTO tblContacts_bck FROM tblContacts;"
>
> DoCmd.RunSQL strSQL
>
> Many thanks
>
> Ray
>
>
>
> [Non-text portions of this message have been removed]
>

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar