Updating a recordset

Hi Using A2003 on XP I am wondering from the MVP's and others, what is the most efficient way (in terms of time to process) of updating data in a table, using the docmd. eg: (if you need it) 1.-------------------------- mysql = "Update [mytable] SET [Some Field] = [somevalue];" docmd. Run SQL mysql 2.----------------------- set myrecordset = mydb. Execute db Fail On Error ' Takes a brief time to compile before running. If you execute a SQL statement repeatedly, compiling it each time, this will be much slower than if you compile it once, then execute it multiple times. If you do this with a saved query that takes parameters, the query will be compiled the first time you run it, and the compiled state will be saved with the query and reused next time.

The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.

If you do it with Find First, you are searching through the rows in a recordset with no optimization at all.

Another factor is that any query, whether it is a SELECT or an UPDATE must first be compiled before it is run.

Well, sometimes you can edit data in the Datasheet View of a query to change the information in the underlying table. The Microsoft Office Access Help system has a fairly extensive list that details when queries are updateable and non-updateable. It is also in different places depending on the Access version you're using.

You would think that typing "updateable recordset" would find the information, but it doesn't.

Search for updating a recordset:

updating a recordset-77

Leave a Reply

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

One thought on “updating a recordset”

  1. Unique Site Features Three unique features have served me well on Korean Cupid: Dating is a Numbers Game As I mention in my online dating e-book, just like the real world, there will be men or women who you will not click with or who don’t respond to your messages (or just disappear).

  2. We’ll display some of our most eligible singles for you to browse, you can use ‘Shuffle’ to match with singles who also ‘like’ you profile photos and interests, or you can use the search function.