Hi Bill
Thanks for the clear explanation. This time I'll opt for the DoCmd route as I want the confirmtions of how many records have been updated / changed etc.
Best regards
Ray
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> 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]
> >
>
Tidak ada komentar:
Posting Komentar