SELECT * vs. SELECT SomeField... which is faster?

[MatthewGrace]

I'm oblivious to how Access handles the execution of a SQL statement. On a Table with say, 10 Fields, does "SELECT OneField" actually run faster than "SELECT *"? Or are they the exact same thing processing speed-wise, but simply show a smaller recordset to the viewers eye?



As a printed example, which of these two would execute faster, if either?
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT OneRandomField FROM tblOrders WHERE [ID]=XXX")
rst.Edit
rst![OneRandomField ] = valueX
rst.Update

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblOrders WHERE [ID]=XXX")
rst.Edit
rst![OneRandomField ] = valueX
rst.Update

[ranman256]

nether is faster than an update query,
update table set [field] = "result" WHERE [ID]="XXX"

[June7]

AFAIK, the more data is pulled, the more time is required. This might not be noticeable on a local table but if pulling across a network, might see difference.

If you want a single value, might even try DLookup() instead of opening recordset object.

You can maybe test speeds by using time stamps in the code and printing the times of start and end out to Intermediate Window.

[MatthewGrace]

Thanks. I still don't know how to implement Ranman's suggestion in VBA anyhow (see pic example). But I'll just have to make a Query to get it to work.

[June7]

ranman is suggesting an UPDATE sql action to write record(s) to a table. So your VBA code syntax is way off the mark. Need DoCmd.RunSQL or CurrentDb.Execute method.

I would use DLookup() (or DCount, DSum, etc) to pull a single value from a table.

But what are you really trying to accomplish? What do you need to do with this value?

[MatthewGrace]

I was initially trying to accomplish a better understanding of how to build the fastest most efficient VBA Recordsets. As an example - I have a project that makes many Recordsets with "Select *" for convenience, when in fact I really only need "Select OneField". I wanted to know which was faster, if either. For now, I'm settling with your (June) opinion as you described it. I consider the thread solved, thank you.

Moving on to a separate subject that I was not expecting to arise, Ranman volunteered a way that looked fast and lean to Update values. That stirred ideas - I went back to my project and realized I can probably omit some of the bulky Recordset operations with a line like his, but couldn't implement it successfully. Sure it made plenty of sense in Query Design, but I was looking for a way to do this in VBA. After browsing MSDN, I was confused because I see Microsoft literature explicitly saying it works in VBA, check it out:

Taken from: https://msdn.microsoft.com/en-us/lib.../ff845201.aspx

I have used your suggestion and this.....
Private Sub Command0_Click()
CurrentDb.Execute "Update Table1 Set [Field2] = 'Result' WHERE [TestFK]=55"
End Sub
... Works beautifully. Picture this: Normally I would Dim rs As Recordset, .OpenRecordset, then Do While Not .EOF - .moveNext - Loop through the whole darn thing and If-Block check for certain values, then assign, then cleanup. This other way is WAAAAAY nicer. Thank you both!