Opening a Text File Using ADO

There are several ways to open text files programmatically. This section demonstrates how to gain access to a text file by using the Microsoft Text driver. Notice that this is a DSN-less connection (see the previous section for more information). All of the connection information is specified inside the program procedure. The following example procedure shows how to open a recordset based on a comma-separated file format and write the file contents to the Immediate window.

©Hands-On 10-8: Opening a Text File with ADO

This hands-on uses the Employees.txt file provided in the book's downloadable files. This data file can be prepared from scratch by typing the following in Notepad and saving the file as Employees.txt:

"Last Name", "First Name", "Birthdate", "Years Worked" "Krawiec","Bogdan",#1963-01-02#,3 "Gorecka","Jadwiga",#1948-05-12#,1 "Olszewski","Stefan",#1957-04-07#,0

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Open_TextFile procedure shown below.

Creating and Manipulating Databases with ADO

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Open_TextFile()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim fld As ADODB.Field

Set conn = New ADODB.Connection Debug.Print conn.ConnectionString conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" &_ "DBQ=" & CurrentProject.Path & "\" Set rst = New ADODB.Recordset rst.Open "select * from [Employees.txt]", conn, adOpenStatic, _

adLockReadOnly, adCmdText Do Until rst.EOF

For Each fld In rst.Fields

Debug.Print fld.Name & "=" & fld.Value Next fld rst.MoveNext

Loop rst.Close

Set rst = Nothing conn.Close

Set conn = Nothing

MsgBox "Open the Immediate window to view the data." End Sub

0 0

Post a comment