HandsOn Copy Records to a Text File

1. In the Code window, enter the WriteToFile procedure as shown below.

Sub WriteToFile()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim f As ADODB.Field Dim fso As Object Dim txtfile As Object

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Set fso = CreateObject("Scripting.FileSystemObject") Set txtfile = fso.CreateTextFile("c:\testfile.txt", True)

Set rst = New ADODB.Recordset rst.Open "[Order Details]", conn With rst

For Each f In .Fields

' Write field name to the text file

Part II

txtfile.Write (f.Name) txtfile.Write Chr(9) Next

' move to a new line txtfile.WriteLine

' write out all the records to the text file txtfile.Write rst.GetString(adClipString) .Close End With txtfile.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

^teettile Wt

Notepad

- G I

üj

Fils Edll

Forme! Help

¡OrderlD

ProductlDUnitPrice Quantity

Discount

3

10248

11

14 12

OLJ10 24 S 42

Hl

9.0

10

CD 1024a 72

34.0 S

on 10249

14

16.6 9

OU 10249 51

42.4

40

CD 10250 41

7.7 10

OP 10250

51

42.4 35

0.15D 10250

Figure 14-3: After running the WriteToFile procedure in Hands-On 14-8, the records from the Order Details table are placed in a text file.

Si testflle.lxt

uyixi

A

B

C

D

E

01

1

OrderlD

iPrtrductID

Unit Pries

Quantity

Discount

:

10246

11

14

12

0

3

10246

42

9.5

10

0

4

10246

72

34.8

s

0

5

10249

14

18.6

9

0

G

10249

51

42.4

46

0

7

10250

41

7.7

10

0

8

10250

£1

42.4

35

0.15

S

10250

65

15.5

15

0.15

to

10251

22

15.5

5

0.05

H

10251

57

15.6

15

0.D5

12

10251

65

ts.e

26

0

13

10252

20

64.e

46

0.05

14

10252

33

2

25

0.05

15

10252

60

27.2

46

0

15

10253

31

15

25

0

17

10253

39

14.4

42

0

ta

10253

49

16

46

0

«

< ^

► >i\testfîle/

<

_>

1

Figure 14-4: The Accessgenerated text file in Hands-On 14-8 is now opened in Excel.

Figure 14-4: The Accessgenerated text file in Hands-On 14-8 is now opened in Excel.

0 0

Post a comment