Aborted queries

[Killer42]

Hi all.

Have any of you experienced update queries (Access 2003) which simply stop before finishing? This is just the latest example, not the only one.

To be more specific, I am running a query which updates two fields in an entire table of something like 12 million records. It chugs along for a while, then asks the usual "this will update something, are you sure?" question. I say yes. It works for another half-hour or whatever, then for no apparent reason, simply stops as though I had pressed the Break key. When I look at the table, 3 million or so records have been updated, the rest not.

The table does not need compacting. (Though I will if/when the update is complete, especially since it's updating indexes and "nulling out" a field). I know (from bitter experience) that it is nowhere near the size limit (2GB) for a database (Access 2000 file format).

What gives?! Some sort of buffer problem, perhaps? I know it's not the amount of RAM available (heaps left), but I also know that Access has some apparently very weird memory limitations at times. I have in the past hit errors suggesting I increase the maximum record locks in the registry, but this case appears quite different.

If it's any help, here's the actual query I'm running - those involved in my other threads will undoubtedly recognise it...

UPDATE Log2004B INNER JOIN Descriptions
ON Log2004B.Description = Descriptions.Description
SET Log2004B.DescID = Descriptions.ID, Log2004B.Description = Null
[I]WHERE (((Log2004B.Description) Is Not Null))[/I];

Note, the WHERE clause was just added to finish up the records missed in the aborted run.

[MMcCarthy]

Check out the size of your database after the query bugs out.

At a guess I'd say you're hitting the max size again. Don't forget access uses memory in queries. Try the old C&R and run query immediately afterwards. You might get away with it.

This is why i prefer recordsets. Doesn't seem to have the same problem.

Mary

Hi all.

Have any of you experienced update queries (Access 2003) which simply stop before finishing? This is just the latest example, not the only one.

To be more specific, I am running a query which updates two fields in an entire table of something like 12 million records. It chugs along for a while, then asks the usual "this will update something, are you sure?" question. I say yes. It works for another half-hour or whatever, then for no apparent reason, simply stops as though I had pressed the Break key. When I look at the table, 3 million or so records have been updated, the rest not.

The table does not need compacting. (Though I will if/when the update is complete, especially since it's updating indexes and "nulling out" a field). I know (from bitter experience) that it is nowhere near the size limit (2GB) for a database (Access 2000 file format).

What gives?! Some sort of buffer problem, perhaps? I know it's not the amount of RAM available (heaps left), but I also know that Access has some apparently very weird memory limitations at times. I have in the past hit errors suggesting I increase the maximum record locks in the registry, but this case appears quite different.

If it's any help, here's the actual query I'm running - those involved in my other threads will undoubtedly recognise it...

UPDATE Log2004B INNER JOIN Descriptions
ON Log2004B.Description = Descriptions.Description
SET Log2004B.DescID = Descriptions.ID, Log2004B.Description = Null
[i]WHERE (((Log2004B.Description) Is Not Null))[/i];

Note, the WHERE clause was just added to finish up the records missed in the aborted run.

[Killer42]

Check out the size of your database after the query bugs out.
At a guess I'd say you're hitting the max size again. Don't forget access uses memory in queries. Try the old C&R and run query immediately afterwards. You might get away with it.
This is why i prefer recordsets. Doesn't seem to have the same problem.
Mary

Ok, taking things in order...

• No, it's nowhere near the size limit. I have had the same problem on a 1.7GB database and a 500MB database.
• I can re-run the update without compacting, and it will (sometimes) run ok. In fact it did this afternoon, getting through the other 9M+ records no worries. The size increased from around 1.7GB to around 1.8GB.
• Not sure what you mean about recordsets. Please explain?

[MMcCarthy]

Ok, taking things in order...

• No, it's nowhere near the size limit. I have had the same problem on a 1.7GB database and a 500MB database.
• I can re-run the update without compacting, and it will (sometimes) run ok. In fact it did this afternoon, getting through the other 9M+ records no worries. The size increased from around 1.7GB to around 1.8GB.
• Not sure what you mean about recordsets. Please explain?

All I mean by this is, Using DAO:

1. Open a recordset of the query being updated
2. As all records are being updated, run through the recordset record by record.
3. Set appropriate field to new value using:

.Edit
rs!FieldName = NewValue
.Update

DoEvents

4. The doevents command should process the update before moving on. This seems to increase speed, it may be that it clears the buffer as it goes along I'm not sure.

5. Loop to next record until EOF

In my experience any time I've had to run an action query to update large amounts of records this seems to increase the speed.

Mary

[mddrains]

Have you tried doing a database repair. Is there a chance there is a corrupted record?

Otherwise, this doesn't answer your question, but may let you update all the records. Is there a field in the database you can use to select part of the data - e.g. a name field where you could do A-F, G-M, N-S, T-Z? Then run the query multiple times.

[Killer42]

Have you tried doing a database repair.

Yes. I have tried continuing the update with and without a compact/repair. Sometimes it continues to the end, sometimes not. There appears to be no correlation between whether it's repaired and whether it finishes.

Is there a chance there is a corrupted record?

Who knows. I'm not aware of any, and the update does eventually finish, though it may take a few attempts.

Otherwise, this doesn't answer your question, but may let you update all the records. Is there a field in the database you can use to select part of the data - e.g. a name field where you could do A-F, G-M, N-S, T-Z? Then run the query multiple times.

Yeah, sometimes I do update it in sections like this. On the other hand, sometimes I can't be bothered, and just keep hitting it until it says there are 0 records to be updated.

Thanks for the input.

This is merely a nuisance, not a disaster. It just means I sometimes have to rerun an update until it completes.

[Killer42]

All I mean by this is, Using DAO:
1. Open a recordset of the query being updated
etc...

Ah, you meant through VB/VBA.

Yes, I have done something similar at time, too.

One problem I have sometimes is that I want to move records from one database to another (in other words, copy to target and delete from source). But because of the target database size (approaching 2GB limit) I know it will crash before completing. That means I'll have duplicate records and have to work out which ones to delete form the source. (I know, I've been there).

So I use a quick VB program to do the copy and delete one record at a time, committing the transaction after each one. That way, when it hits the "invalid argument" at 2GB, I just compact the target and continue on.

It's a neat solution to the problem, but I would not claim any speed increase. I think doing it a record at a time like this is much slower than doing a bulk Update Query in Access.

Does anyone know a better way to move records from one table/database to another, as opposed to copying?

[NeoPa]

I have had similar problems with Access 2K - not exactly the same but it runs for a while then, without finishing, it just stops. I normally only find out much later that it's not still running.
I've never found an explanation or fix though, I'm afraid.
Size limits.
In my experience, you can run into size limit problems well before the db actually reaches full size.
Particularly in your circumstances if the space left is not enough to cope when you run a query requiring large amounts of heap space.
Unfortunately, as mentioned in a previous thread, when MS apps fall short of memory or fall over anything else 'unexpected' they are notoriously poor at handling, or even reporting, the error.

[PEB]

Hi Killer,

Your problem appears coz when you are running your query the database doesn't affect directly your records... The database uses some kind of buffer and writes the information that should be updated...

Once confirmed the database Jet engine begins to write the info into the database!

But in your case the database is enormous! So the transactions can't be saved well in this buffer... And see well it gets double work: Once into the buffer and twice into the table!

So your query is twuce executed /not just twice coz of not ressources available/

The clue that you can apply is running your update query like:

mydb.Execute (SQL)

PLS tell us if there is any difference! :)

[NeoPa]

Also, a performance point for running (non-action) queries with large datasets (couldn't find other thread sry).
Look at the properties of the Access query saved. If it says use transactions or return a Dynaset, then these options will slow down performance.
Use Transactions is only required for an action query.
Returning data to view (not updatable) should always be with a snapshot.
Sorry if this is a little off this topic, but I thought you may be interested.

[Killer42]

Also, a performance point ...

Thanks, both NeoPa and PEB. I'll try to keep these things in mind.