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

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 'StoredProcedureName'.'

*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.

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 "Symptoms" 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.

MORE INFORMATION

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.
On the Insert menu, click Report.
In the New Report dialog box, click Design View, and then click OK. Note that a new blank report appears.
If the property sheet is not displayed, click Properties on the View menu. The Report property sheet appears.
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 "Symptoms" section of this article. Click OK.
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.