ACC2002: "Incorrect Syntax near 'COLLATE'" Error Message When You Import Tables into a Microsoft Access Project (295211)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q295211
Novice: Requires knowledge of the user interface on single-user computers.

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

SYMPTOMS

When you import a table into a Microsoft Access project (ADP) from another Access project, you may receive an error message similar to the following:
[Microsoft][ODBC SQL server Driver][SQL Server] Line 5: Incorrect Syntax near 'COLLATE'.
When you click OK, you receive the following message:
There were errors during the database transfer operation. Open the transfer log for details.

CAUSE

You may receive an error message similar to the one in the "Symptoms" section under the following conditions:
  • You have one Access project that is connected to SQL Server 2000.
  • You import a table from this Access project into another Access project that is connected to SQL Server 7.0.
  • There are statements that are valid only in SQL Server 2000.

RESOLUTION

  1. Open your Access project that is connected to SQL Server 2000.
  2. Right-click the table that you want to import to your Access project that is connected to SQL Server 7.0, and then click Export.
  3. In the Export Table To dialog box, click Text files (*.txt,*.csv,*.tab,*.asc) in the Save as type box.
  4. Note the location where the file will be exported. Then, click Export and proceed through the Export Wizard.
  5. Close the project that is connected to SQL Server 2000, and then open the project that is connected to SQL Server 7.0.
  6. On the File menu, point to Get External Data, and then click Import.
  7. In the Import dialog box, click Text files (*.txt,*.csv,*.tab,*.asc) in the Files of type box, browse to the location where the text file was exported, and then click Import.
  8. Follow the steps in the Import Wizard to import the file.

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new Access project that is connected to SQL Server 2000, and then connect it to the Pubs sample database.
  2. Save the project as ADP2k.adp.
  3. Create another Access project that is connected to SQL Server 7.0, and then save the project as ADP70.adp.
  4. With the ADP70.adp file still open, click Get External Data on the File menu, and then click Import.
  5. Browse to the location of the Adp2k.adp file, click the database, and then click Import.
  6. Select one of the tables on the Tables tab, and then click OK. Note that you receive the error message that is mentioned in the "Symptoms" section of this article.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbbug kberrmsg kbnofix KB295211
©2003 Microsoft Corporation. All rights reserved.