import spec isn't working right

[NJudson]

I'm using Access2k I created an import specification that isn't working right. I'm trying to import a tab-delimited text file and write over an existing table. What is happening is it's appending the data to the existing table instead of writing over the table. When I created the import specification through the import wizard I selected it to import "in a New Table", yet its behaving like I selected to import it to an existing table.

Has anyone ever seen this before? I've deleted the import specs and the table and tried setting it up a few times but it keeps appending data. It may just be user error but I'd sure like to give Microsoft a piece of my mind right now!! Thank you for any help on this.

 

[bjackson]

can you empty the table before importing the text file or is the text file meant to only overwrite certain data

 

[boblarson]

The import specification does NOT have anything to do with whether or not the table is overwritten or appended. It is in the code that you use in conjunction with the Import Spec.

In the code
DoCmd.TransferText acImportDelim, "MyImportSpecName", "MyTableNameWhereToPutText", "TheFilePathAndName", True

The part of "MyTableNameWhereToPutText" is the table that it is going into. If the table exists, it will append. If the table does not exist, it will create it anew.

So, if you want it to start fresh each time, you must do one of several options.

If you don't need the data that is in the existing table, you can either put in code to delete the table before importing, or you can run a delete query to delete the contents before importing.

If you want to keep the data and have a different table name, you can run through the tables collection and find out how many tables exist with the part of the name that you want as a base (use the Instr function) and then you can increment based on that as adding 1 to the table name (use the left/right and instr functions to build that).

 

[NJudson]

thank you both for the replies. I don't know why I was thinking that the import spec would write over the existing data. Cough..Cough...Hack...Ohhhh, I think I'm catching SARS. Thanks again.