Lose primary key when importing

[Richard]

I'm having trouble writing a macro that adds a primary key when importing
text data.
When I import the same data manually (File/Import ... ) Access adds a
primary key.
However, when I do the same import using a macro, no primary key is added.

Here is the macro I'm using to import the tab-delimited text data:

Sub ImportTabDelimitedFiles()

Dim strComdocs

' set strFERS to location of My Documents\CRREL-FERS

Dim oShell, oSpecialFolders
Set oShell = CreateObject("WScript.Shell")
Set oSpecialFolders = oShell.SpecialFolders

strComdocs = oSpecialFolders("MyDocuments") & "\Discoverer\ERDC_comdocs\"

DoCmd.TransferText acImportDelim, "ERDC_comdocs_current Import
Specification", _
"ERDC_comdocs_both_in", strComdocs & "ERDC_comdocs_hist.txt"





End Sub

 

[Douglas J. Steele]

AFAIK, there's no way to import and add the field using TransferText.

You either have to create the table first and then populate it, or else add
the field to the table after you import it.

 

[Richard]

Douglas,
I suspect you're right.
Easiest way for me to create blank table in correct format is to first
import a text file by hand (once), making sure Access adds the primary key,
and then use my macro to just empty table and add more data.

Do you know how to empty the table?

 

[Douglas J. Steele]

Use a Delete query:

CurrentDb.Execute "DELETE * FROM [NameOfTable]"