Home / Free xrated dating site / Updating sql server dao recordsets

Updating sql server dao recordsets

This is a simple view of the situation, but in reality there may be calculations going on and many other things.

But for this question assume a simple creation of records and/or updating existing records.

A new acquaintance of mine has questioned this as he uses SQL statements extensively.

His code includes constructing SQL statements that he then executes to make the changes.

But if you expect to insert multiple records, that is not necessarily the case. Execute ("Delete * from t1") T = Get Tick Count rs. Connection, ad Open Forward Only, ad Lock Optimistic For I = 1 To C rs. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have VBA code that wrote the results of that away to the db, either creating new records or updating existing records, whichever was relevant. Now I generally do this by opening a recordset on the source data and then work though that in a loop and make my data changes.Changes to the data structure would require revisiting all relevant SQL statements, but then it may also require revisiting my code (except that it a lot of situations I write the data across by looping through the fields collection of the destination and don't actually code in field names except where absolutely necessary). One example of a combination approach might be where you need to update a specific group of records using a complex formula implemented as a VBA function.Making VBA function calls from queries is slow, and finding a subset of records by looping through a recordset is slow, so the best bix is to use SQL to retrieve the set of rows to be ubdated, then loop through that resulting recordset to apply the updates.Suppose 20 Users call the SQL (on discrete sets of records) right now. We find we have to examine (with a function in code) much of the data in each record to find which record in Table1 relates to which record in Table2.And then we have to do the work, eg, create genealogical html files and upload them to our genealogy site. During these scans we are trying, testing, discarding and choosing. If we are going to change the value of something 47 times, do we want to write each of those changes to our DB?And of course, with a clever use of Table and Index Creation within Transactions we can effect disconnected Recordsets in DAO. Nothing is ever written to the DB (unless we want it to be). Print Get Tick Count - T End Sub Output: 31, 100 This is the more likely scenario, IMNSHO.Such procedures while demanding to code can be extremely fast and powerful. If you can do a bulk operation using SQL this will always be faster (unless the SQL calls a function when it might not be faster, depends on the function) than walking the recordset.If you are doing a single insert or single row update, SQL would likely be faster.But if you expect to insert multiple records, that is not necessarily the case.I can see this would work and it has made me think about the pros and cons of this. The real question wold be, what is the best way to combine code and SQL to achieve the best performance.This would not always be a suitable solution, but may be a simpler and cleaner one in some situations. I assume there are no problems with executing multiple SQL statements within a transaction so that either everything succeeds or fails. There also may be other considerations besides speed, such as the ease with which you can insert long text into a Memo field (much easier with a recordset).

272 comments

  1. Or Update sql vba statement fastest. My first suggestion would be to use DAO. Add a column with a default value to an existing table in SQL Server.

  2. You need to create a CONNECTION object from an ODBC workspace - then you can open recordsets and/or execute SQL against it. Check out the createworkspace method in.

Leave a Reply

Your email address will not be published. Required fields are marked *

*