Link text file - change field names 1

[jcw5107]

Below is code I am working with...
Everything works just fine except for the field names of the newly created linked table. Since there are no "headers" in the text file, the code just defaults every field name to "F1", "F2", "F3", "F4", etc..
I need to have the field names be something different...
How can I manipulate the code below to show a actual field name instead of "F1", "F2", "F3", "F4", etc..??
Any suggestions or examples..??
Thanks in advance..!!
jcw5107

Sub LinkAMPTxtFile()
On Error GoTo Err_LinkAMPTxtFile
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb
Dim filename
filename = "SCHEDMNT.TXT"
db.TableDefs.Delete "SCHEDMNT"
Set tbl = db.CreateTableDef("SCHEDMNT")
tbl.Connect = "Text;DATABASE=" & "D:/UPSData" & "; FMT=delimited;HDR=NO;IMEX=2"
tbl.SourceTableName = "SCHEDMNT.txt"
db.TableDefs.Append tbl
db.Close
Exit_LinkAMPTxtFile:
Exit Sub
Err_LinkAMPTxtFile:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
Else
DisplayMessage Err.Description
Resume Exit_LinkAMPTxtFile
End If
End Sub

 

[Remou]

You can create a query based in the text file:

[tt]Select F1 As ID, F2 As AnotherName From SCHEDMNT[/tt]

 

[jcw5107]

Remou,

Thanks for the fast response....!!
I may rethink what it is I'm tryin' to do...!!
But I'm still curious as to how you would change field names in the code I posted....???

Thanks for the prompt help..!!
jcw5107

 

[Remou]

I do not think it can be done with the code you show, hence the suggestion of a query, an alternative would be to create a specification and then use TransferText. For example:

[tt]strFile = "C:\Docs\SCHEDMNT.TXT"
strSpec = "SCHEDMNT Link Specification"
DoCmd.TransferText acLinkDelimited, strSpec, "SCHEDMNT", strFile, False[/tt]

It would also be possible to insert a line with header information into the text file.

 

[jcw5107]

Remou,

Example on how I could "insert a line with header info"..???
That may be what I need...
Thanks again...
jcw5107

 

[Remou]

Very roughly:

Dim fs, f1, f2, blnOverwrite
Dim strPath, strFile, strNewFile
Dim strData

Const ForReading = 1
blnOverwrite = False 'or True

strPath = "C:\Documents and Settings\Fionnuala\My Documents\"
strFile = "hh.txt"
strNewFile = "NewFile.txt"

Set fs = CreateObject("Scripting.FileSystemObject")

Set f1 = fs.CreateTextFile(strPath & strNewFile, blnOverwrite)
f1.WriteLine """ID"",""FieldA"""

Set f2 = fs.OpenTextFile(strPath & strFile, ForReading)
strData = f2.ReadAll

f1.WriteLine strData

f1.Close
f2.Close

Set f1 = Nothing
Set f2 = Nothing
Set fs = Nothing

DoCmd.TransferText acLinkDelim, , "NewTable", strNewFile, True

 

[jcw5107]

Remou,
Thank you very much..!! I was able to take what you provided and make it work..!!!
Just awesome..!! Thanks again..!!
Star for you...!!

jcw5107