Export to Microsoft Access - "Syntax error in field definition"

[jjfeigal]

Using Crystal Reports 8.0 I have a created a report with a Lotus Domino Server view as the data source. Each data field in the Lotus view was defined twice in the Available Fields list, once as type <string> and once as type <memo>. I selected fields of type <string> for my report.

After I completed the report development, I wanted to export the data from the report to a Microsoft Access 2000 database. I created a new Microsoft Access database and then defined an ODBC Microsoft Database connection.

When I initiated the export from Crystal Reports, I received two errors via pop-up boxes. First, &quot;Failed to export the report.&quot; Second, &quot;Failed to export the report. [Microsoft] [ODBC Microsoft Access Driver] Syntax error in field definition.&quot;

After I modified the ODBC connection definition to turn on SQL tracing, in the ODBC SQL log file I received the following error informaiton:
dde 45c-610 ENTER SQLExecDirect
HSTMT 02772008
UCHAR * 0x0012EC94 [ -3]
&quot;CREATE TABLE CREXPORT2 ( DE_docID<String> VARCHAR(255), DE_creationDate<String> VARCHAR(255), DE_modificationDate<String> VARCHAR(255), DE_caseManager<String> VARCHAR(255), DE_referredBy<String> VARCHAR(255), DE_referredTo<String> VARCHAR(255), DE_lastName<String> VARCHAR(255), DE_firstName<String> VARCHAR(255), DE_middleInitial<String> VARCHAR(255), DE_countyOfResidence<String> VARCHAR(255), DE_primaryServiceOffice<String> VARCHAR(255), DE_swipeCardNumber<String> VARCHAR(255), DE_homePhone<String> VARCHAR(255), DE_employerPhone<String> VARCHAR(255), DE_cellPhone<String> VARCHAR(255), DE_pagerNumber<String> VARCHAR(255), DE_emailAddress<String> VARCHAR(255), DE_homeStreetAddress1<String> VARCHAR(255), DE_homeStreetAddress1B<String> VARCHAR(255), DE_homeCity<String> VARCHAR(255), DE_homeState<String> VARCHAR(255), DE_homeZip<String> VARCHAR(255), DE_alternateStreetAddress1<String> VARCHAR(255), DE_alternateStreetAddress2<String> VARCHAR(255), DE_alternateCity<String> VARCHAR(255), DE_alternateState<String> VARCHAR(255), DE_alternateZip<String> VARCHAR(255), DE_followupContactLastName1<String> VARCHAR(255), DE_followupContactFirstName1<String> VARCHAR(255), DE_followupContactPhone1<String> VARCHAR(255), DE_followupContactPhoneType1<String> VARCHAR(255), DE_followupContactLastName2<String> VARCHAR(255), DE_followupContactFirstName2<String> VARCHAR(255), DE_followupContactPhone2<String> VARCHAR(255), DE_followupContactPhoneType2<String> VARCHAR(255), DE_SSN<String> VARCHAR(255), DE_disability<String> VARCHAR(255), DE_gender<String> VARCHAR(255), DE_dateOfBirth<String> VARCHAR(255), DE_age<String> VARCHAR(255), DE_citizenshipStatus<String> VARCHAR(255), DE_alienRegCardNumber<String> VARCHAR(255), DE_race<String> VARCHAR(255))\ 0&quot;
SDWORD -3

dde 45c-610 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 02772008
UCHAR * 0x0012EC94 [ -3]
&quot;CREATE TABLE CREXPORT2 ( DE_docID<String> VARCHAR(255), DE_creationDate<String> VARCHAR(255), DE_modificationDate<String> VARCHAR(255), DE_caseManager<String> VARCHAR(255), DE_referredBy<String> VARCHAR(255), DE_referredTo<String> VARCHAR(255), DE_lastName<String> VARCHAR(255), DE_firstName<String> VARCHAR(255), DE_middleInitial<String> VARCHAR(255), DE_countyOfResidence<String> VARCHAR(255), DE_primaryServiceOffice<String> VARCHAR(255), DE_swipeCardNumber<String> VARCHAR(255), DE_homePhone<String> VARCHAR(255), DE_employerPhone<String> VARCHAR(255), DE_cellPhone<String> VARCHAR(255), DE_pagerNumber<String> VARCHAR(255), DE_emailAddress<String> VARCHAR(255), DE_homeStreetAddress1<String> VARCHAR(255), DE_homeStreetAddress1B<String> VARCHAR(255), DE_homeCity<String> VARCHAR(255), DE_homeState<String> VARCHAR(255), DE_homeZip<String> VARCHAR(255), DE_alternateStreetAddress1<String> VARCHAR(255), DE_alternateStreetAddress2<String> VARCHAR(255), DE_alternateCity<String> VARCHAR(255), DE_alternateState<String> VARCHAR(255), DE_alternateZip<String> VARCHAR(255), DE_followupContactLastName1<String> VARCHAR(255), DE_followupContactFirstName1<String> VARCHAR(255), DE_followupContactPhone1<String> VARCHAR(255), DE_followupContactPhoneType1<String> VARCHAR(255), DE_followupContactLastName2<String> VARCHAR(255), DE_followupContactFirstName2<String> VARCHAR(255), DE_followupContactPhone2<String> VARCHAR(255), DE_followupContactPhoneType2<String> VARCHAR(255), DE_SSN<String> VARCHAR(255), DE_disability<String> VARCHAR(255), DE_gender<String> VARCHAR(255), DE_dateOfBirth<String> VARCHAR(255), DE_age<String> VARCHAR(255), DE_citizenshipStatus<String> VARCHAR(255), DE_alienRegCardNumber<String> VARCHAR(255), DE_race<String> VARCHAR(255))\ 0&quot;
SDWORD -3

DIAG [37000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition. (-3553)

Information in the Crystal Decisions Knowledgebase indicates that field names may not contain &quot;special&quot; characters. It is my belief that the underscore character in the &quot;DE_&quot; that is prepended to the field name or the &quot;<&quot; or &quot;>&quot; in the &quot;<string>&quot; appended at the end of each field name may be the source(s) of the error.

Is this a correct assumption? If so, is it possible to instruct Crystal Reports to omit &quot;DE_&quot; or &quot;<string>&quot; from the field names in the SQL defined to create the Microsoft Access table?

From the Crystal Decisions Knowledgebase I determined that the DE_ is generated because the data fields are coming from the Detail section of the report. Also, the Knowledgebase described a StripString function that can be downloaded from the Crystal Decisions web site, but I believe that this funtion acts upon the contents of a data field and not the name of the data field.

Thanks in advance for any help with this problem.

 

[Transcend]

Hi there,

the underscore _ is perfectly acceptable when using names in Access, however I think it is the <string> which is causing you grief. When you define a datatype in Access you define it as a varchar, char, number etc etc. The <string> part is extra information access wouldn't know what to do with.

Hope this helps!

Transcend

 

[jjfeigal]

I appreciate the response from Transcend to my original post. Transcend's response re-enforces the information from the Crystal Decisions Knowledgbase which I present as follows:

To resolve the error message, remove special characters within field names. When using ODBC, databases such as Microsoft Access, FoxPro and Dbase will not accept imported data if field names contain special characters.

Note: ======

A special character is any character other than a number or letter such as: %, $, ', (. -.

===========

For example, the following fields do not export successfully:

{Table.Customer's Last Years Sales}
{Table.Daily % of Monthly Totals}
{Table.Good Places To (Eat)}
{Table-Daily Places to Eat}

The field names being used by my report originated somewhere in the Lotus Notes/Crystal Reports interface. Does anyone know how to instruct Crystal Reports to omit &quot;<string>&quot; from each field name in the SQL CREATE statement for the new Microsoft Access database table being created by the export from Crystal Reports?

 

[jjfeigal]

I found the resolution to my problem.

Initially, my Lotus Domino database connection utilized the Native driver. The Lotus Native driver shows text fields as both strings and memos. The Lotus Domino database connection using the Lotus ODBC driver only shows text fields as strings and therefore does not need to define a Lotus view column twice and distinctly identify them as <string> or <memo>.

Once I changed the Crystal Reports report definition to use the Lotus ODBC driver, my report export to a Microsoft Access database table was successful. Each Microsoft Access database field name was still prepended with &quot;DE_&quot;, and for the time being I can live with that.

There also is another difference between the Lotus Native driver and the Lotus ODBC driver. The field names associated with the Lotus View columns provided by the Native driver are the Lotus View column names which are defined in the Lotus View column properties. The field names associated with the Lotus view columns provided by the ODBC driver are the actual Lotus database field names, and this is an issue when a column in the view is defined by a formula. In this situation the field name is given a cryptic name such as &quot;_3&quot;.

Hopefully, this information will help someone else down the road.