TransferText - Primary Key Lost

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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).
Click to expand...

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).
Click to expand...

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.
Click to expand...

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
 
Thanks John.

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