Microsoft KB Archive/264097

= ACC2000: Error Message or Empty Field List in an Access Project with a Form or a Report Based on a Stored Procedure =

Article ID: 264097

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q264097



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

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



SYMPTOMS
In an Access project in which you have the record source of a form or a report set to a stored procedure that selects from a local or a global temporary table, the field list is empty.

You may also receive the following error message when you try to set the RecordSource property of the form or the report, or when you type the control source for a control on the form or the report in the ControlSource property box:

Microsoft Access can't find the object 'Microsoft Access can't find the object ' '.'


 * You misspelled the object name. Check for missing underscores(_) or other punctuation, and make sure you didn't enter leading spaces.


 * You tried to open a linked table, but the file containing the table isn't on the path you specified. Use the Linked Table Manager to update the link and point to the correct path.

However, after you click OK in the error message, Access accepts the control source or the record source.



CAUSE
In cases where Microsoft Access prepares a SQL statement or a stored procedure in order to determine column information for stored procedures that build a dynamic SQL string, SQL Server does not return column metadata on a simple prepare. Output column information can only be determined on an execute.



RESOLUTION
As a workaround, when you use stored procedures that select from temporary tables, make note of the field names available, and when you create the controls in the form or the report, manually set the control source for each. If you see the error message described in the &quot;Symptoms&quot; section of this article, click OK. If you typed the control source or record source correctly, Access will accept it, and the form or the report will run without error.



Steps to Reproduce the Behavior
 Open the sample Access project NorthwindCS.adp. On the Insert menu, click Stored Procedure.  Paste or type the following stored procedure: Create Procedure ProductRepSp (@BeginDate datetime, @EndDate datetime) As SET NOCOUNT ON  CREATE TABLE #TEMP (ORDERID INT NULL,   ORDERDATE DATETIME NULL,    [NAME] VARCHAR (50)    )

INSERT INTO #TEMP SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName FROM Orders INNER JOIN [Order Details] ON       Orders.OrderID = [Order Details].OrderID INNER JOIN Products ON       [Order Details].ProductID = Products.ProductID WHERE (Orders.OrderDate BETWEEN @BeginDate AND       @EndDate)

SELECT ORDERID, ORDERDATE, [NAME] FROM #TEMP  Save and close the stored procedure. Double-click the stored procedure to run it, and then enter the following dates for parameters: 1/1/97 and 12/31/97. Verify that the stored procedure returns records. Close the datasheet.</li> On the Insert menu, click Report.</li> In the New Report dialog box, click Design View, and then click OK. Note that a new blank report appears.</li> If the property sheet is not displayed, click Properties on the View menu. The Report property sheet appears.</li> In the Report property sheet, click the Data tab, and then type ProductRepSp in the RecordSource property.

Note that you receive the error message described in the &quot;Symptoms&quot; section of this article. Click OK.</li> If the field list is not displayed, click Field List on the View menu.

Note that the field list is empty, even though it should contain the ORDERID, ORDERDATE, and NAME columns.</li></ol>

Additional query words: prb can t

Keywords: kbprb kbclientserver KB264097

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.