How to write into a text file using MS Access VBA code and importing that text file into Access?

I am trying to write into an existing text file. The (text.txt) file contains some numbers such as...


123335
125455
145575
254875

I am trying to import that data into a table called "Invoice".
While importing this data from text file, I need to write "Field Name" on top of the text file and also change the data type to "text".

Thanks in advance.
awarraicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
better to create another text file with the field name

Dim s
Open CurrentProject.Path & "\mytext.txt" For Input As #1
Open CurrentProject.Path & "\mytext2.txt" For Output As #2
Print #2, "FieldName"

Do Until EOF(1)
Line Input #1, s
Print #2, s
Loop
Close #1
Close #2
0
Máté FarkasDatabase Developer and AdministratorCommented:
For example (you have to use Microsoft Scripting Runtime reference):
Sub Import()
Dim Fs As New Scripting.FileSystemObject
Dim Text As Scripting.TextStream
Dim S As String

Set Text = Fs.OpenTextFile("C:\text.txt")
S = Text.ReadAll
S = "Field Name" + vbCrLf + S
Text.Close
Set Text = Fs.CreateTextFile("C:\text.txt", True)
Text.Write S
Text.Close
End Sub

Open in new window

0
datAdrenalineCommented:
Assuming that you have all the values in a single string (CSV with the separator character being a vbCrLf), you can use code like this to save a string to a text file:

Public Sub SaveToTextFile(strString As String, strFilename As String)
   
    Dim intChannel As Integer
   
    'Close any open files
    For intChannel = 1 To 511
        Close #intChannel
    Next intChannel
   
    'Get a filenumber (aka: communication channel) to send information to the file
    intChannel = FreeFile
   
    'Open/Create/Overwrite the file, send the data to it, then close the file
    Open strFilename For Output Access Write As #intChannel
   
    'Write to the text file, then close it
    Print #intChannel, strString
    Close #intChannel
   
End Sub

Example:
SaveToTextFile "MyColumnHeader" & vbCrLf & "Hello" & vbCrLf & "There", "C:\Temp\MyFile.txt"

Will yeild a text file in C:\Temp named MyFile.txt (note: if it exists, it WILL be overwritten) and the contents of that file will be:

MyColumnHeader
Hello
There

-----

To import the data, with the assumption that you will be CREATING new records into an existing table, you can do something like this:

Public Sub ImportFile(strPath As String, strName As String)
   
    Dim strSQL As String
   
    strSQL = "INSERT INTO tblDestinationTableName (FieldNameForData)" & _
              " FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437" & _
                    ";DATABASE=" & strPath & "].[" & strName & "] AS vMyData;"
   
    CurrentDb.Execute strSQL, dbFailOnError
   
End Sub

Example:
ImportFile "C:\Temp", "MyFile.txt"

Will yeild two records in the destination table with "Hello" and "There" in the "FieldNameForData" field.

If my assumptions are incorrect, please let me know and I will help modify the code accordingly.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

datAdrenalineCommented:
... Oh ...

With the import method I have shown, you really DON'T need the field name as the first row of the text file.  Also, I messed up the strSQL statement ...

Should be:
strSQL = "INSERT INTO tblDestinationTableName (FieldNameForData)" & _
              " SELECT MyColumnHeader FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437" & _
                    ";DATABASE=" & strPath & "].[" & strName & "] AS vMyData;"

Or... if you choose not to use a header ....
strSQL = "INSERT INTO tblDestinationTableName (FieldNameForData)" & _
              " SELECT F1 FROM [Text;FMT=Delimited;HDR=NO;CharacterSet=437" & _
                    ";DATABASE=" & strPath & "].[" & strName & "] AS vMyData;"
0
awarraicAuthor Commented:
I tried the first code, it's giving an error, "Bad file or Number", Run time error '52'.
Here's what I have exactly in my code:

Private Sub Command0_Click()

Dim s
Open CurrentProject.Path & "C:\test1.txt" For Input As #1
Open CurrentProject.Path & "C:\test2.txt" For Output As #2
Print #2, "Invoice"

Do Until EOF(1)
Line Input #1, s
Print #2, s
Loop
Close #1
Close #2


End Sub
0
Rey Obrero (Capricorn1)Commented:
try this, the path to your file is not correct

Private Sub Command0_Click()

Dim s
Open "C:\test1.txt" For Input As #1
Open "C:\test2.txt" For Output As #2
Print #2, "Invoice"

Do Until EOF(1)
Line Input #1, s
Print #2, s
Loop
Close #1
Close #2


End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awarraicAuthor Commented:
Thanks for the prompt respond.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.