TransferText - Primary Key Lost

[Ross]

I have an import specification the lets Access create a primary key ("ID").
This is exactly what I want. If I use the specification "manually", it works
perfectly (into a NEW table).

When I use this specification using docmd.transfertext, I loose the ID field
(which I really need).

I can programmatically add the field back into the table and renumber it,
but it's extra work.

Ideas are solicitated and appreciated.

Ross

 

[John Nurick]

Hi Ross,

I have an import specification the lets Access create a primary key ("ID").
This is exactly what I want. If I use the specification "manually", it works
perfectly (into a NEW table).

The import specification doesn't allow you to define fields that don't
exist in the text file.

When I use this specification using docmd.transfertext, I loose the ID field
(which I really need).

Adding the autonumbered "ID" field is a feature of the import wizard,
not of TransferText.

I can programmatically add the field back into the table and renumber it,
but it's extra work.

If you have an import specification, it means you know the table
structure, so the simplest thing may be to explicitly create an empty
table first, including an autonumber PK field, and then append the data
to it. One way of doing this would be to create a "template" table
manually and then use DoCmd.CopyObject to create a copy.

Another is to build and execute a DDL query that creates the table from
scratch, e.g. this air code:

Dim strTableName As String
Dim strSQL As String

strTableName = "My_New_Table"
strSQL = "CREATE TABLE " & strtableName _
& " (ID AUTOINCREMENT CONSTRAINT pkID PRIMARY KEY, " _
& " FirstName VARCHAR(20), " _
& " LastName VARCHAR(20)) " _
& ";"
CurrentDb.Execute strSQL

 

[Ross]

Thanks John.

I ultimately appended the "ID" field to the imported text file access table
and then numbered / populated the field using code.