ACC2002: Action Query Does Not Affect All Valid Records in an Access Project 

Q287429


 --------------------------------------------------------------------------------
 
The information in this article applies to:

Microsoft Access  2002 

 --------------------------------------------------------------------------------
 
Advanced: Requires expert coding, interoperability, and multiuser skills. 

This article applies only to a Microsoft Access project (.adp). 



SYMPTOMS
After you run a stored procedure that updates, appends, or deletes records from a table, you notice that not all of the records that match the specified criteria are updated, appended, or deleted. 



CAUSE
The Default Max Records option is set to a lower number than the number of records that match the query's criteria. 



RESOLUTION
Use one of the following methods to work around this behavior.

NOTE: Method 2 and Method 3 allow the stored procedure to update, append, or delete the correct number of records. However, you may not see all of the changes if the Default Max Records option limits the number of records you can see. 

Method 1: Increase the Default Max Records Option
By setting the Default Max Records option, you can control how many records will be affected by action queries in the Access project. If you enter a zero (0), there is no limit. If you enter a number other than 0, only that number of records will be affected by action queries in the Access project. To change the Default Max Records option, follow these steps: 
Open the Access project (.adp) that you want to change the setting for.


On the Tools menu, click Options.


Click the Advanced tab.


Set the Default Max Records option under the Client-server settings box to a higher number, or set it to 0 to not limit the number of records affected. The setting you choose affects only the Access project that is currently open.


Click OK to close the Options dialog box.


Method 2: Add the SET ROWCOUNT Option to the Stored Procedure
Change the stored procedure's T-SQL to include the SET ROWCOUNT option. The SET ROWCOUNT option overrides the Default Max Records option for a specific stored procedure and has no affect on other stored procedures or queries. To change the stored procedure's T-SQL, follow these steps: 
In an Access project, open the stored procedure that you want to override the DefaultMaxRecords property for in Design view.


On the View menu, click SQL View.


Change the T-SQL to include the SET ROWCOUNT option. For example, if you follow the instructions in the  "Steps to Reproduce the Behavior"  section later in this article, you will create a stored procedure with T-SQL similar to: 

ALTER PROCEDURE dbo.spMakeOrders2Table
AS
SELECT dbo.Orders.*
INTO   dbo.Orders2
FROM   dbo.Orders 
To override the Default Max Records setting, you must change the code to: 

ALTER PROCEDURE dbo.spMakeOrders2Table
AS 
SET ROWCOUNT  0 
SELECT dbo.Orders.*
INTO   dbo.Orders2
FROM   dbo.Orders 
The numeric argument following the SET ROWCOUNT option can be any valid integer greater than or equal to zero ( 0 ). If you set the argument to  1  or higher, the stored procedure will only return or modify that number of rows. If you set the SET ROWCOUNT option to  0 , an unlimited number of records will be returned. 


Method  3 : Use an ADO Command Object to Run the Stored Procedure
When you execute a stored procedure by using an ADO command object, Access sends the SET ROWCOUNT  0  option to SQL Server before sending the instructions to execute the stored procedure. For example, if you follow the instructions in the  "Steps to Reproduce the Behavior"  section later in this article, you will create a stored procedure called spMakeOrders2Table. To run this query from an ADO command object, use VBA code similar to the following in a code module: 

Public Sub MakeNewTable()

   Dim cmd As ADODB.Command
   Set cmd = New ADODB.Command

   cmd.ActiveConnection = CurrentProject.Connection
   cmd.CommandText =  "spMakeOrders2Table" 
   cmd.Execute

   Set cmd = Nothing

End Sub 
This code will override the Default Max Records setting, and will affect an unlimited number of records. 



STATUS
This behavior is by design. 



MORE INFORMATION
The Default Max Records option is designed to limit the number of records returned in a query. However, it also limits the number of records affected by stored procedures that perform updates, inserts, or deletes. 

Steps to Reproduce the Behavior
Open the sample project NorthwindCS.adp.


On the View menu, point to Database Objects, and then click Tables.


Open the Orders table. Note that there are  830  records in the table.


Close the table.


On the Tools menu, click Options.


Click the Advanced tab.


Set the Default max records option to  500 , and then click OK to close the Options dialog box.


On the Insert menu, click Query.


In the New Query dialog box, click Design Stored Procedure, and then click OK.


In the Add Table dialog box, click the Orders table, click Add, and then click Close.


Click to select the * (All Columns) check box.


On the Query menu, click Make-Table Query.


Type Orders2 in the Table name box, and then click OK.


On the File menu, click Save. Save the stored procedure as spMakeOrders2Table.


On the Query menu, click Run. Note that you receive the message:


The stored procedure executed successfully but did not return records. 
Click OK to dismiss the message, and then close the query window.


On the View menu, point to Database Objects, and then click Tables.


On the View menu, click Refresh. Note that the Orders2 table appears in the tables list.


Open the Orders2 table.


Note that the table only contains  500  records although the original Orders table contains  830  records. 



REFERENCES
For more information about SQL Server programming commands such as SET ROWCOUNT, visit the Microsoft SQL Server Developer Center at the following Microsoft Web site: 

http://msdn.microsoft.com/sqlserver/
For additional information about how an Access project (.adp) uses the DefaultMaxRecords setting, click the article number below to view the article in the Microsoft Knowledge Base: 
Q283200 ACC2002: How Access Projects (ADPs) Use the DefaultMaxRecords Setting and the T-SQL SET ROWCOUNT Statement