The OPEN StatementThere are several methods to read a text file into an Access database. VBA has a built-in OPEN statement, which enables input or output access to an external file. Here's an example VBA code to initialize a text file for input:
Open "C:\FolderName\FileName.txt" For Input As #1The above code creates an object (#1) with a reference to the text file, which then allows its contents to be read in code. For example, the following code reads a line of text from the file into a variable:
Line Input #1, VariableNameAfter we are done reading its contents, we need to close the connection to the file with the following:
Close #1Here's an example code snippet to read an entire text file into memory:
Dim intFile As Integer Dim strFile As String Dim strText As String Dim strInput As String intFile = FreeFile() strFile = "C:\FolderName\FileName.txt" Open strFile For Input As #intFile Do While Not EOF(#intFile) Line Input #intfile, strInput strText = strText & strInput Loop Close #intFilePlease notice the use of the FreeFile() function to retrieve the next available file number rather than assigning a specific file number with the OPEN statement. It avoids possible mistakes of referring to the wrong file in case you are trying to manipulate multiple files at the same time.
The File System ObjectMy preferred method to read a text file into Access is to use FSO (File System Object). It adds an external reference (overhead) to the application, but I think the process will run a bit faster, and the code will also be a bit cleaner.
For instance, compare the following function, using FSO, to the one above, using the OPEN statement:
Public Function ReadTextFile(strFile As String) As String '3/10/2016 'http://accessmvp.com/thedbguy Dim fso As Object Dim objFile As Object Set fso = CreateObject("Scripting.FileSystemObject") If fso.FileExists(strFile) Then Set objFile = fso.OpenTextFile(strFile, 1) '1=ForReading, 8=ForAppending ReadTextFile = objFile.ReadAll End If Set objFile = Nothing Set fso = Nothing End FunctionThe above function uses "late binding," so a reference to the File System Object is not necessary. However, you can use "early binding" if you prefer.
I hope you find the above methods useful. Please do not hesitate to let me know which method you prefer and why (or why not the other). Cheers!