Export data from Access database in a file

Hello Expert!!!

How can i export data from Access with single button.click, let's
say i have one button and when i press this button in Access program ( database ) i want to take the value of one or many field and put in text file with sepparate commas.

Regards
Trausti
traustiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HunterKillerCommented:
It's easy, and involves operations with files and the JOIN  intrinsic function of VB:

Private Sub Command1_Click()
'this will export your database to a txt file
Dim n As Integer                'number of the free file
Dim i As Integer
Dim Db As Database
Dim rst As Recordset
Dim Register As String          'the output string
Dim MyFields() As String        'the array of fields
Set Db = OpenDatabase("C:\path\MyDB.mdb")
Set rst = Db.OpenRecordset("SELECT * FROM MyTable")
ReDim MyFields(0 To rst.Fields.Count)
n = FreeFile
Open "C:\path\Export.txt" For Append As #n
Do Until rst.EOF = True
  Register = ""
  For i = 0 To rst.Fields.Count - 1
    MyFields(i) = rst.Fields(i)
  Next i
  Register = Join(MyFields, ",")
  Print #n, Register
  rst.MoveNext
Loop
Close n
MsgBox "File Done !"
End Sub

Use SPLIT to get the fields separated again
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
traustiAuthor Commented:
what if i want just one or tvo fields?
0
HunterKillerCommented:
I forgot a '-1' in the ReDim line; it should be this:

ReDim MyFields(0 To rst.Fields.Count -1)
0
HunterKillerCommented:
In the SELECT statement, instead of the * you should use the fields names, separated by commas, like:

SELECT Code,Name,Adress FROM  ...

it can be a variable, and you could use checkboxes to get the fields you want to export:

Private Sub Command1_Click()
'this will export your database to a txt file
Dim n As Integer                'number of the free file
Dim i As Integer
Dim tabFields As String
Dim strSQL As String
Dim Db As Database
Dim rst As Recordset
Dim Register As String          'the output string
Dim MyFields() As String        'the array of fields

'find wich fields to export:
tabFields = ""
If Check1(0).Value = 1 Then tabFields = tabFields & "Code"
If Check1(1).Value = 1 Then tabFields = tabFields & ", Name"
If Check1(2).Value = 1 Then tabFields = tabFields & ", Address"

Set Db = OpenDatabase("C:\meus documentos\alunos.mdb")
If tabFields = "" Then
  MsgBox "Select at least 1 field to export."
  Exit Sub
End If
strSQL = "SELECT " & tabFields & " FROM tabAlunos"
Set rst = Db.OpenRecordset(strSQL)
ReDim MyFields(0 To rst.Fields.Count - 1)
n = FreeFile
Open "C:\meus documentos\alunos.txt" For Append As #n
Do Until rst.EOF = True
  Register = ""
  For i = 0 To rst.Fields.Count - 1
    MyFields(i) = rst.Fields(i)
  Next i
  Register = Join(MyFields, ",")
  Print #n, Register
  rst.MoveNext
Loop
Close n
MsgBox "File Done !"
rst.Close
Db.Close
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.