Linking to Delimited Text File

[jw5107]

Below is code that I'm working with that creates a link to a text file. The text file I'm working with is delimited with a ";". The first row of the text file is the field names and their delimited with a ";".
When I run the below code, it puts the entire text file into 1 field in the designated table.. I cannot figure out how to get this code so it shows the data the right way....
Any suggestions or examples...??
Thanks in advance,
jw5107

Sub LinkYTDRevCyclesTxtFile()
On Error GoTo Err_LinkYTDRevCyclesTxtFile
Dim Directory As String

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb
Dim filename
filename = "YTDFltHist.txt"
Directory = GetPath(db.Name)
db.TableDefs.Delete "YTDFleetUtilizationMain"

Set tbl = db.CreateTableDef("YTDFleetUtilizationMain")
tbl.Connect = "Text;DATABASE=" & Directory & "; FMT=delimited;HDR=YES;IMEX=2"
tbl.SourceTableName = "YTDFltHist.txt"
db.TableDefs.Append tbl

db.Close

Exit_LinkYTDRevCyclesTxtFile:
Exit Sub

Err_LinkYTDRevCyclesTxtFile:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_LinkYTDRevCyclesTxtFile
End If

End Sub

 

[Remou]

I have not tried this code, but it seems very relevant to you:

Link text file via code
thread705-865710

 

[jw5107]

Remou,

Any other suggestions..... Are we sure that the code I'm working with can't link to a delimited(";") text file, that has a header, and not text qualifier....??
I have tried the code from the tread you posted, but its lengthy and creatin' more havoc tryin' to get IT to work...

Appreciate the help..!!
jw5107

 

[Remou]

You have a unusual delimiter (the only 'usual' delimiter for Access is a comma) and no specification, so it is not going to work. Attach the table manually, choose the Advanced button and save the Specification as, say, YTDFltHist Link Specification. You can then use this:

[tt]DoCmd.TransferText acLinkDelim, "YTDFltHist Link Specification", "YTDFleetUtilizationMain", Directory & filename, True[/tt]