Rabu, 15 Agustus 2012

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

 

Hi Bill & John

Thanks for all the advice. I solved my issue with the RunSQL command and learned a lot about how to concatinate strings correctly in the process. Things like " ' + & ( work wonderfully when you get them in the right place.

I'm going to experiment with the db.Execute strSQL, dbFailOnError idea - learniing all the time.

Thanks
Ray

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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