Access linked tables to SQL Server getting locked

Question

This is an A2K3 application with tables linked to a SQL Server 9.0 back end.  Here is the problem:

When I run these 2 queries, the 1st one is successful, the 2nd one fails:

qryAppend_to_TableA: successful

qryAppend_to_TableB: fails

The error is "Access can't append all the records in the append query .. 13 record(s) to the table due to key violations"

If I reopen the application, qryAppend_to_TableB runs successfully without error.

If I reverse the order, the same same thing:

qryAppend_to_TableB: successful

qryAppend_to_TableA: fails

The error is "Access can't append all the records in the append query .. 50 record(s) to the table due to key violations"

If I reopen the application, qryAppend_to_TableA runs successfully without error.

Does anyone know what causes this and, more importantly, how to fix it?  I will have a whole bunch of these action queries and do not want to reopen the mdb to run each one.  I don’t own this application and converting it to a different format or not using linked tables is not an option.  Thank you

Answer 1

Please post the code that runs these queries...  OR ar you just running them manually?

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 2

It could be that you're not using dbSeeChanges. Show us the code you're using the execute the queries.

"A.Stanton" wrote in message news:*** Email address is removed for privacy *** .com...

This is an A2K3 application with tables linked to a SQL Server 9.0 back end.  Here is the problem:

When I run these 2 queries, the 1st one is successful, the 2nd one fails:

qryAppend_to_TableA: successful

qryAppend_to_TableB: fails

The error is "Access can't append all the records in the append query .. 13 record(s) to the table due to key violations"

If I reopen the application, qryAppend_to_TableB runs successfully without error.

If I reverse the order, the same same thing:

qryAppend_to_TableB: successful

qryAppend_to_TableA: fails

The error is "Access can't append all the records in the append query .. 50 record(s) to the table due to key violations"

If I reopen the application, qryAppend_to_TableA runs successfully without error.

Does anyone know what causes this and, more importantly, how to fix it?  I will have a whole bunch of these action queries and do not want to reopen the mdb to run each one.  I don�??t own this application and converting it to a different format or not using linked tables is not an option.  Thank you

---

Regards, Graham R Seach Sydney, Australia

Answer 3

How are you running these queries?  You write "When I run these 2 queries..." hinting you run them interactively. Is that so? If not, post some code.

---

-Tom. Microsoft Access MVP

Answer 4

Tis bot the SQL Server tables getting locked, see...

http://www.regina-whipp.com/index_files/ActionQueryErrors.htm

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Thank you Gina but none of that applies.  The error I reported is a typically misleading Access error: it doesn't report what the underlying problem is.  I'm not sure what the problem is but it has to do with the backend db getting locked once the first action query is run.  Either of the two queries, doesn't matter which one, will run successfully without error, when run first.  The query that is run second will fail with that bogus error.

I've checked the schema of the backend tables, including their keys and indexes, as well as the data values being inserted and there are no key violations.  Setting the "Use Transaction" property to "No" makes no difference.

Any other ideas?  Thank you

Answer 5

Tis bot the SQL Server tables getting locked, see...

http://www.regina-whipp.com/index_files/ActionQueryErrors.htm

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.