Solved

Best Practices -> ADODB Recordset vs DoCmd.RunSQL

Posted on 2006-11-02
3
992 Views
Last Modified: 2008-02-01
Hi Experts,

I'm making good use of ADODB recordsets. The capabilities they offer are amazing.

I have some operations, however, where I don't really need all the capabilities of a recordset. A simple SQL statement will do the job just fine.

Are there any recommendation when to use recordsets vs. plain SQL?

Thanks for your help.
0
Comment
Question by:arettig
  • 2
3 Comments
 
LVL 8

Expert Comment

by:dds110
Comment Utility
You can use the ADODB Connection object to run execute Insert, Update, and Delete SQL statements without having to open a Recordset Object.

For example:

Dim db As New ADODB.Connection
With db
  .Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='yourDbPath\blah.mdb';User Id=Admin;Password=;")
  .Execute ("INSERT INTO Table1 (blah) VALUES('blah')")
  .Close
End With
0
 
LVL 8

Accepted Solution

by:
dds110 earned 500 total points
Comment Utility
In addition to my previous post, The recordset object is good for returning records but tends to bulk things up when all you need to do is update, delete, or insert.   DoCmd.RunSql is access specific and won't work, for instance, if you decide to put a web front end on top of your db.
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
There is no right answer, only some tradeoffs to consider.  The matter is very similar to the difference between using sql set operations or cursors. Performance is one issue--not only which is more efficient, but where do you want to put the workload.  Maintainability is another issue.

SQL operations occur predominantly within the DBMS and have an efficiency advantage over recordsets. To effect an SQL command the DBMS may construct an implicit cursor (much like a recordset) internally, but it is nearly always assured to use less resources than an explicit cursor that does the same thing. Performance advantage goes to SQL set operations.

SQL operations yield a relatively thin client, meaning the DBMS does most of the work.  They also tend to put less traffic on the network.  I can't really say that is an advantage, it depends on which parts of the system you desire to burden.  Sometimes you may want to leverage the more powerful DBMS server and give the network a break. Other times it may be preferable to distribute the workload to tens or hundreds of clients.  Be aware, that it also depends on the particular thing you are doing.  Sometimes the tradeoff is between a lot of work on the server and a little work on the client, in which case the client can do it faster anyway.  For example, suppose we have a table with ten rows and we need to look at each row twice.  With SQL we would be making 20 requests to the DBMS and sending 20 rows over the network.  With a recordset, we make 1 request to the DBMS and send only 10 rows over the network.  Also, note that with an Access backend, this maybe somewhat of a non-issue because all the work is done at the workstation, anyway.  But with more traditional RDBMS's it may be very significant.

From a maintenance and development standpoint, I give recordsets the advantage.  In my opinion the SQL string constructions are often simpler and the follow-on code is easier to follow. Notwithstanding that, some things that are very simple to do with a recordset are very difficult with SQL.  Ever try returning a field containing a concatenated list someone's children's names?  Trivial with a recordset, inexorably difficult with SQL.  IMHO, recordset techniques are more flexible and usually easier to maintain than native SQL. They also lend themselves to a more procedural programming style.  While not necessarily a good thing, many programmers are more comfrotable that way.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now