Auto Import Table with Primary Key Field

[tristap]

I have set up an automatic import with specification for a fixed width test file using the TransferText method, however I am having problems with a primary key field.

When I manually import, Access assigns a Primary Key field giving each record a unique ID number, however when this process is automated using TransferText method, the Primary Key field is not established.

Is there any way of including the Primary Key column using the Transferautomatically without having to manually add the column to the text file?

 

[Remou]

I think you must create the primary key after you import the table. Here is one possibility:

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim f As DAO.Field
Dim strTableName
Set db = CurrentDb
strTableName = "tblTable"
DoCmd.TransferText acImportDelim, , strTableName, "C:\Import.txt"

Set td = db.TableDefs(strTableName)
Set f = td.CreateField("CustID")
f.Type = dbLong
f.Attributes = dbAutoIncrField
td.Fields.Append f

(Mainly taken from:

http://support.microsoft.com/?kbid=304418)

You could also use Alter table, for example:

strSQL = "Alter TABLE tblTable Add Column CustID COUNTER ;"
DoCmd.RunSQL strSQL

 

[tristap]

Remou,

Used code provided and this has fixed my issue.

Thank you so much for the assist!