![[bugeyed] [bugeyed] [bugeyed]](/data/assets/smilies/bugeyed.gif)
You're right, there is another table I overlooked that gets created when you use the spec wizard (MSysIMEXColumns). If your import file has header names, you must either know them in advance or create a parser that will extract them into variables you can pass to the 'AddSpecs' function.
In the following example, I imported a table with only 4 columns, with headings of 'Hello' and 'World' and 'Import' and 'Test.' If you don't create the heading names and put them in the MSysIMEXColumns table your import table will get default headings starting with F1, F2, etc. Here is an updated script:
Sub TransferIt()
Call AddSpecs("Hello", "World", "Import", "Test"

DoCmd.TransferText acImportDelim, "ImportTest Import Specification", "ImportTemp", "C:\ImportTest2.txt", True
End Sub
Function AddSpecs(ParamArray ColumnNames() As Variant) As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim idx As DAO.index
Dim idx2 As DAO.index
Dim i As Integer
Set db = CurrentDb
' Make the spec table.
Set tdf = db.CreateTableDef("MSysIMEXSpecs"
With tdf
.Fields.Append .CreateField("DateDelim", dbText, 2)
.Fields.Append .CreateField("DateFourDigitYear", dbBoolean)
.Fields.Append .CreateField("DateLeadingZeros", dbBoolean)
.Fields.Append .CreateField("DateOrder", dbInteger)
.Fields.Append .CreateField("DecimalPoint", dbText, 2)
.Fields.Append .CreateField("FieldSeparator", dbText, 2)
.Fields.Append .CreateField("FileType", dbInteger)
.Fields.Append .CreateField("SpecID", dbLong)
.Fields("SpecID"

.Attributes = dbAutoIncrField ' Autonumber
.Fields.Append .CreateField("SpecName", dbText, 64)
.Fields.Append .CreateField("SpecType", dbByte)
.Fields.Append .CreateField("StartRow", dbLong)
.Fields.Append .CreateField("TextDelim", dbText, 2)
.Fields.Append .CreateField("TimeDelim", dbText, 2)
db.TableDefs.Append tdf
db.TableDefs.Refresh
Set idx = db.TableDefs(tdf.Name).CreateIndex
With idx
.Name = "PrimaryKey"
.Fields.Append .CreateField("SpecName"

.Unique = True
.Primary = True
End With
.Indexes.Append idx
.Indexes.Refresh
End With
Set rst = db.OpenRecordset(tdf.Name)
With rst
.AddNew
.Fields("DateDelim"

= "/"
.Fields("DateFourDigitYear"

= True
.Fields("DateLeadingZeros"

= False
.Fields("DateOrder"

= 2
.Fields("DecimalPoint"

= "."
.Fields("FieldSeparator"

= " " ' space
.Fields("FileType"

= 437 ' Excel 5
.Fields("SpecName"

= "ImportTest Import Specification"
.Fields("SpecType"

= 1
.Fields("StartRow"

= 1 ' 0 for 'No Header Row'
.Fields("TextDelim"

= Chr(34)
.Fields("TimeDelim"

= ":"
.Update
End With
' Make the columns table
Set tdf2 = db.CreateTableDef("MSysIMEXColumns"
With tdf2
.Fields.Append .CreateField("Attributes", dbLong)
.Fields.Append .CreateField("DataType", dbInteger)
.Fields.Append .CreateField("FieldName", dbText, 64)
.Fields.Append .CreateField("IndexType", dbByte)
.Fields.Append .CreateField("SkipColumn", dbBoolean)
.Fields.Append .CreateField("SpecID", dbLong)
.Fields.Append .CreateField("Start", dbInteger)
.Fields.Append .CreateField("Width", dbInteger)
db.TableDefs.Append tdf2
db.TableDefs.Refresh
Set idx2 = db.TableDefs(tdf2.Name).CreateIndex
With idx2
.Name = "PrimaryKey"
.Fields.Append .CreateField("FieldName"

.Fields.Append .CreateField("SpecID"

.Unique = True
.Primary = True
End With
.Indexes.Append idx2
.Indexes.Refresh
End With
Set rst = db.OpenRecordset(tdf2.Name)
For i = 0 To UBound(ColumnNames)
With rst
.AddNew
.Fields("Attributes"

= 0
.Fields("DataType"

= dbText
.Fields("FieldName"

= ColumnNames(i)
.Fields("IndexType"

= 0
.Fields("SkipColumn"

= 0
.Fields("SpecID"

= 1 ' The spec table P.K.
.Fields("Start"

= i
.Fields("Width"

= Null
.Update
End With
Next i
AddSpecs = True
ExitHere:
Set tdf = Nothing
Set db = Nothing
Set idx = Nothing
Set rst = Nothing
Exit Function
ErrHandler:
MsgBox "Error: " & Err & " - " & Err.Description
Resume ExitHere
End Function
VBSlammer