Rabu, 15 Agustus 2012

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

 

You're welcome, Ray.

-Bill

--- In MS_Access_Professionals@yahoogroups.com, "Ray" <rayfrew@...> wrote:
>
> 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