Automating Microsoft Access with VBA

Writing to a file is the process of adding values to that file. When opening the file in Input, Output, or Append mode, use the Print # or Write # statement. For Random and Binary mode, use the Put statement to write to the file.

The Write # statement takes the form

Write #filenumber[, outputlist]

where outputlist is one or more comma-delimited variables or literal values you want to add to the file. Separate these values with a comma, space, or semicolon. If you omit outputlist and include a comma character after filenumber, the statement adds a blank line to the file.

There are a number of behaviors that might cause you trouble if you don't know about them:

• All numeric data is written with the period decimal separator.

• Boolean values are written as #TRUE# or #FALSE#.

• Dates are written using the universal date format.

• If outputlist is empty, nothing is written to the file.

• If outputlist is Null, the value #NULL# is written.

• Error values are written as #ERROR errorcode#.

The following procedure enables you to add a new line before retrieving the file's entire contents:

Sub InputAndWrite(fil As String, _ id As Long, fn As String, ln As String) 'Write to fil and then print data 'in Immediate window. Dim hFile As Long Dim strID As String Dim strFN As String Dim strLN As String hFile = FreeFile Open fil For Append Access Write As hFile Write #hFile, id, fn, ln Close hFile hFile = FreeFile Open fil For Input Access Read Shared As hFile Do Until EOF(hFile) Input #hFile, strID, strFN, strLN Debug.Print strID & vbTab & strFN & " " & strLN Loop Close hFile End Sub

Run the following statement in the Immediate window to add employee 15, Susan Harkins, to the employees.txt file:

InputAndWrite "path\employees.txt", 15, "Susan", "Harkins"

The results in Figure 20.4 show the newly added line for employee 15. The Write # statement uses the passed arguments id, fn, and ln which supply the new data. Notice that the Open statement is a little different for writing. Specifically, this example uses the Append mode and the Write access setting. To create a new file (that will overwrite an existing file) with the passed data values, use Output mode instead of Append.