[AccessD] rsR("order") vs rsR!Order

You don't even need  large table to test the relevant part.  Just two tables with at least one 
record in one of the tables:

Function test()
Dim t1 As Single
Dim t2 As Single
Dim x As Long
Dim tests As Long
tests = 1000000
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("Table1")
Set rs2 = CurrentDb.OpenRecordset("Table2")
rs1.AddNew
t1 = Timer
For x = 1 To tests
rs1(1) = rs2(1)
Next
t2 = Timer
Debug.Print tests & " iterations on FieldNum: Finished in " & t2 - t1 & " seconds"
For x = 1 To tests
rs1("field1") = rs2("field1")
Next
t2 = Timer
Debug.Print tests & " iterations on Literal: Finished in " & t2 - t1 & " seconds"
For x = 1 To tests
rs1!field1 = rs2!field1
Next
t2 = Timer
Debug.Print tests & " iterations on Bang: Finished in " & t2 - t1 & " seconds"
End Function


Results on my laptop:
1000000 iterations on FieldNum: Finished in 1.691406 seconds
1000000 iterations on Literal: Finished in 4.535156 seconds
1000000 iterations on Bang: Finished in 7.25 seconds


On 1 Aug 2011 at 19:32, William Benson wrote:

> You should be able to test this yourself on a large nonindexed table.
> My guess is even on million rows your computer timer will not notice
> the difference. All the time will be eaten up by addnew and update.
> 
> I would instead test other things that might be done with the
> properties such as simply assigning the property to a string variable
> a few million times and printing the time the loop takes each method.
> On Aug 1, 2011 7:27 PM, "Darryl Collins"
> <darryl at whittleconsulting.com.au> wrote: > Thanks Jim, > > I was
> trying to find out which one would be faster but was struggling to >
> find the right question to ask Google to get meaningful results. '! vs
> "" > Access Query' wasn't working for me too well :) > > Given the
> tiny workload and that performance is not a constraint it is >
> probably neither here nor there in this case, but if the load gets
> heavy > and/or speed is critical, than that sort of thing is good to
> know for future > reference. > > Cheers > Darryl. > > -----Original
> Message----- > From: accessd-bounces at databaseadvisors.com >
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> > Sent: Monday, 1 August 2011 10:20 PM > To: 'Access Developers
> discussion and problem solving' > Subject: Re: [AccessD] rsR("order")
> vs rsR!Order > > > It's also a tad faster. All the bang/dot notation
> internally is converted > to that format before being executed. > >
> Jim. > > -----Original Message----- > From:
> accessd-bounces at databaseadvisors.com >
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
> Collins > Sent: Sunday, July 31, 2011 11:28 PM > To: 'Access
> Developers discussion and problem solving' > Subject: [AccessD]
> rsR("order") vs rsR!Order > > Hi guys & Gals, > > Slower day at work
> today so I was poking around some code they use here in > my new role
> and found this syntax when dealing with recordsets in Access VBA > >
> rsR.AddNew > rsR("order") = rsM("order") > rsR("sheetname") =
> rsM("sheetname") > rsR("sheetnumber") = rsM("sheetnumber") >
> rsR.Update > > It is very, ummm, MS Excel in style, but it does work
> ok and update the > recordset(s) correctly. > However I would have
> written it like: > > With rsR > .AddNew > !order = rsM!order >
> !sheetname = rsM!sheetname > !sheetnumber = rsM!sheetnumber > !Update
> > End with > > Not withstanding then with / end with bit. What is the
> advantage (if any) > of one syntax over the other? Is one method
> faster? > Actually, Why does the first syntax even work? I would have
> though you > would have had to use the ! method, but very clearly I am
> totally wrong on > that count. > > I had not seen code used like that
> before for MS Access recordsets. Maybe I > need to get out more? > >
> Your thoughts? > > Cheers > Darryl > > -- > AccessD mailing list >
> AccessD at databaseadvisors.com >
> http://databaseadvisors.com/mailman/listinfo/accessd > Website:
> http://www.databaseadvisors.com > > -- > AccessD mailing list >
> AccessD at databaseadvisors.com >
> http://databaseadvisors.com/mailman/listinfo/accessd > Website:
> http://www.databaseadvisors.com > > -- > AccessD mailing list >
> AccessD at databaseadvisors.com >
> http://databaseadvisors.com/mailman/listinfo/accessd > Website:
> http://www.databaseadvisors.com -- AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
>