Edit ADO queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wheesley - 09 May 2005 11:12 GMT

Hi
I have an Access2000 table with about 500'000 data records. Sometimes it's
necessary to change some values in the records. At the moment I do this with
creating a query with "querydef". This query shows me the records I want to
change. Since the query is linked with the table it's very easy to change the
records. But because the connection is slow, this query needs a few minutes
to proceed.
So I tried it with ADO.Command and Parameters. Now I have the data in a few
seconds, but how it's possible to change the records now? Or am I really on
the right path doing it with ADO.Command?
I 'd be grateful for every help
thx

Urs

Reply to this Message

Tim Ferguson - 09 May 2005 17:45 GMT

> I have an Access2000 table with about 500'000 data records. Sometimes
> it's necessary to change some values in the records.

If there is a rule about the change, say changing taxrates from 10 to 12.5,
then a simple update query would do it:

UPDATE MyTable
SET TaxRate = 12.5
WHERE TaxRate = 10.0

More complex updates can usually be accomplished using more complex SQL.

On the other hand, if the changes are random or arbitrary, then the best
you can do is either (a) create a form and base it on a suitable query that
limits the number of records exposed, or (b) use a querydef and a datasheet
window -- for quick-and-dirty one-off updates only!

Hope that helps

Tim F

Reply to this Message