Unable to create a linked table to a .txt file for input to Access

[IvanSoto]

Anyone can readily establish a linked table to a flat (e.g., .txt) file manually. The end result is a table that shows in Access marked with a sort of Notepad icon and an arrow showing it's for input. I've experimented a lot with code similar to the one I show below. In this forum, I've read some threads about that. However, all the threads I've found don't really discuss a real linked table, such as one can create manually. Here's the basic code:
======== VBA code =========
Private Sub testLinkedTableCreationForTextFiles()
Dim theFullFileName As String
theFullFileName = "C:\Users\IvanSoto\Documents\Teach\vba\importee.txt"
Dim theTableName As String
theTableName = "importeeTable"
Dim theNameOfTheImportSpec As String
theNameOfTheImportSpec = "ImporteeFirstRowHeaders"
Dim theFields(1 To 4) As String
theFields(1) = "firstField"
theFields(2) = "secondField"
theFields(3) = "thirdField"
theFields(4) = "fourthField"
makeLinkedTableToTextFile theTableName, theFullFileName, theNameOfTheImportSpec, theFields
End Sub

Public Sub makeLinkedTableToTextFile(ByVal tableName As String, ByVal fullNameOfTextFile As String, _
ByVal nameOfImportSpec As String, _
ByRef fieldNamesTextFields() As String)

Dim thisDB As DAO.Database
Dim tdfLinked As DAO.TableDef

Dim theConnect As String
Dim justTheDirectoryPath As String
Dim justTheFileName As String
Dim backSlashLoc As Integer
backSlashLoc = VBA.InStrRev(fullNameOfTextFile, "\", -1, VBA.VbCompareMethod.vbBinaryCompare)
Dim iX As Integer

Set thisDB = Access.CurrentDb()

If backSlashLoc = 0 Then
justTheDirectoryPath = ""
justTheFileName = fullNameOfTextFile
Else
justTheDirectoryPath = VBA.Strings.Left(fullNameOfTextFile, backSlashLoc - 1)
justTheFileName = VBA.Strings.Mid(fullNameOfTextFile, backSlashLoc + 1, backSlashLoc - 1)
End If

On Error Resume Next
thisDB.TableDefs.Delete tableName
On Error GoTo 0
theConnect = "Text;DSN=" & nameOfImportSpec & _
";FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;ACCDB=YES;DATABASE=" & _
justTheDirectoryPath & ";TABLE=" & VBA.Replace(justTheFileName, ".", "#")
' attributes value observed in a linked table elsewhere is 1073741824, the following rejects it as second argument
Set tdfLinked = thisDB.CreateTableDef(tableName, , justTheFileName, theConnect)
'Debug.Print VBA.VarType(tdfLinked.Attributes), VBA.TypeName(tdfLinked.Attributes) ' this reports "3 Long"

For iX = LBound(fieldNamesTextFields) To UBound(fieldNamesTextFields)
tdfLinked.Fields.Append tdfLinked.CreateField(fieldNamesTextFields(iX), _
DAO.DataTypeEnum.dbText, 255)
Next iX
tdfLinked.Fields.Refresh
thisDB.TableDefs.Append tdfLinked
thisDB.TableDefs.Refresh
Access.Application.RefreshDatabaseWindow
End Sub
======== End VBA code =========

I'm trying this method because the DoCmd Transfer Text method fails very nastily sometimes with the run-time error 3709, "The search key was not found in any record," for which, as I far as I can determine after much Web searching, is of a mysterious sort and really frustrating to end users. I've solved all related technical challenges (for example creating the import spec in VBA), but creating the genuine linked tables remains a challenge so far insurmountable.

Final note: Every linked table I create manually has the attribute value 1073741824 (bit 1 on, all other off, counting 0, 1, ..., 31 in the Long field which attribute is). Ordinary Access tables have 0 there and Access hidden tables have other values. The rub is that the attributes values that turn on bits from 0 to 13 are rejected as invalid.

Insights will be very much appreciated as long as they bear on creating genuine linked tables , not on offering alternative importing methods .

 

[IvanSoto]

I'm able to answer my own question! Here's the difference from above:
(1) Specify the properties Connect and SourceTableName AFTER creating the TableDef;
(2) DO NOT specify any fields -- these will be defined into the TableDef Object when the the source file name is opened, with the guidance of the import specification.

Now the magic attribute 1073741824 turns up after table creation, the icon for the table is as I had been pursuing (the same as linked tables create manually). Most importantly, the behavior of the table is exactly as desired!

Set tdfLinked = thisDB.CreateTableDef(tableName)
tdfLinked.Connect = theConnect
tdfLinked.SourceTableName = justTheFileName
' MUST NOT bother with fields!
thisDB.TableDefs.Append tdfLinked
thisDB.TableDefs.Refresh

Access.Application.RefreshDatabaseWindow
Debug.Print "Attributes of the Database after creation: " & thisDB.TableDefs(tableName).Attributes
Debug.Print "Connect string: " & thisDB.TableDefs(tableName).Connect
Debug.Print "Source file: " & thisDB.TableDefs(tableName).SourceTableName

'Victory at 1:53 AM on Tuesday 4 September 2012!
'Attributes of the Database after creation: 1073741824
'Connect string: Text;DSN=ImporteeFirstRowHeaders;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;ACCDB=YES;TABLE=importee#txt;DATABASE=C:\Users\IvanSoto\Documents\Teach\vba
'Source file: importee.txt