Microsoft KB Archive/295211

= ACC2002: &quot;Incorrect Syntax near 'COLLATE'&quot; Error Message When You Import Tables into a Microsoft Access Project =

Article ID: 295211

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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 &quot;Symptoms&quot; 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.



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 &quot;Symptoms&quot; section of this article.

Additional query words: pra prb import adp collate

Keywords: kberrmsg kbbug kbnofix KB295211

-

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

© Microsoft Corporation. All rights reserved.