export table

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
I have a table: table:id,name. Now I need to export this
table to many id.csv files. All names for the same id goes
to id.csv, and this happens for all id in the table.
How can I do that?
Thanks,
Jim.
 
Jim,

This is not something you can do with a query. It takes some VB code, like
the example below:

Sub Export_id_files()
Dim bd as DAO.Database
Dim rs1 as DAO.Recordset
Dim rs2 as DAO.Recordset
Dim csvPath as String

csvPath = "C:\Temp" 'Destination drive and Path

Set db = CurrentDB()
Set rs1 = db.OpenRecordset("SELECT id FROM tblName GROUP BY id")
rs1.MoveFirst
Do Until rs1.EOF
Open csvPath & "\" & rs1.fields(0) & ".csv" for Output as #1
Set rs2 = db.OpenRecordset("SELECT id, name FROM tblName WHERE id = ' "
& rs1.fields(0) & " ' ")
rs2.MoveFirst
Do Until rs2.EOF
Print #1, rs2.field(0) & "," & rs2.fields(1) & ","
rs2.MoveNext
Loop
Close #1
rs2.Close
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub

Make sur et change tblName to the actual table name, and id and name in the
SQL strings to the actual field names. Also, set csvPath to the desired
destination path.

HTH,
Nikos
 
Back
Top