ACC2002: Error Message: Start Position Is Before the Beginning or Past the End of the Rowset. No Rows Were Returned (287446)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q287446
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

When you copy or import a table in a Microsoft Access project (.adp), the new table is created, but no data is copied to the new table, and the primary key may not have been created. Depending on the size of the table, you may also receive the following error message:
Start position is before the beginning or past the end of the rowset. No rows were returned.

CAUSE

There are embedded spaces within one or more field names in the table that you are trying to copy or import.

In the SQL Distributed Management Objects (SQL-DMO) process used by Access, neither brackets ([]) nor quotation marks ("") are placed around field names in the underlying T-SQL statement used to create a primary key or to insert records into the new table. Spaces within field names cause the statement to fail because the syntax is incorrect. If there is a space in any field name in the table, the new table will be created, but the data will not be copied. Additionally, if any field that makes up the primary key contains an embedded space, the primary key will not be created.

The symptoms can occur if each of the following is true:
  1. You have the Microsoft SQL Server 2000 version of SQL-DMO installed on the client computer. SQL-DMO can be installed in several ways, including:
    • Installing a version of SQL Server 2000 on the client computer, including SQL Server 2000 Desktop Edition.
    • Installing the SQL Server 2000 Client Tools.

  2. You have embedded spaces in one or more field names in the table that is being copied or imported.
Note that the error can occur regardless of the SQL Server version used on the server computer that contains the data.

RESOLUTION

Use one of the following methods to work around the problem:
  • Modify the table's design to remove spaces from all field names (recommended).
  • Use a make-table query to create a new table. You must manually set the primary key on the new table after you run the query.
  • Uninstall SQL Server 2000 from the client computer, including the shared file SQLDMO.dll. You must manually create the primary key field on the new tables after copying.
The first method listed is the only method that will enable Access to copy or import a table from the user interface without requiring the user to manually create the primary key afterward.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Depending on the client computer's configuration, Access uses one of two methods to connect to SQL Server to copy or import a table:
  1. If Access detects that the SQL Server 2000 version of SQL-DMO is on the client computer, Access uses SQL-DMO to create both the table and the primary key, and then does a bulk insert to copy the data.
  2. If Access does not detect the SQL 2000 version of SQL-DMO, Access connects to SQL Server directly to create the table (without the primary key) and inserts the data one record at a time (similar to the method used in Access 2000).
Access only checks for SQL-DMO the first time that a user tries to copy or import a table for any particular instance.

Steps to Reproduce the Behavior

  1. On a computer that has SQL Server 2000 installed, open the NorthwindCS.adp sample project file.
  2. Open the Customers table in Design view. Change the name of the ContactTitle field to Contact Title (with an embedded space).
  3. Save and close the Customers table.
  4. In the Database window, right-click the Customers table, and then click Save As on the shortcut menu.
  5. Click OK in the Save As dialog box.

    Note that the table is copied, and then you receive the error message that is mentioned in the "Symptoms" section of this article.

REFERENCES

For more information about how to modify table design, click Microsoft Access Help on the Help menu, type about tables in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about make-table queries, click Microsoft Access Help on the Help menu, type about action queries in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:kbbug KbClientServer kberrmsg kbnofix KB287446
©2003 Microsoft Corporation. All rights reserved.