[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Export data from Access database in a file

Posted on 1999-12-13
4
Medium Priority
?
150 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:trausti
  • 3
4 Comments
 
LVL 1

Accepted Solution

by:
HunterKiller earned 300 total points
ID: 2277191
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
 

Author Comment

by:trausti
ID: 2277228
what if i want just one or tvo fields?
0
 
LVL 1

Expert Comment

by:HunterKiller
ID: 2277277
I forgot a '-1' in the ReDim line; it should be this:

ReDim MyFields(0 To rst.Fields.Count -1)
0
 
LVL 1

Expert Comment

by:HunterKiller
ID: 2277335
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question