Applecore Pages on Microsoft Access

File I/O Using VBA

Although Access has various Wizards to assist you in importing data from text files, there are times when these are not suitable, due to the way that the data is laid out in the file. In cases like this, you may only be able to import them into Access by using VBA File I/O functions. On this page I list the functions used, and any notes about their useage. You will be able to find examples of the functions in the Access Help.

OpenFreeFile
EOF 
InputInput #
Line Input 
PrintWrite
CloseReset

As well as these functions, you may also find that you will need to use some of the VBA string handling functions, such as InStr, Left, Mid, Right and Trim, to parse the data as required.

There is an example of some of these functions on this page being used to read data in from the cdplayer.ini file.

Open
This function is used to open a file for either input or output, depending on the exact syntax used.

Top

FreeFile
You use this Access function to obtain the next valid number for use when performing file operations. Note that after allocating a number in this way, you must open or close a file before using it again, otherwise the same number is generated. The first example below will fail, but the second will work:

    intInFile = FreeFile
    intOutFile = FreeFile
    Open "C:\in.txt" For Input As intInFile
    Open "C:\out.txt" For Output As intOutFile

    intInFile = FreeFile
    Open "C:\in.txt" For Input As intInFile
    intOutFile = FreeFile
    Open "C:\out.txt" For Output As intOutFile

Top

EOF
This function indicates when you have reached the end of a file, and as such it is normally used as the condition for a Do loop.

Top

Input #
This function will read data from a file that has been written to using the Write function. As this means that text will normally be delimited with quotes and similar, it is not of much use for general text importing.

Top

Input
The Input function will import the number of specified characters at a time into Access. This is probably of most use when you have a record of fixed width (i.e. you have set the Len argument of the Open statement). One trick with this function is to read the whole file into Access in one go - this is often faster than reading it line by line or character by character, and then separating it:

Sub sImportAll()
    On Error GoTo E_Handle
    Dim strImport As String
    Dim lngChars As Long
    Dim intFile As Integer
    intFile = FreeFile
    Open "C:\test.txt" For Input As intFile
    lngChars = LOF(intFile)
    strImport = Input(lngChars, intFile)
sExit:
    On Error Resume Next
    Reset
    Exit Sub
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Top

Line Input
This function will input a line at a time from the text file. You will often read this into a temporary variable, and then split it using the string handling functions (Left, Mid, etc.) as required.

Top

Print
This function is used to write data to an output file. I would normally use this function, rather than Write, as it does not wrap data in special characters, making the exported more universally available.

Top

Write
This function is used to write data to a file, but as it 'wraps' data with special characters (such as double quotes) it is not that useful as far as exporting data for use in other programs is concerned. Also be aware that it ignores locale settings, such as the decimal separator.

Top

Close
You use this function to close a file that has been opened using the Open statement. It is important to close files that you have opened, either using this function or Reset, otherwise they are 'locked'.

Top

Reset
You use this function to reset all files that have been opened - therefore it should be used with care, as you may find yourself closing a file that has been opened in another procedure. Unless I know that I have a file open in another procedure, I will normally have this statement in the exit point for a procedure, otherwise I will use the Close statement on each individual file.

Top

 


HOME | NEW | TABLES | QUERIES | FORMS | REPORTS | GENERAL | API | DOWNLOADS | TUTORIAL | RESOURCES
E-MAIL
Copyright & Disclaimer

 

Last modified at 06/06/2006 14:58:05