Populating Tables with Data

The major reason for creating tables is to populate them with data. One obvious way to satisfy this objective is to open a recordset on a table and then invoke the AddNew method for the recordset. Another approach for adding data to a table is to use SQL statements, such as INSERT INTO. Other attractive techniques also exist, but they rely on stored queries, which Chapter 4 examines.

This section provides an overview of the resources available for adding data to tables. The initial sample contrasts populating a table with data from a SQL statement and copying data from one table to another via recordset objects. Once you have data in a recordset, transferring it to another recordset is straightforward. Therefore, you can benefit from revisiting the discussion of recordsets in Chapter 1, particularly the review of the recordset Open method.

In this section, we'll examine creating read/write links with non-Access data sources, such as Microsoft Excel workbooks and Microsoft SQL Server databases. You can manage links programmatically either through an ADO Connection object or by creating a linked table in a Database window with the DoCmd object. With either approach, you generally can create read/write or read-only links. However, links to text files through an ISAM driver are always read-only.

Inserting vs. Copying

The first listing in this section contrasts two approaches to populating a table with data. The sample creates fresh copies of the FamilyMembers and FamilyMembers2 tables in the MyNewDB database. Then, it adds records to one table and copies those records to the other table. The code sample reinforces techniques covered earlier in the chapter by applying them to the chores for this application.

The sample consists of two procedures. The first procedure designates three parameters and passes them to the second procedure, which performs the main objectives of the sample. The three parameters designate the path and filename of an Access database file and the names of the two tables within the database that the application uses.

The second procedure enters some data into the FamilyMembers2 table with an INSERT INTO statement. You probably won't populate many databases this way, but the approach does work for small record sources, such as the one in this sample. Next, the sample illustrates the syntax for copying records from one record source (namely, the FamilyMembers2 table) to another record container (in this instance, the FamilyMembers table). This part of the application is both easy to understand and easy to apply. It is also very flexible because you can readily filter a recordset to contain a subset of the original recordset's rows and columns before copying it to a second record container.

Note

SQL statements are widely regarded as a fast way to process record sources because you work with the record source as a whole instead of just its individual records. On the other hand, looping techniques for records with ADO recordsets are easy to understand and grasp. As long as your record sources are not too large (thousands of records as opposed to hundreds of thousands of records or more), you can achieve acceptable performance with ADO looping methods.

The second procedure starts by calling the DeleteATable procedure to remove the FamilyMembers2 table from the MyNewDB database. This can generate an error if the table is not already there, but the application ignores the error with an On Error statement that resumes with the next line of code. Next, the procedure creates a fresh copy of the FamilyMembers2 table. The procedure then repeats this entire process to create a fresh copy of the FamilyMembers table as well. After creating the tables, the procedure moves on to populate them with data. First, it runs a series of INSERT INTO statements to add records to the FamilyMembers2 table. Then, it creates two recordsets—one pointed at the FamilyMembers2 table and the other pointed at the FamilyMembers table. With a Do loop, the procedure iterates through each of the rows in the FamilyMembers2 table. For each row in the FamilyMembers2 table it invokes the AddNew method to add a new row to the FamilyMembers table. Nested within the Do loop is a For…Each loop that passes through each field of the FamilyMembers2 recordset, copying column values from one recordset to the other. After completing the For…Each loop, the procedure invokes the Update method to transfer the copied values to the FamilyMembers table.

Sub CallTwoWaysToPopulateTables() 
Dim str1 As String
Dim str2 As String
Dim str3 As String
   
'Assign strings to pass arguments
str1 = "C:\Access11Files\Chapter03\MyNewDB.mdb"
str2 = "FamilyMembers2"
str3 = "FamilyMembers"
   
'Pass arguments
TwoWaysToPopulateTables str1, str2, str3
   
End Sub
   
Sub TwoWaysToPopulateTables(str1 As String, str2 As String, _
    str3 As String)
Dim cmd1 As ADODB.Command
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim fld1 As ADODB.Field
   
'Delete source table (str2), and re-create it
'to make sure you start with a fresh table.
On Error Resume Next
DeleteATable str1, str2
On Error GoTo 0
MakeFamilyMembers2TableInMyNewDB
   
'Delete destination table (str3), and re-create it
'to make sure you start with a fresh table; placed
'early in the procedure on purpose to allow time
'for automatic refresh to work because Refresh method used
'later in the procedure is not fast enough.
On Error Resume Next
DeleteATable str1, str3
On Error GoTo 0
MakeFamilyMembersTableInMyNewDB
   
'Add an index for the primary key on FamID.
   
AddPK
   
'Populate FamilyMembers2 table with command
'object based on a SQL statement.
Set cmd1 = New ADODB.Command
With cmd1
    .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & str1
   
    .CommandText = "INSERT INTO " & str2 & _
        "(Fname, Lname, Relation) VALUES ('Rick','Dobson','me')"
    .CommandType = adCmdText
    .Execute
    .CommandText = "INSERT INTO " & str2 & _
        "(Fname, Lname, Relation) VALUES ('Virginia','Dobson','wife')"
   
    .Execute
    .CommandText = "INSERT INTO " & str2 & _
        "(Fname, Lname, Relation) VALUES ('Glen','Hill','son')"
   
    .Execute
    .CommandText = "INSERT INTO " & str2 & _
        "(Fname, Lname, Relation) VALUES ('Tony','Hill','son')"
   
    .Execute
    .CommandText = "INSERT INTO " & str2 & _
        "(Fname, Lname, Relation) VALUES " & _
        "('Charles','Hinkle','father-in-law')"
   
    .Execute
End With
   
'Open recordsets pointed at populated FamilyMembers2 table
'and unpopulated FamilyMembers table.
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst1.Open "FamilyMembers2", cmd1.ActiveConnection
rst2.Open "FamilyMembers", cmd1.ActiveConnection, _
    adOpenKeyset, adLockOptimistic, adCmdTable
   
'Use ADO techniques to populate one table
'based on the values in another.
With rst2
    Do Until rst1.EOF
        .AddNew
            For Each fld1 In rst1.Fields
                .Fields(fld1.Name) = fld1.Value
            Next fld1
        .Update
    rst1.MoveNext
    Loop
   
End With
   
'Clean up objects.
Set cmd1 = Nothing
rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
   
End Sub

Linking via the Connection Object

You can link a database through the ADO Connection object and then select a specific record source within the database to populate a table with data. One key advantage of using a Connection object as opposed to a classic Access linked table is that the object does not appear in the Database window. This helps to maintain the security and integrity of your database. By using the Connection object, you can expose a record source in any mode that your Connection object and Recordset object settings permit. For example, by using a keyset cursor with a connection to a remote database, you can enable reading and writing to a record source within a database.

The following listing shows how to apply these concepts to an Excel spreadsheet file. The approach demonstrated in the sample uses a connection string to designate an ISAM driver. The inclusion of the Excel 8.0 property specifies the driver for Excel 97 and later workbook files. The source argument for the recordset Open method points to a range within the worksheet. In Excel, point to Name on the Insert menu and choose Define to create and update custom ranges that meet your application requirements.

Notice that the Open method in the following listing uses cursor settings that make the spreadsheet available for editing. In fact, the code sample appends xxxx to the first column in the first row. The procedure also prints to the Immediate window the first two columns of each row in the Customers range within the Customers.xls workbook file.

Sub OpenAndWriteToXLDataSource()
Dim cnn1 As New ADODB.Connection
Dim rst1 As Recordset
Dim bol1 As Boolean
   
'Open and set recordset
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=C:\Access11Files\Chapter03\Customers.xls;" & _
   "Extended Properties=Excel 8.0;"
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Open "Customers", cnn1, , , adCmdTable
   
'Open recordset, and print a test record.
Do Until rst1.EOF
    If bol1 = False Then
        rst1(0) = rst1(0) & "xxxx"
        bol1 = True
    End If
    Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
    rst1.MoveNext
Loop
   
'Clean up objects.
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
   
End Sub

Note

The Chapter03.mdb file includes a procedure named OpenAndRestoreXLDataSource that removes the trailing xs added by the OpenAndWriteToXLDataSource procedure.

The next listing shows the same technique, but this time the target data source is a table in a SQL Server database. Because the sample uses the MSDASQL provider, you can use the same basic code with any ODBC data source. The sample references the Authors table in the pubs database. The sample uses a DSN (data source name) to abbreviate the connection string for the data source. Abbreviating the connection string using a DSN requires that you previously define a DSN on the workstation with the correct connection string details. One advantage of using a DSN for small and mid-sized organizations is that you can use the ODBC Administrator interface to define the connection string graphically. Some large businesses write custom procedures to automate the installation of DSNs on workstations throughout an organization. Therefore, DSNs are appropriate for solutions in large and small organizations.

Notice the similarity of the following listing's design with that of the preceding one. This similarity occurs despite the fact that one listing references a spreadsheet and the other references a SQL Server database. That's a big advantage of using the ADO approach. This similarity also positions Access as a development environment for analyzing data from heterogeneous data sources.

Sub OpenAndWriteToODBCDataSource() 
Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim bol1 As Boolean
   
'Open ODBC sources with MSDASQL provider and DSN reference.
cnn1.Open "Provider=MSDASQL;DSN=Pubs;"
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Open "authors", cnn1, , , adCmdTable
   
'Open recordset and print a test record.
Do Until rst1.EOF
    If bol1 = False Then
        rst1(1) = rst1(1) & "xxxx"
        bol1 = True
    End If
    Debug.Print rst1.Fields(0).Value, _
        rst1.Fields(2), rst1.Fields(1).Value
    rst1.MoveNext
Loop
   
'Clean up objects.
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
   
End Sub

I restored the Authors table from the Pubs database with a procedure named OpenAndRestoreODBCDataSource. Just as the program for copying the xs to a data source had the same structure whether it was an XL file or an ODBC data source, so it is that the program to remove trailing xs also has the same structure for both sources. The sole distinction is to specify the connection string properly. You will need to update the connection string and the DSN so that they point at the proper data source. The connection string for the restore program for the sample appears below. Update the Server argument so that it points at a server in your environment instead of the CabSony1 server that I used for this book.

cnn1.Open "Provider=MSDASQL;DRIVER=SQL Server;" & _
    "SERVER=CabSony1;DATABASE=Pubs;uid=sa;pwd=password;"

Linking Using the DoCmd Method

You can link or import data sources with the DoCmd method. Since many developers and users are familiar with linked tables, this approach will appeal to those who prefer traditional techniques. The next listing includes three procedures. The first one links to a spreadsheet range. The second procedure links to an ODBC data source, and the third creates a linked table that points to a table in another Access database file. If you are an experienced Access developer, chances are you have applied these methods in the past. They appear here as a reminder that a venerable object, such as DoCmd, can still serve some important and valuable purposes. If you are just beginning to program Access, you might find extensions to these samples an especially easy way to link data from data sources outside the current Access database file. Access Help provides full explanations of all the arguments. The code presented here complements those explanations with easy-to-follow examples of the commands in action.

Note

If you already have a linked table in your database by the name that the TransferSpreadsheet or TransferDatabase method specifies, the method will create a linked table with a new name. The new name consists of the table name specified by the method argument followed by a number. So, if your method attempts to create a linked table named dboAuthors when a linked table with that name already exists, the method creates a linked table with the name dboAuthors1.

Sub linkXLCustomers()
Dim rst1 As ADODB.Recordset
Dim bol1 As Boolean
   
'Use DoCmd to programmatically make the link.
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, _
    "XLCustomers", "C:\Access11Files\Chapter03\Customers.xls", _
    True, "Customers"
   
'Open and set recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Open "XLCustomers", , , , adCmdTable
   
'Open recordset, and print a test record.
Do Until rst1.EOF
    Debug.Print rst1.Fields(0).Value, rst1.Fields(2)
    rst1.MoveNext
Loop
'Clean up objects.
rst1.Close
set rst1 = Nothing
   
End Sub
   
Sub linkODBCAuthors() 
Dim rst1 As ADODB.Recordset
   
'Use DoCmd to programmatically make the link.
DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=Pubs;", _
    acTable, "Authors", "dboAuthors"
   
'Open and set recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Open "dboAuthors", , , , adCmdTable
   
'Open recordset, and print a test record.
Do Until rst1.EOF
    Debug.Print rst1.Fields(0).Value, rst1.Fields(2)
    rst1.MoveNext
Loop
   
'Clean up objects.
rst1.Close
set rst1 = Nothing
   
End Sub
   
Sub linkNWCustomers() 
Dim rst1 As ADODB.Recordset
   
'Use DAO to programmatically make the link.
DoCmd.TransferDatabase acLink, "Microsoft Access", _
    "C:\Program Files\Microsoft Office\" & _
 "Office11\Samples\Northwind.mdb", _
    acTable, "Customers", "NWCustomers"
   
'Open and set recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Open "NWCustomers", , , , adCmdTable
   
'Open recordset, and print a test record.
Do Until rst1.EOF
    Debug.Print rst1.Fields(0).Value, rst1.Fields(2)
    rst1.MoveNext
Loop
   
'Clean up objects.
rst1.Close
Set rst1 = Nothing
   
End Sub

If you plan to work with linked tables programmatically, you need a way to track and manage the linked tables. Before we examine how to do this, recall that Access can represent linked tables in two ways. Linked tables pointing at ISAM data sources, such as an Excel workbook file or another Access database file, have a table Type property of LINK. Linked tables pointing at ODBC data sources, such as a SQL Server database, have a table Type property of PASS-THROUGH. To denote the set of all linked tables, you must specify tables with both Type properties.

The following listing can print the names of all linked tables in the Immediate window for any Access database that you specify. The listing specifically refers to the current project's connection, but this is just for convenience (you denote the Access database file in the first procedure). The application passes the database file path and filename to the second procedure, which creates a catalog that points at the database. Then, the procedure loops through the catalog's Tables collection, filtering for linked tables that are user defined. When the procedure finds a user-defined linked table of type LINK or PASS-THROUGH, it prints the table name and type to the Immediate window.

Sub CallListLinkedTables() 
Dim str1 As String
    
str1 = CurrentProject.Connection.ConnectionString
ListLinkedTables str1
   
End Sub
   
Sub ListLinkedTables(str1 As String) 
Dim cat1 As adox.Catalog
Dim tbl1 As adox.Table
Dim str2 As String
Dim mwd As Integer
   
'Point catalog at target database file.
Set cat1 = New adox.Catalog
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & str1
   
'Find length of longest table name.
mwd = 1
For Each tbl1 In cat1.Tables
    If (tbl1.Type = "LINK" Or tbl1.Type = "PASS-THROUGH") _
        Then If mwd < Len(tbl1.Name) Then mwd = Len(tbl1.Name)
Next tbl1
mwd = mwd + 1
   
'Print linked table names.
For Each tbl1 In cat1.Tables
    If (tbl1.Type = "LINK" Or tbl1.Type = "PASS-THROUGH") _
        And Left(tbl1.Name, 4) <> "~TMP" Then
        str2 = String(mwd - Len(tbl1.Name), " ")
        Debug.Print tbl1.Name & str2 & tbl1.Type
    End If
Next tbl1
   
'Clean up objects.
Set cat1 = Nothing
   
End Sub

A variation of the procedure just shown deletes all linked tables from a designated database. This variation merits special attention because you cannot use a For…Each loop to pass through the members of the Tables collection if you are going to delete a table within the loop. This is because the Delete method forces a reindexing of the objects in a collection each time that you invoke it. This reindexing causes the Delete method to miss some items that you meant to delete.

The solution to this problem is to use a For…Next loop that steps from the last to the first item in a collection. This approach ensures that the Delete method operates on each qualified member of a collection. The following listing illustrates the syntax for this approach:

Sub CallDeleteLinkedTables() 
Dim str1 As String
   
str1 = CurrentProject.Connection.ConnectionString
DeleteLinkedTables str1
   
End Sub
   
Sub DeleteLinkedTables(str1 As String)  
Dim cat1 As ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim int1 As Integer
   
'Point catalog at target database file
Set cat1 = New ADOX.Catalog
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & str1
   
'Loop through ISAM and ODBC linked tables
'to delete all linked tables.
For int1 = cat1.Tables.Count - 1 To 0 Step -1
    Set tbl1 = cat1.Tables(int1)
    If (tbl1.Type = "LINK" Or tbl1.Type = "PASS-THROUGH") _
        And Left(tbl1.Name, 4) <> "~TMP" Then
        cat1.Tables.Delete tbl1.Name
    End If
Next int1
   
'Refresh Database window to show deleted
'table links.
Application.RefreshDatabaseWindow
   
'Clean up objects.
Set cat1 = Nothing
   
End Sub

Importing Data from Text Files

One common need that arises when populating a database is to import delimited text data into an Access database. This need is especially common when you are working with legacy mainframe applications or UNIX applications. Developers can use the TransferText method of the DoCmd object to assist with this task. Because this is one of the method's most straightforward uses, you can designate just three arguments for it: the type of source (such as a delimited text file), the table name for the target within the Access database file, and the path and filename of the text file.

To demonstrate how this process works, you can export a table or two (such as the Northwind Orders table) from the Access database to create a suitable source for importing. Figure 3-8 shows the first several rows of the Orders table as a text file named Orders.txt. Notice the file has a typical comma delimited-format delimited with quotes around text fields. This is a common format for many text files. The whole file corresponding to the Orders table is also provided for you in the companion content for this book. The lines can wrap in Notepad view.

Figure 3.5: The first several rows of the Orders table from the Northwind database in a text file named Orders.txt and viewed in Notepad.

You can readily process text files in alternative formats to the one shown in Figure 3-8. When using these alternate formats, you might find a specification helpful for importing the data from the text file. One especially easy way to develop a specification is by pointing to Get External Data on the File menu in Excel and then choosing Link Tables. Then, select the Text Files file type. Select a file to link, then click the Link button. Click the Advanced button at the lower-left corner of the first Link Text Wizard page. Then, make selections, assign field names, and specify data types in the Link Specification dialog box. Click the Save As button to save your set of specifications with a name that lets you reuse it by designating this name as the second argument in the TransferText method. For a well-formed text file, the use of a specification is optional.

Note

The TransferText method has arguments that appear to enable the linking and importing of text data. However, you should understand that the text ISAM driver does not permit you to update the text source for a linked file from Access.

The following listing shows the syntax for basic use of the TransferText method. Notice that this sample specifies just three arguments. You can designate input and output formats for data other than acLinkDelim. That intrinsic constant denotes the input of a delimited text file. The second argument, a specification name, is blank. If you use a specification name, you enclose its name in double quotes. The third argument is the name of the table that contains the text file data. The fourth argument is the path and filename for the text file. An excerpt from this file appears in Figure 3-8.

The sample consists of three procedures. The first procedure manages the overall task and invokes the other two. Initially, the application deletes any previously existing version of the linked table that it will create for Orders.txt. A call to the DeleteTable procedure performs this task. Next, the first procedure invokes the TransferText method for the DoCmd object. The arguments for the method enable it to create a linked table named after the value in str2. The RstForTxt procedure handles this task and returns a Recordset object pointing at the linked table. The first procedure concludes by printing the first five rows from the returned recordset.

Sub ImportTxtToMyLinkedUneditedTable() 
Dim str1 As String
Dim str2 As String
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
   
'Assign values to string variables.
str1 = "C:\Access11Files\Chapter03\Orders.txt"
str2 = "MyLinkedTableUnedited"
   
'Remove previous version of table if it exists
DeleteTable str2
   
'Make a table and recordset based on imported data.
Set rst1 = RstForTxt(str1, str2)
   
'Print first five rows from recordset..
For int1 = 1 To 5
    Debug.Print rst1(0), rst1(1), rst1(2), rst1(3)
    rst1.MoveNext
Next int1
   
End Sub
   
Function RstForTxt(strInputPathFile As String, _
    strTableName As String) As ADODB.Recordset
Dim rst1 As ADODB.Recordset
   
'Import a delimited text file to the MyLinkedTable table.
DoCmd.TransferText _
    acLinkDelim, , _
    strTableName, _
    strInputPathFile
   
'Make a recordset based on imported data.
Set rst1 = New ADODB.Recordset
rst1.Open strTableName, CurrentProject.Connection, , , adCmdTable
   
'Return recordset for text file
Set RstForTxt = rst1
   
End Function
   
Sub DeleteTable(strTableName As String)  
Dim aot As AccessObject
   
'Loop for target table to delete
For Each aot In CurrentData.AllTables
    If aot.Name = strTableName Then _
        DoCmd.DeleteObject acTable, strTableName
Next aot
   
End Sub

By using the FileSystemObject to supplement the TransferText method, it is possible to edit a text file in a way that is not possible with the TransferText method alone. The ReadAndEditTextFile procedure demonstrates an approach to the task. This sample is from Module1 in the Chapter03.mdb file. The VBA project for the Access project has a reference to the Microsoft Scripting Runtime library. This reference is necessary for the syntax applied by the sample.

Note

A VBA project in an Access database file refers to any VBA code in standalone modules, class modules, and modules behind forms and reports.

The declaration area at the top of the procedure creates a new instance (fso) of the FileSystemObject. In turn, the OpenTextFile and CreateTextFile methods for the instance create read and write versions of TextStream objects. Recall from Chapter 2 that a text stream is an in-memory stream of bytes representing text characters. You can read and write to text stream objects by a character, a line, or a whole stream at a time. The f_txt text stream contains the characters in the original text file (Orders.txt). The procedure loops through the first five lines in f_txt and converts all instances of 1996 to 2000 with the VBA Replace function. After converting the values on each of the first five lines, the procedure writes the new converted lines to the nuf_txt stream object, which passes the data to Orderswritten.txt because of the CreateTextFile method used to instantiate the text stream. After completing the editing, the procedure closes the files and stream objects.

Next, the procedure generates a linked table named MyLinkedTableUnedited. In fact, the procedure generates this linked table twice—once for the initial text file (Orders.txt) and a second time for the edited text file (Orderswritten.txt). Each time the sample generates the linked table, it prints the first five rows to the Immediate window. In essence, the sample edits a text file and shows the changes in an Access module. The sample concludes with the linked table (MyLinkedTableUnedited) reflecting the edited values. This result is not possible with the TransferText method alone because the ISAM driver that the method uses does not enable editing a text file. By reading the data as a text stream, the sample is able to accomplish what the TransferText method cannot accomplish by itself. However, the sample still takes advantage of the TransferText method for readily creating a linked table based on a text file.

Sub ReadAndEditATextFile()
Dim str1 As String
Dim f_txt As TextStream
Dim nuf_txt As TextStream
Dim fso As New FileSystemObject
Dim rst1 As ADODB.Recordset
Dim str2 As String
Dim str3 As String
Dim int1 As Integer
   
'Assign the path to the initial text file.
str1 = "C:\Access11Files\Chapter03\Orders.txt"
   
'Instantiate two TextStream objects:
'one for reading and one for writing.
Set f_txt = fso.OpenTextFile(str1, ForReading)
Set nuf_txt = fso.CreateTextFile(Left(str1, Len(str1) - 4) & "written.txt")
   
'Edit the first five rows and write out the edited lines.
For int1 = 1 To 5
    str2 = f_txt.ReadLine
    str3 = Replace(str2, "1996", "2000")
    nuf_txt.WriteLine (str3)
Next int1
   
'Clean up the references to the files.
f_txt.Close
nuf_txt.Close
Set f_txt = Nothing
Set nuf_txt = Nothing
Set fso = Nothing
   
'Use str2 to name the linked table.
str2 = "MyLinkedTableUnedited"
   
'Remove previous version of table if it exists
'before creating a new version of the linked table.
DeleteTable str2
Set rst1 = RstForTxt(str1, str2)
   
'Print first five rows from the recordset based
'on the table.
Debug.Print "Initial, Unedited Values"
For int1 = 1 To 5
    Debug.Print rst1(0), rst1(1), rst1(2), rst1(3)
    rst1.MoveNext
Next int1
   
'Remove previously created version of the linked table.
DeleteTable str2
   
'Specify the edited file, Orderswritten.txt, as the
'source for the new linked table and recordset.
str1 = "C:\Access11Files\Chapter03\Orderswritten.txt"
Set rst1 = RstForTxt(str1, str2)
   
'Print first five rows from recordset.
Debug.Print String(2, vbCrLf) & "Edited Values"
For int1 = 1 To 5
    Debug.Print rst1(0), rst1(1), rst1(2), rst1(3)
    rst1.MoveNext
Next int1
   
End Sub