MS Access 2000 hangs with append queries when run on Windows XP

[ctaylor]

I have some append queries in MS Access 2000 that cause the PC to hang. Recently the PC was upgraded to Windows XP. Prior to this the PC was running Windows 98SE. The same append query works great on Windows 98SE but for some reason it hangs now.

My preliminary research shows that this problem is not unique to me. (http://dbforums.com/arch/42/2003/7/848223) describes the exact same problem and symptoms). I thought the query might just be running slow, so I let it run on an Intel P4 processor PC with 256 MB RAM for 16 hours just to make sure this was not the case. There are about 120 records that I am trying to append.

Also of interest would be the fact that when the query is changed to a SELECT query, it runs with no issues.

Copying the SQL coding for the query and pasting it into a new query duplicates the problem and indicates that the saved query is not corrupt.

(EDIT: Since this problem was first encountered, I created a new MS Access database and imported all the tables, queries, forms etc to see if there might be some sort of background corruption, but this did not fix the problem in the new copy of the database. I know this does not address the OS concern, but thought it was worth trying anyways.)

Any thoughts would be appreciated.

[pedantic]

OS related.

[ctaylor]

I realized yesterday that when the PC was migrated to XP, the virtual memory configuration had been manually overriden and assigned a small value. With Win98SE, dynamic allocation of page file space was enabled.

I suspect that the append query (which is also a resource pig because it is a quasi-cartesian product) consumed all the physical memory, virtual memory and then caused the application to hang. Reviewing the TASK MANAGER was misleading because I suspect that MS Access continued to consume huge amounts of system resources without the ability to complete processes.

[tStroupe]

cTaylor, I am also having a problem with my Access 2000 code hanging up in Windows XP. I also do append queries. How did you resolve your problem?

[ctaylor]

Just yesterday I got my virtual memory configured for dynamic page file allocation. I tried running the append query immediately and 16 hours later the append query has still not completed running.

I wish I had found a solution.........

What have you tried (even if it did not resolve the issue)?

[tStroupe]

I initially thought my problem had to do with Access reports and XP, so I replaced the report with a form. That did not solve the problem. I do have 4 append statements prior to displaying the report. That's why I thought I might be having the same problem. Today, I will put the report back and replace the append statements with a make table query or add records to the tables I need via recordsets.

[tStroupe]

Also, I checked my client's virtual memory configuration. He had 1152Mb initial and 2304Mb maximum. Doesn't that seem adequate?

[ctaylor]

step 1: for some bizarre reason when I created a new MS Access database file and imported all MS Access objects into it from the DB that would not run the append query, the new DB continued the problem the original DB experienced.

step 2: When I created a third MS Access DB file and imported all objects from the second MS Access DB, the problem appeared to have cured itself......

BAD DB ==> SECOND DB ==> THIRD DB

weird, huh?

[tStroupe]

I'm glad your problem was solved. How did you know that it was the append command that was problematic?

I have 2 programs. The 1st program creates/edits/submits things to the 2nd program that can edit, than approve the sumbittal. 2nd program was a copy of the 1st program w/ unneed steps removed. Both worked fine in Windows 2000. 1st program works fine in Windows XP but the 2nd hangs. I don't believe the append commands are the culprit because the same append commands are run in both programs. Any ideas?

[pedantic]

still, OS related.

what's an "append query"? btw?

SQL union?