Microsoft Access VBA Macro Programming

For n = 1 To 10

Line Input #fnum, temp

Print #fnum1, temp

Next n

Close fnum

Close fnum1

End Sub

This code opened the 4GB file (MyLargeFile) for input so it could be read, and also

created an output file called FirstTenRows.

Using a For..Next loop, a row was read into the variable temp and then written out to the

output file. This iterated ten times and produced a sample file of manageable size that could

easily be viewed in Notepad.

On viewing the sample file, the deliminators were tilde characters (~) and there were also

single quote text qualifiers (‘). Access does not work well with tilde deliminators and the text

qualifiers are not actually necessary for the purpose of linking the text file into Access.

With a small file, the issue of text qualifiers would not matter, but with a 4GB file, the text

qualifiers take up a considerable amount of space.

The following code opens the big file and iterates through it changing tilde characters to

commas and removing single quote marks.

Sub GetMyBigFile()

Dim temp As String, temp1 As String

fnum = FreeFile

Open "C:\MyBigFile.txt" For Input As fnum

fnum1 = FreeFile

Open "c:\MySmallFile.txt" For Output As fnum1

Do While Not EOF(fnum)

Line Input #fnum, temp

temp1 = ""

For m = 1 To Len(temp)

If Mid(temp, m, 1) = "~" Then

temp1 = temp1 & ","

ElseIf Mid(temp, m, 1) = "'" Then

308 Microsoft Access 2010 VBA Macro Programming