How to Export an Access table into a
textfile.
Fixed length and Comma Delimited.
This sample shows How to Export an Access table to either a
comma delimited or a fixed length text file. It also shows how
to create a Schema.ini at runtime for the fixed length
textfile.
What is a Schema.ini you say! Click
here to find out!
For the sample code to run you will need to do
the following things:
1. Add a Reference to Microsoft DAO 3.5(VB5) or 3.51(VB6)
Object Library.
2. 3 Command Buttons.
3. Make sure you have the correct path to the Database. This
uses the sample database Biblio.mdb and the Authors table. If
you want to use a different database just change the
OpenDatabase and Execute lines.
Option Explicit
Dim db As Database
'This sample shows How to Export an Access
table to either a comma delimited or a fixed length text
file.
Private Sub Command1_Click()
'This procedure opens the sample database
biblio.mdb and exports the authors table to a new comma
delimited text file.
'For a fixed length text file create a schema.ini first. See
Command button 3 below for an example.
'Make sure to change the path to the location of
biblio.mdb
Set db = OpenDatabase("D:\biblio.mdb")
'The Select into statement creates new
table/text file.
'Make sure to change the path to where you want the text file
created.
db.Execute "Select * into A1#txt in
''[Text;database=d:\textfiles\;] from authors"
MsgBox "Done"
End Sub
Private Sub Command2_Click()
'This procedure opens the sample database
biblio.mdb and exports the authors table to an existing text
file.
'Make sure to change the path to the
location of biblio.mdb
Set db = OpenDatabase("D:\biblio.mdb")
'The Insert into statement Inserts data into
an already existing table/textfile
'Make sure to change the path to where the text file is.
db.Execute "Insert into test#txt in
''[Text;database=d:\textfiles\;] select * from authors"
MsgBox "Done"
End Sub
Private Sub Command3_Click()
'This Procedure uses
FileI/O to create a schema.ini for a fixed length
textfile
'Make sure to change the path to where your text file is. So
the Schema.ini will be create in the same directory.
Open "d:\textfiles\schema.ini" For Output As #1
'This is the basic structure of a
Schema.ini
Print #1, "[A1.txt]"
Print #1, "ColNameHeader=True"
Print #1, "Format=FixedLength"
Print #1, "MaxScanRows=25"
Print #1, "CharacterSet=OEM"
Print #1, "Col1=AU_ID Integer Width 10"
Print #1, "Col2=AUTHOR Char Width 30"
Print #1, "Col3=" & Chr(34) & "Year Born" &
Chr(34) & " Integer Width 4"
'If your field name has spaces in it you
will need to put double quotes around it. The chr(34) is
used for this.
Close #1
MsgBox "Done"
End Sub
Private Sub Form_Load()
Command1.Caption = "Select Into"
Command2.Caption = "Insert Into"
Command3.Caption = "Schema.ini"
End Sub