Ray
You can still track the number of records by using RecordsAffected property of db object. In this case, you DO have to set db.
Dim db As DAO.Database
Dim strSQL As String
Dim lngRecsAff As Long
Set db = CurrentDb
strSQL = "UPDATE Employees " _
& "SET TitleOfCourtesy = 'Ms.' " _
& "WHERE TitleOfCourtesy = 'Miss'"
db.Execute strSQL
lngRecsAff = db.RecordsAffected
Debug.Print lngRecsAff
Set db = Nothing
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Ray" <rayfrew@...> wrote:
>
> 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