Access VBA - DoCmd.TransferText error "The INSERT INTO statement contains the following unknown field name..."

0
votes

I've been asked to do some updates on an Access 2007 VBA application. My experience is mostly with Excel VBA, not much with Access.

In one place, we're adding some additional fields. One of the tables is exported to an Excel workbook like this:

DoCmd.TransferText acExportDelim, "(text file specification)", "(source table name)", (path to CSV file to be created), True

After adding the new fields to the source table, I get the error:

"The INSERT INTO statement contains the following unknown field name: '(field name)'. Make sure you have typed the name correctly, and try the operation again."

After reading what I could find on this, I understand that the problem is that the "text file specification" does not have the new fields. And further, that I can't edit the text file specification but instead must create a new one.

Okay, I can do that. But how do I know everything the previous text file specification was doing? I can see the result, but that doesn't necessarily tell me everything it did.

Is it possible to see, even if read only, that text file specification? If I go (in Access 2007) to External Data tab > Export section > Saved Exports, and go to the Saved Exports tab, I see a single item there that may be the one, though it doesn't seem to have the same name there as what I see in the DoCmd.TransferText line. However I don't see any way of viewing what it does; just a name for it, a description (which is blank), and a path (which is not currently valid).

Is there a better way than playing guessing games about what the old text file specification does?

Any suggestions?

Thanks,

Greg

1
ms-accessms-access-2007vba

1 Answers

2
votes

In Access, right-click the table you are exporting and choose Export > Text File. On the first page of the export wizard, specify the destination file name for this test export and then click "OK".

In the dialog box that follows (where you can choose "Delimited" or "Fixed Width"), click the "Advanced..." button in the lower-left corner. That will take you into the Export Specification dialog. Click the "Specs..." button and you will see a list of the saved export specifications. Select the one you want and click "Open".

After you make your changes, remember to click the "Save As..." button and save the new version with the same name as the old one. (When asked if you want to overwrite the existing specification, say "Yes".)