Exporting data from Microsoft Access table file subsets to plain text

Question

I have a problem in exporting data from Microsoft Access. I have an Access file made of several rows and columns, like this:

Data table main data

Every row contains a subset of data, tha could be observed by clicking on the "+" button:

Data table subsets

I would like to export all these data (main table & subsets), but there's a problem. When I click on External Data -> Export -> Text file, Ms Access exports only the main table data (1st Figure), and completely omits the data of the subsets (2nd Figure).

How could I export all these data (main table & subsets)?

Thanks

Answer 1

Microsoft Access does not know how you want the files exported when you use the standard "export" text function.

Here is a trivial piece of code that you can add to a Module, and run from a Command Button on a form, or directly from the Immediate Window:

Public Sub ExportTables(ByVal DestinationPath As String)
 Dim a As Long
 For a = 0 To CurrentDb.TableDefs.Count - 1
 If Not (CurrentDb.TableDefs(a).Name Like "MSys*") Then
 DoCmd.TransferText AcTextTransferType.acExportDelim _
 , _
 , CurrentDb.TableDefs(a).Name, DestinationPath & "\" & CurrentDb.TableDefs(a).Name & ".csv" _
 , True
 End If
 Next a
End Sub

Add a Module to your Access database, and paste in the code above. Press "CTRL-G", enter ExportTables "C:\temp", then press Enter - this will export all tables in the database into C:\TEMP in comma-separated-values format (.CSV) ready for import into Excel or other databases.