Microsoft KB Archive/287429

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

Article ID: 287429

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287429



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:
 * 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:  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 &quot;Steps to Reproduce the Behavior&quot; 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 &quot;Steps to Reproduce the Behavior&quot; 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 = &quot;spMakeOrders2Table&quot; 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.</li> On the View menu, point to Database Objects, and then click Tables.</li> Open the Orders table. Note that there are 830 records in the table.</li> Close the table.</li> On the Tools menu, click Options.</li> Click the Advanced tab.</li> Set the Default max records option to 500, and then click OK to close the Options dialog box.</li> On the Insert menu, click Query.</li> In the New Query dialog box, click Design Stored Procedure, and then click OK.</li> In the Add Table dialog box, click the Orders table, click Add, and then click Close.</li> Click to select the * (All Columns) check box.</li> On the Query menu, click Make-Table Query.</li> Type Orders2 in the Table name box, and then click OK.</li> On the File menu, click Save. Save the stored procedure as spMakeOrders2Table .</li> On the Query menu, click Run. Note that you receive the message:

The stored procedure executed successfully but did not return records.

</li> Click OK to dismiss the message, and then close the query window.</li> <li>On the View menu, point to Database Objects, and then click Tables.</li> <li>On the View menu, click Refresh. Note that the Orders2 table appears in the tables list.</li> <li>Open the Orders2 table.</li></ol>

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

<div class="references_section">