For me, it depends on the situation. If I just have one record to do, I probably would use SQL. On the other hand if I need to iterate through the records, I would use the recordset.
Sometimes you can overthink some thing and over engineer it, where a simple solution would work better.

I started programming in BASIC on my TRS-80 so my brain tends to think in loops. I've always preferred record sets myself. I was well into my 30s before I really learned how to use SQL. But if it's something simple that doesn't require different processes for each record then I'll try to squeeze it into an SQL statement if I can because SQL tends to be faster than iterative record set Loops. Depends on the situation.

QQ

Thanks, Rick. Sound advice for sure and also in your words I find a possible explanation for my recordset reflex; for I too began with a TRS-80 many years ago. Mine had a ribbon cable connecting the keyboard to the box that housed the (now historically ancient) processor. This was a constant thorn for me because the slightest movement in the room would interrupt the data stream at the loose-fitting ribbon and all was lost. Hundreds of lines of BASICA text. My original version predated DOS and relied on a small tape recorder for data storage. Horrible!

Haha. Yep. I had the tape recorder too! The first computer I used was the vintage black and gray TRS-80. It was in the "computer room" in my school. One computer in the whole room for like 5 of us to use (the only kids nerdy enough to care about it). Then I begged and begged to get one for Xmas, so the first computer that was actually mine was a Color Computer 2. It's still sitting in a box in my storage unit. I've got the ROM-pak expansion port, speech synthesizer (ala Stephen Hawking), DUAL floppy drive (you're a baller with that) although I did start off on a cassette drive like you mentioned. PAINFUL. Oh, and I had a 300 baud modem! Whooop!

One of these days I'm gonna set that bad boy up and make a video out of it. I actually wrote my first REAL program on that thing... it was a D&D dice roller followed by a D&D character generator. Can you tell what 12-year-old me did with my time? Computer programming, role-playing, and listening to Rush. :)

Wow! The priest friend who got me interested in computer hobby (and who gave me the TRS-80) eventually upgraded his system to a monstrous, 12" floppy disc drive unit. When I think of it! The disks were huge and prone to data transfer errors. Every save was a white-knuckled adventure. These are museum pieces today, aren't they? My current machine is a nice, Insperon desktop. More reliable, but no less frustrating at times.

12"? Damn. My CoCo had the 5.25" disks (remember buying the punch tool so you could double-side them?) LOL

I thought it was SOOOOO cool and futuristic when the 3.5" hard plastic "floppy" disks came out because they looked like the cartridges they'd load into the computer on Star Trek (TOS).

Regarding recordset or SQL, you use whatever that is best for the job.  Also, one can do things the others can't.  If I need to use properties like Recordcount, Bookmark, AbsolutePosition, etc., if I need to update one recordset with another (e.g. r1!field1 = r2!field2), if I need additional logic that a plain SQL can't provide, I use VBA and Recordset.

Conversely, you need to avoid using what is clearly not best for the job.  If you need to update 1000+ rows, you obviously don't loop through 1000+ rows and update them one by one; you run just one query whenever you can.  In most cases, using only SQL is faster, because if you use VBA and Recordset, you STILL have to use SQL (Set r = CurrentDb.OpenRecordset("SELECT ... ")), in addition to VBA.

If you connect to a remote database where Internet speed is a limiting factor, using Recordset is definitely slower than running a SQL in a passthrough query.  (But in ASP, you can use server-side Recordset that reduces the effect of Internet speed.)

When you delete a whole table, you obviously run a single SQL (DELETE FROM Table1) instead of opening a Recordset and deleting rows one by one.

Well said. :)

So which is faster? In Developer 18, I believe, it was stated recordsets were faster than SQL by a wide margin.  Just trying to learn.

I don't have access to the paid courses so I don't know what D18 says exactly.  I'm sure there are occasions where VBA and recordsets are (much) preferred.  When I need "additional logic", when I need to store an entire set of data into a variable, that's when I use recordsets for sure.  I can pass an entire recordset to another procedure so that additional operations can be done without opening up the recordset again.  A plain SQL can't do that.  When there are "bulk updates," that's when I turn to SQL.

Lots of useful info from many familiar names. Thanks all. It remains clear to me that the recordset approach provides a very unambiguous view of the task as it executes. Loops within loops are clear and somewhat easier to develop with .ADDNEW I think. Since speed is rarely a issue for my modest, desktop DBs, I think I'll keep my fondness for rSt. Thanks again.

My rule of thumb: if I'm doing the same "thing" to a crap-load of records, I use SQL... deleting all orders from Texas. Adding 10% markup to all products. That kinda stuff.

If I have to look through a bunch of records, analyze each one, and handle what happens differently (possibly for each record) then I use a recordset loop.

But that's just a rule of thumb. There are always options.

Brent: Recordsets CAN be faster, but it depends on the situation, and once I upgraded my backend to SQL Server, I started moving things that I traditionally did with recordsets over to SQL if possible, because that will ALWAYS execute faster on the server. Otherwise, Access has to pull down each record, analyze it, and send it back up. If you can do it with a passthru query on the server, it will run MUCH faster.

I like this thinking. The speed at which SQL clobbers data still alarms me - even after all these years, despite my routinely making an insurance copy of a table before running some innocent looking Action Query. This has been my custom since "the one" time I didn't assume the worst and for my moment of rash hubris paid with several hours of hand typing data into emptied fields while the other kids were frolicking loudly out by the swimming pool.

Thanks Richard for your explanation!  I wish I would have picked this stuff up way earlier in life. I began using access in the late 90's and built a purchasing system for the company I worked for then. Self taught then and the database was great for what we used it for but it was very rudimentary. After I left that job, I moved into sales and did not pick access back up until 2020 and I only did then because I needed a CRM and the company was slow in providing. I use the CRM I created daily and it works great for me thanks to your wonderful lessons!! It is much better than I could ever imagine but I have made so many mistakes in it. Luckily it is just for me but it has been a great tool and I want to continue to make it better because I love this stuff!!! Again thanks for sharing your knowledge with all of us hacks!!! And thanks to all of your moderators!! Everyone is extremely helpful and never condescending! Believe me, that means a lot to all of us who don't do this as our job but do want to create a tool to help us do our jobs better and more efficiently!

Have a great day!!!

Let me echo your sentiments, Brent. We have had remarkably similar histories with Access and Rost. And now, in retirement from a 35 year career in contract accountancy, this remarkable pair has reemerged to challenge my day. Really wonderful, isn't it?