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

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:

1. Open the Access project (.adp) that you want to change the setting for.
2. On the Tools menu, click Options.
3. Click the Advanced tab.
4. 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.
5. 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:

1. In an Access project, open the stored procedure that you want to override the DefaultMaxRecords property for in Design view.
2. On the View menu, click SQL View.
3. 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.