Access 2003 Append query hangs

[John Thoren]

An append query I have been using monthly since 2002 all of a sudden will
start running but hang and never finish. I tried making it a make table
query and got the same results. If I change it to a select query it runs in
3 seconds. One source of the query is another select query that gets data
from a SQL data base via ODBC links. In January we updated this SQL data
base from MSSQL 2000 to MSSQL 2005 but I ran this query the end of Jan and
Feb with no problems. I am running Access 2003 SP3 and WinXP SP3 operating
system. Anyone know what is causing my problem?

 

[MGFoster]

An append query I have been using monthly since 2002 all of a sudden will
start running but hang and never finish. I tried making it a make table
query and got the same results. If I change it to a select query it runs in
3 seconds. One source of the query is another select query that gets data
from a SQL data base via ODBC links. In January we updated this SQL data
base from MSSQL 2000 to MSSQL 2005 but I ran this query the end of Jan and
Feb with no problems. I am running Access 2003 SP3 and WinXP SP3 operating
system. Anyone know what is causing my problem?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually, this is caused by someone else updating the table, which caused
the table to become locked. The append is waiting for the update (lock)
to end before it proceeds. When this happens you might try to add a row
(record) to the target table to see if the table is truly locked.
Sometimes users will open a form, start an edit and then go to lunch
without updating the data. This can cause the table to be locked.
Especially if the form has "Lock All Records" set on the form's Record
Locks property.

The next thing to do is look at the query's execution plan. You have to
change a Registry setting to do this. Unfortunately, I don't know which
setting to change. This has been discussed on this newsgroup before.
Use Google groups advance search to find those discussions - look for
"execution plan." You might find this in the Access Help, but its
search abilities is damn, near useless.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSaxckIechKqOuFEgEQIPVQCeJ6ZLjxVIsGt1TwSIzzMu2ldt6acAoL9y
MXPJOXQViam3hAKNnu31rUcT
=FIwg
-----END PGP SIGNATURE-----

 

[John Thoren]

--
John Thoren
Berner Foods Inc

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually, this is caused by someone else updating the table, which caused
the table to become locked. The append is waiting for the update (lock)
to end before it proceeds. When this happens you might try to add a row
(record) to the target table to see if the table is truly locked.
Sometimes users will open a form, start an edit and then go to lunch
without updating the data. This can cause the table to be locked.
Especially if the form has "Lock All Records" set on the form's Record
Locks property.

The next thing to do is look at the query's execution plan. You have to
change a Registry setting to do this. Unfortunately, I don't know which
setting to change. This has been discussed on this newsgroup before.
Use Google groups advance search to find those discussions - look for
"execution plan." You might find this in the Access Help, but its
search abilities is damn, near useless.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSaxckIechKqOuFEgEQIPVQCeJ6ZLjxVIsGt1TwSIzzMu2ldt6acAoL9y
MXPJOXQViam3hAKNnu31rUcT
=FIwg
-----END PGP SIGNATURE-----

I fixed my own problem. I restored an earlier version of the query form a
backup of the data base and now the query works. Some how the query got
corrupted and would perform as a select and not as an append or update. Is
this possible?