Chapter 30 Handling Large Text Files
Chapter 30 Handling Large Text Files
I
n an Access application, you frequently have to either import text files or link to them.
This is often because they are generated from an external database and the database
owner does not want you linking your application to their database either because of
security issues or because your application link may cause performance issues on the host
database.
Generally, it is easy enough to import or link to a text file, but what if the text file is too
big for Access to handle? I recently had to deal with a 4GB text file and Access completely
refused to cooperate with it. I could not even view the data in the file using the Access Text
File Wizard.
Trying to view the data in Notepad or WordPad was also a non-starter since the file took
too long to load.
This was where VBA came to the rescue. The first thing I needed to do was find out the
structure of the file, what the delimiters were, and what the data looked like, since I knew
nothing about the contents of the file.
The following code creates a file containing the first ten rows of the monster text file:
Sub CreateTenRows()
Dim temp As String
fnum = FreeFile
Open "C:\MyLargeFile.txt" For Input As fnum
fnum1 = FreeFile
Open "c:\FirstTenRows.txt" For Output As fnum1
307