473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Aborted queries

8,435 Expert 8TB
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...
Expand|Select|Wrap|Line Numbers
  1. UPDATE Log2004B INNER JOIN Descriptions
  2. ON Log2004B.Description = Descriptions.Description
  3. SET Log2004B.DescID = Descriptions.ID, Log2004B.Description = Null
  4. WHERE (((Log2004B.Description) Is Not Null));
Note, the WHERE clause was just added to finish up the records missed in the aborted run.
Nov 14 '06 #1
10 2005
MMcCarthy
14,534 Expert Mod 8TB
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...
Expand|Select|Wrap|Line Numbers
  1. UPDATE Log2004B INNER JOIN Descriptions
  2. ON Log2004B.Description = Descriptions.Description
  3. SET Log2004B.DescID = Descriptions.ID, Log2004B.Description = Null
  4. WHERE (((Log2004B.Description) Is Not Null));
Note, the WHERE clause was just added to finish up the records missed in the aborted run.
Nov 14 '06 #2
Killer42
8,435 Expert 8TB
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?
Nov 14 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
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
Nov 14 '06 #4
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.
Nov 14 '06 #5
Killer42
8,435 Expert 8TB
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.
Nov 14 '06 #6
Killer42
8,435 Expert 8TB
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?
Nov 14 '06 #7
NeoPa
32,556 Expert Mod 16PB
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.
Nov 14 '06 #8
PEB
1,418 Expert 1GB
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! :)
Nov 18 '06 #9
NeoPa
32,556 Expert Mod 16PB
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.
Nov 18 '06 #10
Killer42
8,435 Expert 8TB
Also, a performance point ...
Thanks, both NeoPa and PEB. I'll try to keep these things in mind.
Nov 19 '06 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Moritz Beller | last post by:
Hi! string* pWordlist; pWordlist = new string; for(int i=0; i < elements; i++)    pWordlist = ""; ... pWortliste = buf;
2
by: Alex Vinokur | last post by:
------ foo.cpp ------ #include <iostream> using namespace std; int main() { #define FACTOR 10 for (unsigned long array_size = 1; ; array_size *= FACTOR) { int* p = new int;
2
by: Matthias | last post by:
Hi, I am currently writing a gtkmm component which acts as a file browser, much like a small filemanager which can be embedded in gtkmm applications as a scrolled window. I have just...
7
by: Abdul-Wahid Paterson | last post by:
Hi, I have had a site working for the last 2 years and have had no problems until at the weekend I replace my database server with a newer one. The database migration went like a dream and I had...
2
by: ruben | last post by:
Hi: After upgrading 7.4.2 to 7.4.5 quite smoothly in a Red Hat 8.0 box, we are having intermitent issues with certain online PHP transactions, returning this error: "Warning: pg_exec() query...
3
by: Prasad Dannani | last post by:
Hi, I was created a web service which will execute some functions and will do some database activities related to our project When i execute this method directly from browser using webservice...
2
by: Daniel Knöpfel | last post by:
Hi I am develloping an asp.net 2.0 application. For some tasks (daily notifications to users via email), we use background threads. I rather have this task as background thread of the asp.net...
2
by: ntech | last post by:
hi, This is the error that we are getting on postgresql. The error is as follows: current transaction is aborted. queries ignored until end of the transaction block. What might be the real...
64
by: Mika | last post by:
Hello, we understand you guys may be able to help. We have a page which has been working great for over a year and gets many hits. However recently something got changed that we cannot seem to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.