Exporting Data from an Access Database

[rene316]

Hello,
Not sure if this has been asked, I tried searching for it but came up empty, so forgive me if this is a redundant question. I have an application that I am writing for my company, and I have it posting to an access database. But I am having trouble finding anything on how to export the data to a comma delimited file(csv). Is there a way to do this(fairly easy) I am still new to this, I do this on my spare time at work, it is not my main function and have had no classes. I am learning out of about 10 or more different books on my own. Any help would be much appreciated.

Thank you.

 

[vb5prgrmr]

You can export directly from access to a csv if you wish. If you are wanting to automate this from withing your program you could do something like the following.
[tt]

Dim S As String, FName As String, FNumb As Integer

'open connection/recordset

FName = "Your path to file" 'you could use common dialog to specify this
FNumb = FreeFile

Open FName For Output As #FNumb

Do While Not rs.EOF
S = rs.fields("Your_First_Field&quot & ","
S = S & rs.fields("Your_Next_Field&quot & ","
'...
Print #FNumb, S
rs.movenext
Loop

Close #FNumb

'close connection/recordset

[/tt]

Good Luck

 

[CCLINT]

If you are using ADO then you can use the GetString method to put all the records into a delimited format into one string variable, and then save this to a file using vb5prgrmr's above "Open" statement in one shot
Or, use the Jet's SELECT INTO method (it will then use the text driver to export) [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[thbinh]

Exporting Data from an Access Database

TextBox: txtFileName
txtDatabaseName
CommandButton: cmdExport
Label: Label1


Option Explicit

' Export the file.
Private Sub cmdExport_Click()
Dim fnum As Integer
Dim file_name As String
Dim database_name As String
Dim db As Database
Dim rs As Recordset
Dim num_fields As Integer
Dim field_width() As Integer
Dim field_value As String
Dim i As Integer
Dim num_processed As Integer

On Error GoTo MiscError

' Open the output file.
fnum = FreeFile
file_name = txtFileName.Text
Open file_name For Output As fnum

' Open the database.
Set db = OpenDatabase(txtDatabaseName.Text)

' Open the recordset.
Set rs = db.OpenRecordset( _
"SELECT * FROM Books ORDER BY Title&quot

' Start with the names of the fields.
num_fields = rs.Fields.Count
ReDim field_width(0 To num_fields - 1)
For i = 0 To num_fields - 1
' We're only working with Text here. Other
' types are different. For example, an
' integer may take 2 bytes to store but 6
' characters to display.
field_width(i) = rs.Fields(i).Size
If field_width(i) < Len(rs.Fields(i).Name) Then
field_width(i) = Len(rs.Fields(i).Name)
End If
field_width(i) = field_width(i) + 1
Print #fnum, rs.Fields(i).Name;
Print #fnum, Space$(field_width(i) - _
Len(rs.Fields(i).Name));
Next i
Print #fnum, &quot;&quot;

' Process the records.
Do While Not rs.EOF
num_processed = num_processed + 1
For i = 0 To num_fields - 1
field_value = rs.Fields(i).Value
Print #fnum, field_value & _
Space$(field_width(i) - _
Len(field_value));
Next i
Print #fnum, &quot;&quot;
rs.MoveNext
Loop

' Close the file and database.
rs.Close
db.Close
Close fnum
MsgBox &quot;Processed &quot; & _
Format$(num_processed) & &quot; records.&quot;

Exit Sub

MiscError:
MsgBox &quot;Error &quot; & Err.Number & _
vbCrLf & Err.Description
End Sub
Private Sub Form_Load()
txtDatabaseName.Text = App.Path & &quot;\books.mdb&quot;
txtFileName.Text = App.Path & &quot;\books.txt&quot;
End Sub


::: books.mdb
Title URL
Advanced Visual Basic Techniques

http://www.vb-helper.com/avbt.htm

Custom Controls Library

http://www.vb-helper.com/ccl.htm

Ready-to-Run Delphi Algorithms

http://www.vb-helper.com/da.htm

 

[CCLINT]

Actually, there is a simiple way with DAO (will work under ADO also) and JET using a SELECT INTO statement:

db.Execute &quot;SELECT * INTO [Export.Asc] IN '' [TEXT;DATABASE=C:\Test\] FROM myMDBTable&quot;
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!