Microsoft Access ADP UPDATE Query does NOT update?

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

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.

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?

To see the updated records I inspect the tables directly in SQL Server, not the linked one. Will try Profiler later on as i've never used it before and need some time to figure it out. – joedotnot Aug 19 '09 at 2:39.

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.

I ran Profiler with all the Error and Warning turned on. Apart from a series of statements indicating that the query does get run (SP:Starting, SQL:StmtStarting, SQL:StmtCompleted, SP:Completed) and some duration times, nothing seems to stand out as an error! At least I can verify that the query is hitting the right server!

– joedotnot Aug 19 '09 at 3:53 ok, so that's a step in the right direction. This tells us that we are hitting the SQL Server, and that the stored procedure is running without issue (at least as far as the SQL Server knows). But then the Access database is rolling back the transaction for some reason.It's been a long time since I've worked in access.

Is there a way to put some debug code into what ever is calling the SQL procedure and see if you can't see if something's going array somewhere along the line? – mrdenny Aug 19 '09 at 5:29.

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

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?

Autonumber isn't the issue. The PK idea is a good thought, but not for an ADP query. – JohnFx Aug 19 '09 at 2:42 I think you are right - it isn't autonumber.

But I do clearly remember that the primary key could be an issue. While it shouldn't matter when simply executing a stored proc, I wonder if executing it from the access environment would still have the primary key issue be a problem. If it were me, I would check on that just to make sure, before hunting down what may be an obscure bug.

– sql_mommy Aug 19 '09 at 3:04 I ignored the "no records returned" msg, I knew it is an update query after all, I would have paid closer attention IF it had said "no rows affected". My example hinted that I am joining on primary keys pk1, pk2. And no, they are NOT autonumber/identity columns.

– joedotnot Aug 19 '09 at 3:07 have you verified that they are marked as primary keys when you view the table in Access? – sql_mommy Aug 19 '09 at 3:09 With respect to your EDIT above, there are no inputs, no print statements to deal with. I define a stored proc in SQL Server, view it thru the access ADP client, and double-click on it (or select and choose the Run option).

– joedotnot Aug 19 '09 at 3:29.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.