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.

 SubCallTwoWaysToPopulateTables()  Dimstr1AsString Dimstr2AsString Dimstr3AsString     'Assignstringstopassarguments str1="C:\Access11Files\Chapter03\MyNewDB.mdb" str2="FamilyMembers2" str3="FamilyMembers"     'Passarguments TwoWaysToPopulateTablesstr1,str2,str3     EndSub     SubTwoWaysToPopulateTables(str1AsString,str2AsString,_ str3AsString) Dimcmd1AsADODB.Command Dimrst1AsADODB.Recordset Dimrst2AsADODB.Recordset Dimfld1AsADODB.Field     'Deletesourcetable(str2),andre-createit 'tomakesureyoustartwithafreshtable. OnErrorResumeNext DeleteATablestr1,str2 OnErrorGoTo0 MakeFamilyMembers2TableInMyNewDB     'Deletedestinationtable(str3),andre-createit 'tomakesureyoustartwithafreshtable;placed 'earlyintheprocedureonpurposetoallowtime 'forautomaticrefreshtoworkbecauseRefreshmethodused 'laterintheprocedureisnotfastenough. OnErrorResumeNext DeleteATablestr1,str3 OnErrorGoTo0 MakeFamilyMembersTableInMyNewDB     'AddanindexfortheprimarykeyonFamID.     AddPK     'PopulateFamilyMembers2tablewithcommand 'objectbasedonaSQLstatement. Setcmd1=NewADODB.Command Withcmd1 .ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1     .CommandText="INSERTINTO"&str2&_ "(Fname,Lname,Relation)VALUES('Rick','Dobson','me')" .CommandType=adCmdText .Execute .CommandText="INSERTINTO"&str2&_ "(Fname,Lname,Relation)VALUES('Virginia','Dobson','wife')"     .Execute .CommandText="INSERTINTO"&str2&_ "(Fname,Lname,Relation)VALUES('Glen','Hill','son')"     .Execute .CommandText="INSERTINTO"&str2&_ "(Fname,Lname,Relation)VALUES('Tony','Hill','son')"     .Execute .CommandText="INSERTINTO"&str2&_ "(Fname,Lname,Relation)VALUES"&_ "('Charles','Hinkle','father-in-law')"     .Execute EndWith     'OpenrecordsetspointedatpopulatedFamilyMembers2table 'andunpopulatedFamilyMemberstable. Setrst1=NewADODB.Recordset Setrst2=NewADODB.Recordset rst1.Open"FamilyMembers2",cmd1.ActiveConnection rst2.Open"FamilyMembers",cmd1.ActiveConnection,_ adOpenKeyset,adLockOptimistic,adCmdTable     'UseADOtechniquestopopulateonetable 'basedonthevaluesinanother. Withrst2 DoUntilrst1.EOF .AddNew ForEachfld1Inrst1.Fields .Fields(fld1.Name)=fld1.Value Nextfld1 .Update rst1.MoveNext Loop     EndWith     'Cleanupobjects. Setcmd1=Nothing rst1.Close rst2.Close Setrst1=Nothing Setrst2=Nothing     EndSub 

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.

 SubOpenAndWriteToXLDataSource() Dimcnn1AsNewADODB.Connection Dimrst1AsRecordset Dimbol1AsBoolean     'Openandsetrecordset cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\Access11Files\Chapter03\Customers.xls;"&_ "ExtendedProperties=Excel8.0;" Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Open"Customers",cnn1,,,adCmdTable     'Openrecordset,andprintatestrecord. DoUntilrst1.EOF Ifbol1=FalseThen rst1(0)=rst1(0)&"xxxx" bol1=True EndIf Debug.Printrst1.Fields(0).Value,rst1.Fields(1).Value rst1.MoveNext Loop     'Cleanupobjects. rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing     EndSub 

Note  

The Chapter03.mdb file includes a procedure named OpenAndRestoreXLDataSource that removes the trailing x s 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.

 SubOpenAndWriteToODBCDataSource()  Dimcnn1AsNewADODB.Connection Dimrst1AsADODB.Recordset Dimbol1AsBoolean     'OpenODBCsourceswithMSDASQLproviderandDSNreference. cnn1.Open"Provider=MSDASQL;DSN=Pubs;" Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Open"authors",cnn1,,,adCmdTable     'Openrecordsetandprintatestrecord. DoUntilrst1.EOF Ifbol1=FalseThen rst1(1)=rst1(1)&"xxxx" bol1=True EndIf Debug.Printrst1.Fields(0).Value,_ rst1.Fields(2),rst1.Fields(1).Value rst1.MoveNext Loop     'Cleanupobjects. rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing     EndSub 

I restored the Authors table from the Pubs database with a procedure named OpenAndRestoreODBCDataSource . Just as the program for copying the x s 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 x s 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=SQLServer;"&_ "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 .

 SublinkXLCustomers() Dimrst1AsADODB.Recordset Dimbol1AsBoolean     'UseDoCmdtoprogrammaticallymakethelink. DoCmd.TransferSpreadsheetacLink,acSpreadsheetTypeExcel97,_ "XLCustomers","C:\Access11Files\Chapter03\Customers.xls",_ True,"Customers"     'Openandsetrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection=CurrentProject.Connection rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Open"XLCustomers",,,,adCmdTable     'Openrecordset,andprintatestrecord. DoUntilrst1.EOF Debug.Printrst1.Fields(0).Value,rst1.Fields(2) rst1.MoveNext Loop 'Cleanupobjects. rst1.Close setrst1=Nothing     EndSub     SublinkODBCAuthors()  Dimrst1AsADODB.Recordset     'UseDoCmdtoprogrammaticallymakethelink. DoCmd.TransferDatabaseacLink,"ODBCDatabase",_ "ODBC;DSN=Pubs;",_ acTable,"Authors","dboAuthors"     'Openandsetrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection=CurrentProject.Connection rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Open"dboAuthors",,,,adCmdTable     'Openrecordset,andprintatestrecord. DoUntilrst1.EOF Debug.Printrst1.Fields(0).Value,rst1.Fields(2) rst1.MoveNext Loop     'Cleanupobjects. rst1.Close setrst1=Nothing     EndSub     SublinkNWCustomers()  Dimrst1AsADODB.Recordset     'UseDAOtoprogrammaticallymakethelink. DoCmd.TransferDatabaseacLink,"MicrosoftAccess",_ "C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb",_ acTable,"Customers","NWCustomers"     'Openandsetrecordset Setrst1=NewADODB.Recordset rst1.ActiveConnection=CurrentProject.Connection rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.Open"NWCustomers",,,,adCmdTable     'Openrecordset,andprintatestrecord. DoUntilrst1.EOF Debug.Printrst1.Fields(0).Value,rst1.Fields(2) rst1.MoveNext Loop     'Cleanupobjects. rst1.Close Setrst1=Nothing     EndSub 

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.

 SubCallListLinkedTables()  Dimstr1AsString str1=CurrentProject.Connection.ConnectionString ListLinkedTablesstr1     EndSub     SubListLinkedTables(str1AsString)  Dimcat1Asadox.Catalog Dimtbl1Asadox.Table Dimstr2AsString DimmwdAsInteger     'Pointcatalogattargetdatabasefile. Setcat1=Newadox.Catalog cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1     'Findlengthoflongesttablename. mwd=1 ForEachtbl1Incat1.Tables If(tbl1.Type="LINK"Ortbl1.Type="PASS-THROUGH")_ ThenIfmwd<Len(tbl1.Name)Thenmwd=Len(tbl1.Name) Nexttbl1 mwd=mwd+1     'Printlinkedtablenames. ForEachtbl1Incat1.Tables If(tbl1.Type="LINK"Ortbl1.Type="PASS-THROUGH")_ AndLeft(tbl1.Name,4)<>"~TMP"Then str2=String(mwd-Len(tbl1.Name),"") Debug.Printtbl1.Name&str2&tbl1.Type EndIf Nexttbl1     'Cleanupobjects. Setcat1=Nothing     EndSub 

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:

 SubCallDeleteLinkedTables()  Dimstr1AsString     str1=CurrentProject.Connection.ConnectionString DeleteLinkedTablesstr1     EndSub     SubDeleteLinkedTables(str1AsString)  Dimcat1AsADOX.Catalog Dimtbl1AsADOX.Table Dimint1AsInteger     'Pointcatalogattargetdatabasefile Setcat1=NewADOX.Catalog cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1     'LoopthroughISAMandODBClinkedtables 'todeletealllinkedtables. Forint1=cat1.Tables.Count-1To0Step-1 Settbl1=cat1.Tables(int1) If(tbl1.Type="LINK"Ortbl1.Type="PASS-THROUGH")_ AndLeft(tbl1.Name,4)<>"~TMP"Then cat1.Tables.Deletetbl1.Name EndIf Nextint1     'RefreshDatabasewindowtoshowdeleted 'tablelinks. Application.RefreshDatabaseWindow     'Cleanupobjects. Setcat1=Nothing     EndSub 

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.

 SubImportTxtToMyLinkedUneditedTable()  Dimstr1AsString Dimstr2AsString Dimrst1AsADODB.Recordset Dimint1AsInteger     'Assignvaluestostringvariables. str1="C:\Access11Files\Chapter03\Orders.txt" str2="MyLinkedTableUnedited"     'Removepreviousversionoftableifitexists DeleteTablestr2     'Makeatableandrecordsetbasedonimporteddata. Setrst1=RstForTxt(str1,str2)     'Printfirstfiverowsfromrecordset.. Forint1=1To5 Debug.Printrst1(0),rst1(1),rst1(2),rst1(3) rst1.MoveNext Nextint1     EndSub     FunctionRstForTxt(strInputPathFileAsString,_ strTableNameAsString)AsADODB.Recordset Dimrst1AsADODB.Recordset     'ImportadelimitedtextfiletotheMyLinkedTabletable. DoCmd.TransferText_ acLinkDelim,,_ strTableName,_ strInputPathFile     'Makearecordsetbasedonimporteddata. Setrst1=NewADODB.Recordset rst1.OpenstrTableName,CurrentProject.Connection,,,adCmdTable     'Returnrecordsetfortextfile SetRstForTxt=rst1     EndFunction     SubDeleteTable(strTableNameAsString)  DimaotAsAccessObject     'Loopfortargettabletodelete ForEachaotInCurrentData.AllTables Ifaot.Name=strTableNameThen_ DoCmd.DeleteObjectacTable,strTableName Nextaot     EndSub 

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.

 SubReadAndEditATextFile() Dimstr1AsString Dimf_txtAsTextStream Dimnuf_txtAsTextStream DimfsoAsNewFileSystemObject Dimrst1AsADODB.Recordset Dimstr2AsString Dimstr3AsString Dimint1AsInteger     'Assignthepathtotheinitialtextfile. str1="C:\Access11Files\Chapter03\Orders.txt"     'InstantiatetwoTextStreamobjects: 'oneforreadingandoneforwriting. Setf_txt=fso.OpenTextFile(str1,ForReading) Setnuf_txt=fso.CreateTextFile(Left(str1,Len(str1)-4)&"written.txt")     'Editthefirstfiverowsandwriteouttheeditedlines. Forint1=1To5 str2=f_txt.ReadLine str3=Replace(str2,"1996","2000") nuf_txt.WriteLine(str3) Nextint1     'Cleanupthereferencestothefiles. f_txt.Close nuf_txt.Close Setf_txt=Nothing Setnuf_txt=Nothing Setfso=Nothing     'Usestr2tonamethelinkedtable. str2="MyLinkedTableUnedited"     'Removepreviousversionoftableifitexists 'beforecreatinganewversionofthelinkedtable. DeleteTablestr2 Setrst1=RstForTxt(str1,str2)     'Printfirstfiverowsfromtherecordsetbased 'onthetable. Debug.Print"Initial,UneditedValues" Forint1=1To5 Debug.Printrst1(0),rst1(1),rst1(2),rst1(3) rst1.MoveNext Nextint1     'Removepreviouslycreatedversionofthelinkedtable. DeleteTablestr2     'Specifytheeditedfile,Orderswritten.txt,asthe 'sourceforthenewlinkedtableandrecordset. str1="C:\Access11Files\Chapter03\Orderswritten.txt" Setrst1=RstForTxt(str1,str2)     'Printfirstfiverowsfromrecordset. Debug.PrintString(2,vbCrLf)&"EditedValues" Forint1=1To5 Debug.Printrst1(0),rst1(1),rst1(2),rst1(3) rst1.MoveNext Nextint1     EndSub