Microsoft Access ADP UPDATE Query does NOT update

Question

I have a (very simple and standard) UPDATE statement which works fine either directly in Query Analyser, or executed as a stored procedure in Query Analyser.

UPDATE A
SET 
      A.field1 = B.col1
    , A.field2 = B.col2
FROM
       tblA AS A INNER JOIN tblB AS B 
ON A.pk1 = B.pk1 AND A.pk2 = B.pk2

Problem is when i execute the same stored proc via microsoft ADP (by double-clicking on the sproc name or using the Run option), it says "query ran successfully but did not return records" AND does NOT update the records when i inspect the tables directly.

Before anyone even says "syntax of MS-Access is different than SQLServer T-SQL", remember that with ADP everything happens on the server and one is actually passing thru to T-SQL.

Any bright ideas from any ADP gurus out there?

Answer 1

Try and see whether the query gets executed on the SQL Server using SQL profiler.
Also, I think you might need to close the linked table & re-open it to see the updated records.

Does that work?

Answer 2

I seem to remember that I always got the "didn't return any rows" message and had to simply turn off the messaging. It's because it isn't returning any rows!

as for the other - sometimes there's a primary key issue. Does the table being updated have a primary key in SQLServer? If so, check the view of the table in Access - sometimes that link doesn't come through. It's been a while, so I could be wrong, but I think you may need to look at the design view of the table while in access and add the primary key there.

EDIT: Additional thought: in your debugging, try throwing in print statements to see what the values of your inputs are. Is it actually picking up the data from the table as you expect when you execute from access?

Answer 3

Run the query with SQL PRofiler running. Before you start the trace add in all the error events. This will give you any errors that the SQL Server is generating that the Access ADP might not be showing correctly (or at all).

Feel free to post them here.

Answer 4

Gotcha. Responding to my own question for the benefit of anyone else.

Tools / Options / Advanced / Client-Server Settings / Default max records is set at 10,000 (presumably this is the default). Change this to 0 for unlimited.

My table had 100,000+ rows and whatever set of 10,000 it was updating was difficult to find ( among a sea of 90,000+ un-updated rows ). Hence the update did not work fully as expected.

Answer 5

Just as a reference, here's a paper I wrote on Update Queries that discusses some of the issues associated with when the fail.

http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html