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.