TransferText - Type conversion errors

[Petrol]

I'm having difficulty with DoCmd.TransferText for a small comma-delimited file.
In one VBA procedure I Select a person's record from the People table, scramble the text fields for security, write it to a temporary delimited file and then email that file as an attachment to another user with their own copy of the database (same FE, different data in the BE but they need to add a copy of this particular record).

The recipient saves the file and passes it's location to a VBA procedure which executes CREATE TABLE to make an empty table with the same structure as the original, then uses TransferText to import the data to that table, unscrambles the text fields then appends it to the his People table.

At least that's the idea. In practice the import TransferText gets Error 3421, Data Type Conversion Error. Since the receiving table has the same structure as the sending table (I have checked this!) the only explanation I can think of is that the transfer file sometimes has null fields. Does anyone know if TransferText has problems handling empty fields?

The CREATE TABLE statement doesn't have any NOT NULL clauses, but it does have a lot of fields - 46 of them, including a mix of CHAR, INTEGER, BYTE, DATETIME formats, which is making it very hard for me to locate the problem. But surely TransferText should be able to import any files which it has exported?

[twinnyfo]

Petrol,

During your scrambling and unscrambling of the fields, does this generate any additional commas? If so, this could be the cause.

You could change the format to Tab-delimited, which is typically not a character that would be saved in a text field.

Don't know if this will hepp....

[Petrol]

Thanks, Twinnyfo, good thought. However, no. The number of commas is just right for the 46 fields. The scrambling is very basic - it just changes alphabetic to other alphabetic, numeric digits in character fields to other numeric digits, and leaves all non-character fields untouched.

However, I could try tab-delimited - at least they might be easier for me to decode when I look at a dump of the file. But the TransferText documentation at https://docs.microsoft.com/en-us/off...d.transfertext doesn't say anything about being able to change the delimiter ... how would I do it? Or is that the same as fixed-width? My character fields vary from 1 to 200 characters in width.

[twinnyfo]

Would it be possible to use a Spreadsheet? You would have to scramble and unscramble each cell (and I don't know how you are doing this, but that is immaterial if you are currently doing it field by field).

Then, using TransferSpreadsheet, you could attach that file as a table and use it as a table, unscramble the data and append it directly to your permanent table without the need for creating a new, temporary table.

[ADezii]

1. As I see it, there should be no need to change the Delimiter.
2. Do you have your HasFieldNames Argument set to True when it shouldn't be?
3. In the VBA Procedure that creates the Table, set all Fields to TEXT 255 then use TransferText. If all the Fields append correctly, then it should be a simple matter to go through each Field, modify it's characteristics, and find the Offender.
4. Should the above fail, in the VBA Procedure, after creating the Table, write VBA Code (instead of TransferText) to process the Delimited File Line-by-Line appending the Data to the appropriate Fields.
5. Should steps 1 and 2 fail, can you Upload the VBA Procedure that creates the Import Table as well as a Sample Comma-Delimited File void of any sensitive information?

[Petrol]

Oh, how embarrassing! I had confused a few 0-origin and 1-origin variables (counter vs field number) in the unscrambling loop, so some fields were unscrambled that shouldn't have been and vice versa. :-(

I still have all the fields set to TEXT(255) as per ADezii's suggestion, and will switch them back to their proper types tomorrow and advise, but I suspect that was the problem.

[Petrol]

Sure enough, it now works with some fields defined as INTEGER, DATETIME etc.
It was ADezii's suggestion to convert the fields to TEXT on input that put me on the right track - instead of data type conversion errors I started getting Overflow errors (Error 6), and investigating that led to the discovery of the mismatched unscrambling of fields. Thank you both for your help and suggestions.

Interestingly, here's a warning for anyone else using TransferText in this way: Even though the table that was exported and the table that is imported have exactly the same specification, if the data exported has text fields that do not take the full defined width of the field it will be exported as its true length, but on import TransferText fills out the field with blanks; so the data imported does not match that exported ("This " is not the same as "This"). So you need to RTrim() the received text fields.

[ADezii]

Glad you got it worked out, good luck with your Project.