Access97 - docmd.Transfertext - (Jet?) error message

[KenReay]

Hi

In an application (not witten by me!) to reformat data taken from one system to a format suitable to import into another system I have the line of code:



DoCmd.TransferText acExportDelim, "", "qGLAllSQLLoaderEdgar", strPath & "egl.csv", False, ""

The SQL behind the Query "qGLAllSQLLoaderEdgar" is a s follows

SELECT glall.gtr_run_group, glall.gtr_seq_number, glall.gtr_company, glall.gtr_old_company, glall.gtr_old_acct_nbr, IIf(IsNull([gtr_source_code])," ",[gtr_source_code]) AS GtrSourceCode, Format(CDate(Format([gtr_date],"00\/00\/0000&quot),"dd-mmm-yy&quot AS GTRDate, Nz([gtr_reference]," &quot AS gtrReference, Nz([gtr_description]," &quot AS gtrDescription, Nz([gtr_currency_code]," &quot AS gtrCurrencyCode, Nz([gtr_units_amount],0) AS gtrUnitAmount, Nz([gtr_tran_amount],0) AS gtrTranAmount, Nz([gtr_base_amount],0) AS gtrBaseAmount, Nz([gtr_baserate],0) AS gtrBaserate, Nz([gtr_system]," &quot AS gtrSystem, Nz([gtr_program_code]," &quot AS gtrProgramCode, Nz([gtr_auto_rev]," &quot AS gtrAutoRev, Format(CDate(Format([gtr_posting_date],"00\/00\/0000&quot),"dd-mmm-yy&quot AS GTRPostingDate, Nz([gtr_activity]," &quot AS gtrActivity, Nz([gtr_acct_category]," &quot AS gtrAcctCategory, Nz([gtr_document_nbr]," &quot AS gtrDocumentNbr, Nz([gtr_to_base_amt],0) AS gtrToBaseAmt, IIf(IsNull([gtr_effect_date]),"01-Jan-1700",Format(CDate(Left([gtr_effect_date],2) & "/" & Mid([gtr_effect_date],3,2) & "/" & Mid([gtr_effect_date],5)))) AS gtrEffectDate, Nz([gtr_jrnl_book_nbr]," &quot AS gtrJrnlBookNbr, Nz([gtr_mx_value1],0) AS gtrMXValue1, Nz([gtr_mx_value2],0) AS gtrMXValue2, Nz([gtr_mx_value3],0) AS gtrMXValue3, Nz([gtr_jbk_seq_nbr],0) AS gtrJbkSeqNbr, Nz([gtr_negative_adj],0) AS gtrNegativeAdj, Nz([spare_field]," &quot AS SegmentBlocl, Nz([spare_field1],"0&quot AS rptAmount1, Nz([spare_field2],"0&quot AS rptRate1, Nz([spare_field3],"0&quot AS rptND1, Nz([spare_field4],"0&quot AS rptAmount2, Nz([spare_field5],"0&quot AS rptRate2, Nz([spare_field6],"0&quot AS rptND2, Nz([spare_field7]," &quot AS SpareField7
FROM glall;

The table GLAll has 27755 rows.

I get the message:

<Application name> was unable to append all the data to the table. The contents of fields in 3964 record(s) were deleted, and 0 record(s) were lost due to key violations.
* if data was deleted, the data you pasted or imported doesn't match the field data type or the fieldsize property in the destination table.

* if rrecords were lost, either the records you posted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship between tables.

Do you want to proceed anyway?

Yes No Cancel


The above is not a trappable Access Error.

If I simply select Yes to prceed, it does the task and all 26755 rows are written to the output file.

Has anyone come accross this error message, and/or can they offer an explanation / solution

Thanks



Ken Reay Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[wemeier]

I can't believe you are getting the error message that you described on the EXPORT of the data to an ASCII delimited file. The error message seems more like one you would get when you IMPORT the ASCII delimited file into another table.

This error message occurs when data in one or more fields violates keys in the new table. These keys could be unique keys (in which case the data in one or more fields is not absolutely unique) or keys that must be non-blank (i.e blanks or nulls not allowed).

If the error really is being generated on the TransferText method, the only other possibility is if the query being called in the TransferText method is an Append or MakeTable query that is being executed and causing the key violations.

 

[KenReay]

Hi wemeier

I aggress with evertthing you say but:

The SQL is in my post, you can see for yourself it is a SELECT query

That is exectly the error message I am getting.

I too am toatlly puzzled by it, hence the posting
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK