Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

View products that this article applies to.

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

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

More information

Steps to Reproduce the Behavior

1. Open the sample Access project NorthwindCS.adp.
2. On the Insert menu, click Stored Procedure.
3. 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
4. Save and close the stored procedure.
5. 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.
6. On the Insert menu, click Report.
7. In the New Report dialog box, click Design View, and then click OK. Note that a new blank report appears.
8. If the property sheet is not displayed, click Properties on the View menu. The Report property sheet appears.
9. 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.
10. 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.

↑ Back to the top

Keywords: KB264097, kbclientserver, kbprb

↑ Back to the top

Article Info
Article ID : 264097
Revision : 2
Created on : 6/25/2004
Published on : 6/25/2004
Exists online : False
Views : 258